Varying Query Performance

[Bill]

Hi All,

When running more complex queries on larger data sets I get massive
variation in response times.

Sometimes I run the query and response is instant, then immediately after it
will take a couple of minutes. Queries are in one database and the data is
in another (though on the same hard disk - this is running it on my laptop)
Compact and repair helps but I would have to do it every time!! Also
sometimes performance comes back without doing a C&R.

This is a Tosh Portege Tablet (M200) running XP. memory has been upgraded to
1GB. There is plenty of disk space.

Any idea what might be going on or how I troubleshoot this please.

Ta.
Bill.

 

[Gary Walter]

So many things, you might have to be
more specific...

Is your remote data in mdb?

If so, the most effective thing one can do
is open a connection to the remote db
when start program, and only close
connection when shut program down.

All my apps have a "switchboard" form
that is bound to a "minimal" query of table
in backend, something like

"Select ID FROM tblDummy Where ID=1"

These guys "wrote the book"

Access 2002 Desktop Developer's Handbook
Chapter 15: Application Optimization
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4009c15.asp

Other sources:

Optimizing Client/Server Performance
http://www.esdrm.pt/CentroInformatica/tutoriais/access/Manual/BLDAPPS/chapters/ba19_6.htm

(I understand it is not over a network, but...)
How to optimize Office Access and Jet database network performance...
http://support.microsoft.com/default.aspx?scid=kb;en-us;889588

 

[Bill]

Gary,

Yes - the remote database is in mdb, but it reside on the same laptop as the
application which is also mdb. The link between the two is by linked tables.

Thanks for the links.
Bill.