Slow queries when joining local to SQL Server tables

[Jon M]

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!

 

[Bob Barrows]

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.

 

[Sylvain Lafontaine]

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.

 

[Jon M]

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"?

 

[david]

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

(david)

 

[John Spencer]

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]

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

 

[Danny Lesandrini]

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?

 

[Jon M]

"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

 

[Jon M]

Yes there is an SQL Server index on dbo_asset.tag_number

 

[John Spencer]

That particular query could be rewritten to

SELECT Tag_Number
FROM dbo_Asset
WHERE Tag_Number IN
(SELECT Tag from tblTag2)

That may be more responsive.

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

 

[Jon M]

Hi,

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

Profiler shows that the statement actually being passed contains no
selection criteria so it is passing back the entire 1,000,000+ records. Any
hints at reworking the query in Access so that the server does the work would
be appreciated.

Jon

 

[david]

Is this dynamic SQL or a saved query? Compact the database,
create a saved query, and try it again.

Compacting the database resets the database hints and discards the
saved query plans. Saved query plans sometimes work differently
from dynamic SQL.

Using a single left join like that should work as well as an inner join.

There is no obvious reason why this should be downloading all
records - I used to do this all the time and it never took 4 minutes,

Also, even when I downloaded all records it never need to complete
the download unless it was joining to a combo box on a datasheet. -
for normal use I could continue with the first tranche.

How are you 'running' the query?

What version of SQL Server and which ODBC driver?

(david)

Hi,

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

Profiler shows that the statement actually being passed contains no
selection criteria so it is passing back the entire 1,000,000+ records.
Any
hints at reworking the query in Access so that the server does the work
would
be appreciated.

Jon

 

[Jon M]

Hi David,

The customers are using Access to create and run the query from the Access
query design screen. They are actually querying a view of the data that's
stored in a table in a different database on the same server. The underlying
table is recreated every night (via BCP, a snapshot of data from a different
system) along with the indexes. The version of SQL Server is Enterprise
Edition 8.00.679 SP2 and the ODBC driver is Microsoft SQL (sqlsrv32.dll)
version 2000.85.1117.00. SQL Profiler shows that the actual query statement
Access is sending to SQL Server contains no selection criteria, just the
select fields. I'm stumped.

Jon

 

[david]

I wonder if the SQL Server database statistics are correct for that table?
Relink the linked table - that link contains an unknown amount of
hidden information.

You could try the "Native Client" driver which is probably installed
on the client machine already to see if there is an anomaly with the
2000 driver.

If you haven't done so already, compact the Access database, create
a saved query, and try it again.

(Also make sure Auto-correct is disabled in the Access database).

Are you able to find a client who does not have this problem?

(david)

 

[Jon M]

I created a new dblink using the native SQL client, create a new Access
database and re-linked the table using the new ODBC connection. Same problem.
Here is some more info on what's going on between Access and SQL (and no I
haven't found any customers who are not having the issue):

SQL Statement in Access:
SELECT dbo_CASMALL.ACCOUNT, dbo_CASMALL.TAG_NUMBER
FROM dbo_CASMALL
WHERE (((dbo_CASMALL.TAG_NUMBER)="572115"));

What Profiler sees:
SELECT "ACCOUNT" ,"TAG_NUMBER" FROM "dbo"."CASMALL" WHERE ("TAG_NUMBER"
= '572115' )


SQL Statement in Access:
SELECT dbo_CASMALL.ACCOUNT, dbo_CASMALL.TAG_NUMBER
FROM tag INNER JOIN dbo_CASMALL ON tag.tag = dbo_CASMALL.TAG_NUMBER;

What Profiler sees:
SELECT "ACCOUNT" ,"TAG_NUMBER" FROM "dbo"."CASMALL"

It's never sending any selection criteria when joining a local table to a
remote table. Is there no way in Access to force a remote index join?

Thanks for continuing to try to help.

Jon

 

[david]

I'm flummoxed: I'm not in a situation where I can test this, but, like
I said, I didn't know that I had this this problem when I was using
SQL Server 2000.

There is no way to force Access to use any particular join method.


If none of the methods suggested here help then there may be
no easy solution.

(david)

 

[Rick Brandt]

I'm flummoxed: I'm not in a situation where I can test this, but, like I
said, I didn't know that I had this this problem when I was using SQL
Server 2000.

There is no way to force Access to use any particular join method.


If none of the methods suggested here help then there may be no easy
solution.

In my experience in doing this (sometimes it is the only way and I make
sure the local table is a small one), the SQL that is sent to the server
is like...

SELECT *
FROM ServerTable
WHERE FieldName = LocalRow1Value
OR FieldName = LocalRow2Value
OR...

While these are inelegant looking statements, they have always performed
just fine for me. Again, if I don't allow the local table to contain too
many rows.

It's a mystery to me why this isn't working the same in this case.

 

[Jon M]

Hi,

That's what I would expect, it looks like what the Microsoft documentation
describes as an indexed join (if the remote table field you're querying has
an index on it). I just can't get it to work on the system I inherited. Maybe
it's something on the sql box that makes Access think it can't handle a it.

Jon