If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Slow queries when joining local to SQL Server tables
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" |
#3
|
|||
|
|||
Slow queries when joining local to SQL Server tables
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! |
#4
|
|||
|
|||
Slow queries when joining local to SQL Server tables
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"? |
#5
|
|||
|
|||
Slow queries when joining local to SQL Server tables
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! |
#6
|
|||
|
|||
Slow queries when joining local to SQL Server tables
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"? |
#7
|
|||
|
|||
Slow queries when joining local to SQL Server tables
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 |
#8
|
|||
|
|||
Slow queries when joining local to SQL Server tables
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 |
#9
|
|||
|
|||
Slow queries when joining local to SQL Server tables
"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 |
#10
|
|||
|
|||
Slow queries when joining local to SQL Server tables
Yes there is an SQL Server index on dbo_asset.tag_number
|
|
Thread Tools | |
Display Modes | |
|
|