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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9337-sql-server-thread-access-long-queries.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9337
between Access and SQL Server and how they went about resolving them???

Any help would be GREATLY appreciated.

On Wed, 10 May 2006 0902 -0700, wnfisba
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9337
wrote:

>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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9337
>
>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]

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.

"John Vinson" wrote:

> On Wed, 10 May 2006 0902 -0700, wnfisba
> wrote:
>
> >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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9337
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9337
> >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]
>