Best practice advise - performance problem

[AlexT]

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

 

[Philipp Stiefel]

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...

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

 

[Robert Morley]

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

 

[david epsom dot com dot au]

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)

 

[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

 

[dbahooker]

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

 

[dbahooker]

rob

compact and repair in ADP?

i mean.. seriously here

 

[AlexT]

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

 

[aaron.kempf]

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