Query takes 35 minutes to run with linked table.

[Guest]

A simple select statement is taking 5 minutes to run in SQL Server. There
are no sorts, groupings or where blocks, simply a Select Field1,Field2, ....
From Table. The table contains 606,775 records. When a query is run from
Access that uses a linked table to that table in SQL Server, it takes 35
minutes to run. When the same query is run from Access linked to a table in
another Access application also with 606,775 records, it runs in 35 seconds.
Any ideas ?

 

[Guest]

Access may be requesting the records one at a time, using
606,775 separate queries.

How long does it take to open the linked table? How long
to scroll to the last record?

How long does it take to open the query? How long to
scroll to the last record?

If it is just the form that has problems, remove any linked
combo boxes (use joins in the query instead), and check
to make sure that you aren't causing the problem while
changing the form recordset.

(david)

 

[John Spencer]

IS the linked Access table on the same computer, while the SQL table is
on a server on the network? If so, that is probably a good part of the
explanation for the performance difference.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================