 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / December 2005Best practice advise - performance problem
Folks I'm having a performance problem with an ADP project I wish to submit to your collective wisdom... In summary I have a search form and a detail form connected to a fairly complex underlying query. The search form allows the user to select a small subset of the underlying query and everything works fine. It takes some time to run the query but it's acceptable. Now the problem is when, from the search form, the user requests the detail form. Typically I set the record source of the said form as being my base query and specify the PK (SELECT * FROM qBASE WHERE myUniqueIdx = 11111). Again this works fine... but I have a very bad performance problem, as it seems that the underlying query is re-run in full as soon as I set the recordsource property of my detail form. So opening the form takes an unacceptably long time... How would you tackle this ? Any suggestion welcome --alexT > In summary I have a search form and a detail form connected to a fairly > complex underlying query. [quoted text clipped - 12 lines] > soon as I set the recordsource property of my detail form. So opening > the form takes an unacceptably long time... I think we'll have to know more details about qBASE to come up with a decent solution. Nevertheless, my approach would probably be to change the RecordSource of the detail form to something like: SELECT * FROM base_table WHERE myUniqueIdx =11111 If it isn't feasible to query the base_table, you should try to build a very simple (and fast) query instead of the complex and slow seaqrch-query. Cheers Phil I'm not 100% sure I understood where/why the requery is taking place, but something that *might* help (or might not) is to set the RecordSource's of all forms involved programmatically in the Form_Open event, and leave them blank in the underlying form properties. If nothing else, this should give you finer control over what is requeried when. I don't have it in front of me right now to confirm, but I believe if Access doesn't see the form as bound, it'll be a little less aggressive about auto-requerying when you don't want it to. Rob > Folks > [quoted text clipped - 23 lines] > > --alexT This may not be relevant, but when working with MDB/ODBC linked tables, set the new recordsource TWICE: frm.RecordSource = "..." frm.RecordSource = "..." When working with MDB-ODBC-SQL Server, this has an obscure side effect which sometimes makes the refresh much faster. -- and you might as well try it and see if it makes any difference here. (david) > Folks > [quoted text clipped - 23 lines] > > --alexT Thanks for all the feedback. I have ended up creating a separate query for the detail form, with a bunch of lookups. Works fine... --alexT cool.. by the way; do you know indexing; or do you use the index tuning wizard? one of the best things about using ADP-- if you keep most of the tables in one database; then you can use the index tuning wizard with pretty awesome results.. if nothing else; it helps to make sure you dont miss a couple of required indexes.. -aaron > cool.. by the way; do you know indexing; > or do you use the index tuning wizard? To be frank I have yet to obtain any usefull output out of the index tuning wizard... Anyone with a good tutorial ? Thanks ---alexT well it only works off of queries that work within a single database you know what i mean? if you joni a table in 1 db to a table in another db; index tuning wizard wont help i'd check out www.sqlserverperformance.com -aaron As odd as this may seem, have you Compact & Repaired the database recently? Have you created a new database and imported all the objects from the old data to the new database? Rob Mastrostefano  SignatureFMS Professional Solutions Group http://www.fmsinc.com/consulting Software Tools for .NET, SQL Server, Visual Basic & Access http://www.fmsinc.com > Folks > [quoted text clipped - 23 lines] > > --alexT rob compact and repair in ADP? i mean.. seriously here |
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.