Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query: Improve Performance (2002)

    I'm using Access 2002 (10.2627.3501)SP-1

    I've been testing an ODBC driver to pull records from a DOS database.

    If I create a standard select query, and use all records from a table, the query when run is quite quick.

    If I create relationships linking two or more tables and then create a select query, the performance is terrible.
    The main table "EST" contains about 18000 records, from this, pulling a query with a date range takes forever.

    Has anyone any solutions on improving speed with the query ?
    Compact and repair doesn't really improve much at all.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query: Improve Performance (2002)

    Would it be practicable to import the table(s) from the DOS database into Access first, and run the query on the imported tables? Or have you already imported the tables?

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query: Improve Performance (2002)

    My guess is that you can't do much. Let's say you have Customer and Invoice tables, and the common field is CustomerNo. I'm guessing that Access doesn't see a CustomerNo index on the Invoice table, so linking the 2 tables is very infefficient. It would almost seem easier to import the Invoice table into a temp table in Access, where you can control the indexes.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query: Improve Performance (2002)

    In point of fact this often happens with ODBC linked tables, and is largely a function of the ODBC driver. The problem comes when you start joining tables, either entirely in Access or partially in Access and partially in the external database. When you have those situations, Access brings in the entire table(s) so it can do the join internally - if it is just a single table, the ODBC driver is usually "smart" enough to get just the data you need and deliver it to Access. (The SQL Server ODBC driver is smart enough that it will often run two or three table joins where all the tables are in SQL Server in the database engine, but try it with two tables in SQL Server and one in Access and the wheels fall off.)

    In your case I agree both with Hans and Mark - bringing the entire set of data in on a temporary or periodic basis would be the best solution, OR you could simply rewrite the application that creates and uses the data in Access! [evingrin]
    Wendell

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query: Improve Performance (2002)

    Ok well thankyou all for the wise words.

    The tables are linked at the moment, so answering all your guess's.
    I'll do some testing to see which is the best method.
    If I created the relationships now, will those relationships still be present once imported. ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query: Improve Performance (2002)

    You will have to create the relationships anew after importing the data, since you will have to delete the links before importing the tables.

    Added: if you want, you can import, create indexes and relationships in code using DAO.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query: Improve Performance (2002)

    Hmmmmmmmmm

    Looks like this needs to be done with VBA ?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query: Improve Performance (2002)

    You can type the following keywords in the Visual Basic Editor (in a module or in the Immediate window) and press F1 to get help on them:

    TransferDatabase to import a table.
    CreateIndex to create a new index on a table.
    CreateRelation to create a new relationship between two tables.

    The latter two require that you have set a reference to the Microsoft DAO 3.6 Object Library.

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query: Improve Performance (2002)

    Ok Hans.
    Thanks, Thats what I'll do.

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query: Improve Performance (2002)

    Hans

    Wouldn't it be easier to run a make table query on a splash and then a delete query on closing the db ?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query: Improve Performance (2002)

    Hi Dave,

    This would only be possible if your database will be never used by more than one user simultaneously. If there are multiple users, it would result in chaos.

    How often do the data in the external database change? Perhaps it would be enough to refresh the data once a day, or once a week.

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query: Improve Performance (2002)

    Hans

    The idea behind the db is basically to pull information for reporting only.
    There would possibly only two users MD and Accounts to pull month end results which the data would be 99% last months.
    I don't intend giving any of the users the facility to write to the ODBC tables at all.
    Other information I would need (Which is not part of the DOS tables), I can create Access tables and link them.

    So creating Make table queries becomes possible, how about the relationships, VBA still ?

    BTW

    I enterred CreateRelation in the Immediate window which came back with no help.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query: Improve Performance (2002)

    If the data are those from last month, there is no need to refresh them each time the database is opened. And even with only two users, if they could be active at the same time, you should NOT import the tables when the database is opened, and delete them when the database is closed.

    > I entered CreateRelation in the Immediate window which came back with no help.
    Do you have a reference to the Microsoft DAO 3.6 Object Library?

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query: Improve Performance (2002)

    Ok Hans, I understand the issue with Multiple users.

    Do you have a reference to the Microsoft DAO 3.6 Object Library?, No I hadn't but is ok now.

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query: Improve Performance (2002)

    I had the same problem with ODBC data from an Accounting package and like you I was really only using it for reporting. Eventually I got to the stage that each month, I delete all the data in the tables then reimport the data into the existing tables (thus preserving relationships but being sure I had up to date data) . Means I spend 15 mins or so each month importing all the data but overcame the ODBC speed problem
    Steve (holy molly an answer from me rather than a question - It will probably turn out I misunderstood the question :-0
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

Page 1 of 2 12 LastLast

Posting Permissions

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