SQL Server thread with Access and long queries

[Guest]

We have a SELECT INTO query in Access that is Appending to an Access Table.
We have made sure that all /Temp folders have been cleaned up. The SELECT
INTO is going out to get SQL Server data via ODBC. The query in Access is
taking between 7 and 10 minutes. When we rip the query out of Access and run
it in Query Analyzer, it is taking 30 seconds. So we're pretty sure the query
is efficient. In both cases, it is returning 7,000+ rows.

Can anyone shed any light onto any thread issues that they have encountered
between Access and SQL Server and how they went about resolving them???

Any help would be GREATLY appreciated.

 

[John Vinson]

We have a SELECT INTO query in Access that is Appending to an Access Table.
We have made sure that all /Temp folders have been cleaned up. The SELECT
INTO is going out to get SQL Server data via ODBC. The query in Access is
taking between 7 and 10 minutes. When we rip the query out of Access and run
it in Query Analyzer, it is taking 30 seconds. So we're pretty sure the query
is efficient. In both cases, it is returning 7,000+ rows.

Can anyone shed any light onto any thread issues that they have encountered
between Access and SQL Server and how they went about resolving them???

Any help would be GREATLY appreciated.

Are you appending into a local JET Access table, or a linked SQL
table?

If the local table, my guess is that you're running into the overhead
of updating the indexes on the table. It may be necessary to drop the
indexes, run the append, and then re-index the table. :-{(

John W. Vinson[MVP]

 

[Guest]

I have found it to be much quicker to run views and stored procedures on the
server instead of a query in access. Your local machine probably does not
have the horsepower of the server, and the traffic over the network slows it
down also.