Query Speed

[Manuel]

Hi,

I have a database which contains tables which are linked (via ODBC) to
tables in my company's data warehouse. The tables vary in size and number of
records, but the larger tables can contain upwards of 2 Million records.

When I query the data, particularly when I join two large tables, or even
one small table with one large table, Access can take up to several minutes
to process the query.

I don't understand why Access is so slow in processing the query when the
tables are linked; I thought linking tables would reduce network traffic and
speed database performance.

Could someone please provide suggestions for how I may be able to speed up
the performance of my queries?

The database itself is only 1.56 MB in size (it’s only a test database as
I’m in the early stages of development. But if the database is this slow
not, I can only imagine when I start adding forms and reports).

Thanks,

Manuel

 

[Jerry Whittle]

Check out SQL pass-through queries. They do the processing out on the
database server and only return the requested records. If you do a standard
select query from within Access, Access needs to get at all the records on
the server then return the proper records.

http://support.microsoft.com/default.aspx/kb/303968

 

[Dorian]

Seeing your queries might help.
Also how fast is your PC? that can be a significant factor.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Jeff Boyce]

Manuel

Factors that affect query speed:

* Indexing -- are the underlying tables/views indexed on every field used
in joins, sorts, selection?
* Network -- network speed; NICs; traffic
* functions -- your query involves "local" functions (i.e., functions
that exist in Access but not in your data warehouse)

Any of those apply in your situation?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Manuel]

Thanks for enlightening me in regards to Pass-Through queries; I’d heard
about them before but had never actually used them. Unfortunately, for
whatever reason, the pass-through query is not any quicker than the regular
select query I had. Any other suggestions?

Thanks,
Manuel

 

[Bernard Peek]

Hi,

I have a database which contains tables which are linked (via ODBC) to
tables in my company's data warehouse. The tables vary in size and number of
records, but the larger tables can contain upwards of 2 Million records.

When I query the data, particularly when I join two large tables, or even
one small table with one large table, Access can take up to several minutes
to process the query.

The delay will probably be caused by the way Access queries the central
database, forcing the server to do more work than it should. You are
probably going to have to apologise to the database administrator for
slugging the performance of the database for all of the other users. You
aren't the first person to do this. It is one reason why a lot of
companies don't allow users to connect Access databases to their
production servers.

Others have already mentioned pass-through queries. Bear in mind that
pass-through queries should be written in the dialect of SQL used by the
server and not the version used by Access. You need to understand more
about the server system in order to write code that runs efficiently on
the server.

I don't understand why Access is so slow in processing the query when the
tables are linked; I thought linking tables would reduce network traffic and
speed database performance.

It does, mostly. But ODBC uses a subset of SQL that can result in your
Access databases sending commands to the server that it finds difficult
to execute.

What type of database are you connecting to?