Slow queries when joining local to SQL Server tables

I manage a SQL Server data warehouse and my customers use Access to create
ad-hoc queries of the data. A customer is creating a local table in Access to
store some 200-300 serial numbers and then joining that to a remote table
that's linked in via ODBC that contains 1,000,000+ records. If I perform the
query using SQL Server it returns in under 2 seconds (there is an index on
the serial number field) but if I mimic his query it takes forever (cut it
off after 20 minutes or so). I suspect that it's sending down all 1,000,000+
records and letting the local computer process the query. Is there any way in
Access to force the query to process on the server? The database is a
read-only data warehouse so making a table on the server for him to load with
the records he's searching for isn't an option. Thanks!

Jon M wrote:
I manage a SQL Server data warehouse and my customers use Access to
create ad-hoc queries of the data. A customer is creating a local
table in Access to store some 200-300 serial numbers and then joining
that to a remote table that's linked in via ODBC that contains
1,000,000+ records. If I perform the query using SQL Server it
returns in under 2 seconds (there is an index on the serial number
field) but if I mimic his query it takes forever (cut it off after 20
minutes or so). I suspect that it's sending down all 1,000,000+
records and letting the local computer process the query. Is there
any way in Access to force the query to process on the server? The
database is a read-only data warehouse so making a table on the
server for him to load with the records he's searching for isn't an
option. Thanks!

How about creating a linked server to the Access db on the SQL Server. Then
use a passthrough query to perform the join on the server.
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

It's quite possible that Access is requesting the whole table, a lot of
conditions can force that. However, as you didn't give us the structure of
the tables and of the query involved, it's impossible to tell you more on
this.

In your case, you should take a look with the SQL-Server Profiler to see
what queries Access is sending to the SQL-Server. If this is the case, then
you should rework your query to eliminate the full query of the table by
Access.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Jon M" wrote in message
...
I manage a SQL Server data warehouse and my customers use Access to create
ad-hoc queries of the data. A customer is creating a local table in Access
to
store some 200-300 serial numbers and then joining that to a remote table
that's linked in via ODBC that contains 1,000,000+ records. If I perform
the
query using SQL Server it returns in under 2 seconds (there is an index on
the serial number field) but if I mimic his query it takes forever (cut it
off after 20 minutes or so). I suspect that it's sending down all
1,000,000+
records and letting the local computer process the query. Is there any way
in
Access to force the query to process on the server? The database is a
read-only data warehouse so making a table on the server for him to load
with
the records he's searching for isn't an option. Thanks!

How about creating a linked server to the Access db on the SQL Server. Then
use a passthrough query to perform the join on the server.
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


I have 1,500 or so customers and don't have any control over what databases
they are creating locally (and usually don't communicate with them directly).
I read on http://support.microsoft.com/kb/286222 that Jet will:

"Joins between small local tables and large remote tables, where the join
column is indexed, may result in a remote index join. In a remote index join,
one query for each row in the local table is sent to the server, and only the
joining rows are returned."

But that doesn't appear to be happening. When I do a test with only one
record in the local table it takes 3-4 minutes for the result to come back.
Is there a way to force a "remote index join"?

If it's taking that long, it may be querying each individual record
from the server (1000000+ separate queries).

(david)

"Jon M" wrote in message
...
I manage a SQL Server data warehouse and my customers use Access to create
ad-hoc queries of the data. A customer is creating a local table in Access
to
store some 200-300 serial numbers and then joining that to a remote table
that's linked in via ODBC that contains 1,000,000+ records. If I perform
the
query using SQL Server it returns in under 2 seconds (there is an index on
the serial number field) but if I mimic his query it takes forever (cut it
off after 20 minutes or so). I suspect that it's sending down all
1,000,000+
records and letting the local computer process the query. Is there any way
in
Access to force the query to process on the server? The database is a
read-only data warehouse so making a table on the server for him to load
with
the records he's searching for isn't an option. Thanks!

Just out of curiousity.

What is the time for a query using a literal value? That is instead of
using the local table, just use a value. If that still takes 3-4
minutes then something besides the local table is causing the
performance problem.

Troubleshooting without seeing the query involved and without knowing
the version of Access and MS SQL server is problematic.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'================================================= ===


Jon M wrote:
How about creating a linked server to the Access db on the SQL Server. Then
use a passthrough query to perform the join on the server.
--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


I have 1,500 or so customers and don't have any control over what databases
they are creating locally (and usually don't communicate with them directly).
I read on http://support.microsoft.com/kb/286222 that Jet will:

"Joins between small local tables and large remote tables, where the join
column is indexed, may result in a remote index join. In a remote index join,
one query for each row in the local table is sent to the server, and only the
joining rows are returned."

But that doesn't appear to be happening. When I do a test with only one
record in the local table it takes 3-4 minutes for the result to come back.
Is there a way to force a "remote index join"?

Thanks eveyone for your responses. If I query with a literal I get an answer
back in seconds.

Here is a simple query that took about 20 minutes to return data (note that
the "tbltag2" local table only contains one record):

Select tbltag2.tag, dbo_asset.tag_number
from tbltag2 left join dbo_asset on tbltag2.tag=dbo_asset.tag_number

You may need it to be a LEFT join, but did you test the speed with an INNER JOIN?
Just Curious. Also, is there a SQL Server INDEX on dbo_asset.tag_number?
--
Danny J Lesandrini

www.amazecreations.com



"Jon M" wrote ...
Thanks eveyone for your responses. If I query with a literal I get an answer
back in seconds.

Here is a simple query that took about 20 minutes to return data (note that
the "tbltag2" local table only contains one record):

Select tbltag2.tag, dbo_asset.tag_number
from tbltag2 left join dbo_asset on tbltag2.tag=dbo_asset.tag_number

"Inner join" instead of left join doesn't seem to show any improvement.

Version information: Access 2003 SP3, SQL Server Enterprise Edition 8.00.679
SP2

Yes there is an SQL Server index on dbo_asset.tag_number