"Smart querying" to speed up?

[Guest]

Hi,

Although I have a few books on MS Access I have not come over any topic that
discuss the aspects of speed when creating queries.

For example, in my case I'm doing a "simple" query that draws the material
from several tables (some very large), most tables are linked (ODBC) and the
querying can be really slow,.. which made me start thinking about ways to
construct more efficient queries.

So, if someone could give some general advise, or point to an applied
textbook on how to speed up the queries I'd be really thankful.

(One thing that should boost the query would be if I could, somehow, make
one query filter the biggest table, and then run the other queries AFTER
that... sort of "sequential queries"... but I don't know how).

Cheers,
Mikael
Sweden

 

[Rick Brandt]

Hi,

Although I have a few books on MS Access I have not come over any
topic that discuss the aspects of speed when creating queries.

For example, in my case I'm doing a "simple" query that draws the
material from several tables (some very large), most tables are
linked (ODBC) and the querying can be really slow,.. which made me
start thinking about ways to construct more efficient queries.

So, if someone could give some general advise, or point to an applied
textbook on how to speed up the queries I'd be really thankful.

(One thing that should boost the query would be if I could, somehow,
make one query filter the biggest table, and then run the other
queries AFTER that... sort of "sequential queries"... but I don't
know how).

Cheers,
Mikael
Sweden

Since you're using ODBC I assume these tables reside on a server-based database
like SQL Server. If so, then while standard Access queries against ODBC linked
tables can often run just fine, joins (especially complicated joins) are better
to be processed completely on the server. That means building Views on the
server to perform the joins and then linking to those or using passthrough
queries.

The worst case performance-wise is a query that combines links from different
sources or combines links with local tables. Unless the trables from one of
those sources is very small these kinds of queries will always be slow and there
is not much you can do except experiment with different configurations on the
query.

Sometimes breaking a single query into multiple smaller ones and then combining
those in a final query helps a lot. If you need to run such a query multiple
times for a task or multiple queries will use the same linked data for a task
then pulling that data into a local table first might at least make the second,
third, etc., queries faster.

 

[Jeff Boyce]

To Rick's suggestions I'll also add that a rough rule of thumb is to have
indices set on any field used to:
* join tables
* select
* sort
This is only a rough "rule", not an absolute.

But if your tables (SQL-Server or local) have no indexing, you can expect
your queries to take longer.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/