Why is DAO.QueryDef is so slow

[Richard]

When I create a DAO.QueryDef in Access 2000 that calls a
SQL stored procedure, the querydef takes about 40 seconds
to return the recordset (about 100 records). The same
stored procedure with the same parameters executed in SQL
Server Query Analyzer takes less than one second. We're
running SQL Server 2000.

I thought pass-through queries would have nearly the same
performance as executing the query in QA since the work is
passed to SQL Server. Why do you think my QueryDef is so
slow?

Thanks.

 

[prabha]

Hi Richard,

Think of the overhead involved this way:

Linked table: ---> Access ---> JET ---> ODBC ---> SQL Server
PassThrough: ---> Access ---> ODBC ---> SQL Server
Query Analyzer: ---> SQL Server

Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."


--------------------
| Content-Class: urn:content-classes:message
| From: "Richard" <[email protected]>
| Sender: "Richard" <[email protected]>
| Subject: Why is DAO.QueryDef is so slow
| Date: Wed, 25 Feb 2004 14:02:16 -0800
| Lines: 13
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcP76we6evP20WAjT9mVrlDRKuV+Dg==
| Newsgroups: microsoft.public.access.queries
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:191472
| NNTP-Posting-Host: tk2msftngxa09.phx.gbl 10.40.1.161
| X-Tomcat-NG: microsoft.public.access.queries
|
| When I create a DAO.QueryDef in Access 2000 that calls a
| SQL stored procedure, the querydef takes about 40 seconds
| to return the recordset (about 100 records). The same
| stored procedure with the same parameters executed in SQL
| Server Query Analyzer takes less than one second. We're
| running SQL Server 2000.
|
| I thought pass-through queries would have nearly the same
| performance as executing the query in QA since the work is
| passed to SQL Server. Why do you think my QueryDef is so
| slow?
|
| Thanks.
|