[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
Question
[x]
Attachment Details

How to make odbc delete query run faster

Asked by Mach1pro in Microsoft Access Database

I have an Access 2007 front end connected to an sql server back end with odbc tables. I have a simple SP in sql server that only takes one parameter to delete the records from a table. Run from SQL enterprise manager, this delete sp runs in under a second.  In the Access code, I am using ado to connect to sql server and then run the SP as an adodb command.  When I first open Access and run the command, it will take anywhere from 10-20 seconds to execute.  I can change the parameter, run it again (or use the same parameter) and it will execute in a 1-2 seconds.  If I close Access, reopen it and run it again, it will still run in 1-2 seconds. If I restart the machine, open Access and rerun it, then the first time run is back to 10-20 seconds.

What is going on here and how can I speed up the initial execution of this query?
I've already tried running an Access delete query directly against the odbc table and its slower than using the adodb command.
[+][-]09/22/09 03:46 AM, ID: 25391288Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]09/22/09 05:42 AM, ID: 25392103Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]09/22/09 06:31 AM, ID: 25392538Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]09/30/09 09:09 AM, ID: 25460359Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]09/30/09 10:52 AM, ID: 25461442Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]09/30/09 11:05 AM, ID: 25461565Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]09/30/09 11:18 AM, ID: 25461696Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]09/30/09 11:20 AM, ID: 25461715Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]10/07/09 03:08 AM, ID: 25513638Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20091021-EE-VQP-81 - Hierarchy / EE_QW_3_20080625