Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008

    Unanswered: Query Performance

    Good Morning.

    I have a table with about 160 columns and it takes quite a long time to query the data. I already have the table indexed on the 6 main fields used for selecting the data. Aside from breaking down the table into several smaller tables, is there anything else that can be done to increase performace?


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    firstly make sure your expectations are realistic, what you think is poor performance may be acceptable to the user.
    query performance is a thorny issue in Access/JET
    the general rules are
    index columns you use in a where clause
    try not to use functions (especially user writen functions) in JET queries
    only retrieve the data you actually need dont use select *, use select my,required,columns
    I suspect the number of columns may be an issue, I only come accross a few with that number of columns, one needed that many, the others had a suspect design.. you may need to revisit that design

    after that you are into more esoteric problems that are harder to pin donw
    make sure the network connection between where the data is stored and the computer its used on is adequate.. make sure its not choked, isn't having network problems

    theres hardware issues of making certain the PC has sufficient resources. ie enough system memroy. Access can be prone to problems with machines that run software which has memory leaks (eg where software closes without releasing memory back to the computer), thiosw seems to be a problem with graphics programs and programs from small companies/individuals. try not to open many programs at the same time
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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