Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: Performance issue with linked table

    This may be an Access 2003 question rather than a DB2 question but I hope that someone may know the answer.

    We're moving from Access 2000 to 2003. One of our Access applications serves as a front end: one of the tables in ACCESS is a linked table to the DB2 database via ODBC.

    If I try a search in the form, I have to wait half a minute for the result, I am connected to the DB2 server via LAN. People who are connected to our DB2 server via WAN have to wait 30 minutes or longer. It seems Access 2003 reads the whole table and performs the search on the client PC instead of letting DB2 do the search on the server. Access 2000 was able to use the indexes defined on the table in DB2 (search took 1 or 2 seconds). How can I convince Access 2003 to use DB2's indexes too ?

    Any suggestions ?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Nov 2004
    Posts
    374

    linked

    many products pull the complete table down and apply the predicate locally..
    I don't see a way around this .. (use other tools../ application..that will do what you request and don't live there own lives..)
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you tried using a passthru query instead ?



    Quote Originally Posted by Wim
    This may be an Access 2003 question rather than a DB2 question but I hope that someone may know the answer.

    We're moving from Access 2000 to 2003. One of our Access applications serves as a front end: one of the tables in ACCESS is a linked table to the DB2 database via ODBC.

    If I try a search in the form, I have to wait half a minute for the result, I am connected to the DB2 server via LAN. People who are connected to our DB2 server via WAN have to wait 30 minutes or longer. It seems Access 2003 reads the whole table and performs the search on the client PC instead of letting DB2 do the search on the server. Access 2000 was able to use the indexes defined on the table in DB2 (search took 1 or 2 seconds). How can I convince Access 2003 to use DB2's indexes too ?

    Any suggestions ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I'm not an Access guru. The current solution is a temporary one, until we find the resources to analyse and program it from the ground up. The form was written by one of our end users and the linked DB2 table was a solution for the inability of Access to handle multiple concurrent users. Ripping this table out of Access and putting it in a multi user DB solved the problem, until Access 2003 came at the rescue.

    How can you use a form with pass through? I think he then has to reprogram the whole form and with a totally different design.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •