 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / August 2006Strange Query Behavior
Access 97 with split FE/BE I have a query based on 5 tables with one inner join and 3 outer joins in FE. Two tables have about 125,000 records each, one has about 17,000 records and two have about 80 records. The query has parameters derived from form textboxes and is used for a form's recordsource. Performance has been excellent until I tried to change the query. With the original query, parameters that would return results in 1-2 seconds, not take 8+ seconds. Initially, I thought that I inadvertently changed something that I did not intend to, so I imported the original query from a backup copy of the FE. It ran fine. Opened the query in design view, changed nothing and did File > SaveAs ... Performance of the new query dropped like a rock -- NOTHING WAS CHANGED! Tried several times and got same results. I have decompiled, compiled, repaired and compacted. Can anyone offer an explanation/solution?  SignatureAG Email: discuss at adhdata dot com When you save a query, Access saves an execution plan with it. If you alter the query, it recalcualtes the best execution query, and saves that, even if nothing else changed. It would seem that for this particular query, the original execution plan (calculated on whatever the data was originally) is better than the new execution plan (based on the current data.) That's unusual, but not impossible. Here's a really silly idea. Temporarily move the back end to a different location, and restore the old back end, with data similar to what would have been there when you originally saved the query. Do what you like with the query, and save it. It might save the execution plan like your original one. Then restore your current back end, and the saved query with the old plan just might behave like it used to. These articles might provide a starting point for understanding the execution plan: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/o dc_4009c15.asp http://builder.com.com/5100-6388-5064388.html http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/h tml/ODC_MicrosoftOfficeDeveloperForumMicrosoftAccessMicrosoftJetDatabaseEngine.a sp SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Access 97 with split FE/BE > I have a query based on 5 tables with one inner join and 3 outer joins in [quoted text clipped - 20 lines] > > Can anyone offer an explanation/solution? Thanks Allen, I will check out the articles. The last time the query was modified/saved was probably almost a year ago. I am working remotely via TS and client does daily backups, but he would not have one that old. SignatureAG Email: discuss at adhdata dot com > When you save a query, Access saves an execution plan with it. If you > alter the query, it recalcualtes the best execution query, and saves that, [quoted text clipped - 41 lines] >> >> Can anyone offer an explanation/solution? Hello, Since it is Access 97 database, you may want to refer to the following article to truoblehsoot the issue: ACC: How to Optimize Queries in Microsoft Access 2.0, Microsoft Access 95, and Microsoft Access 97 http://support.microsoft.com/default.aspx?scid=kb;[LN];112112 Also, it is suggested that you consider upgrade latest Access version so that you might be get better support since Access 97 has ended Office Family Product Support Lifecycle FAQ http://support.microsoft.com/default.aspx?scid=fh;en-us;lifeOffice Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. ====================================================== Thanks for the reply Peter, The article was informative. I have been working in Access for a long time and remember when Rushmore first came out. It was supposed to be so wonderful and fast. In fact it was so fast that many of my queries ran at least 50% slower (yes, that is slower, not faster). I had to redesign them just to get to 90% of pre-Rushmore! Of course, I could be wrong, but I doubt that upgrading to a later version of Access would do anything performance-wise. Feature-wise, there is nothing in the newer version that this app needs. As for support, I think these forums are the best for issues like this. I was able to get near original performance by creating a query with the few fixed parameters necessary, using it a a base to build a new recordsource with the added user-entered parameters, in code and then applying that to the form's recordsource. SignatureAG Email: discuss at adhdata dot com > Hello, > [quoted text clipped - 22 lines] > rights. > ====================================================== Hello, I would like to know whether the KB article could help you on this issue. If you have any questions or concerns, please feel free to let us know. Sincerely, Wei Lu Microsoft Online Community Support |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.