Slow queries when joining local to SQL Server tables

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

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


"Sylvain Lafontaine" wrote:

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)

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)


"Jon M" wrote in message
...
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


"Sylvain Lafontaine" wrote:

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)

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" wrote:

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)

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" wrote in message
news
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" wrote:

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)

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" wrote:

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)

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)


"Jon M" wrote in message
...
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" wrote:

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)

On Fri, 06 Feb 2009 14:21:34 +1100, david wrote:

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.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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

"Rick Brandt" wrote:

On Fri, 06 Feb 2009 14:21:34 +1100, david wrote:

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.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com