none
Slow pass-through query from Access to SQL 2008 R2 RRS feed

  • Question

  • I have an Access 2003 pass-through query that runs very slowly on our production SQL server but runs quickly on our test server. Both servers have exact same copies of the database. Both servers are running SQL 2008 R2 on MS Server 2008 and both were configured to be as identical as possible.

    The query returns about 500 records. When executed from SSMS, the query takes :14 on the test server and :07 on the prod server (the prod server is a much faster machine). When run as a pass-through query to the test server, records are returned in :14 (as fast as the query runs on SQL). But when passed through to the prod server, it times out at over 3:00.

    I tested this by changing only the ServerName from the prod server to the test server in the ODBC connection string for the pass-through query. "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=mydb;UID=****;PWD=*****"

    Where can I begin troubleshooting this problem? I know very little about performance monitoring and troubleshooting, so tell me where to start. TIA for any help.

    Friday, January 7, 2011 6:37 AM

Answers

  • Well, a bad query plan could be tied to a particular login; this would explain why the query is running fast from one machine but not from another if they are using different login.  Even if you have cleared the buffer and rebuilt the indexes and statictics, you could still be affected by that if you are hit by the parameter sniffing problem of SQL-Server.  That would also give you the very same symptoms because it could generate a bad query plan on a machine but not on another.

    This is why I've suggested to you to add the WITH RECOMPILE option to the SP.  It is not sufficient to recompile the SP for debugging this kind of problem.

    The network protocol used for the ODBC connection could also be the culprit here.  Make sure that you are using the TCP/IP protocol in both cases.  If the problem is still there, try with the Named Pipes protocol.  Don't forget that SSMS don't necessarily use the same protocol as your Access connection. Normally, the protocol used shouldn't affect the performance but if you have some kind of problem with your Active Directory Server - if you use one - or a network that is not well setup, maybe this is where the problem is coming from.  However, if I were you, I would loose to much time on that.

    If you know how to do it, you could try calling your SP through an ADO connection or an ADP project instead of going a MDB/ACCDB file with ODBC Linked Tables and Passthrough queries.  Maybe this could shed some light on this.  However, looking at what's going on on the server and especially, looking for any bad query plan would be my first option if I were you.

    Does all these machines running on the same version of Windows - including the Service Packs - and Office/Access?   Maybe it's a corruption problem or a bad reference in the MDB or ACCDB file.  Check the references and try to decompile/repair/compact your MDB file.  You could also try with a new, blank database file.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    <Mike Storms> wrote in message news:263c2e11-e582-4db6-b963-9dbbeea4563a@communitybridge.codeplex.com...

    Sylvain,

    Again I want to thank you taking so much time to respond in detail. I hope you don't think I am obstinent or just dense, but I don't see how the problem could be with the stored procedure. Since the SP runs fast directly on the server (executed from SSMS), how could the problem be with the SP itself (given that I definitely do have connectivity from the pass-through query)? Likewise, since the pass-through query runs fast when connected to one server but slow when connected to another--and both servers have the same copy of the database, how could it be a problem with the SP?

    BTW, I do use fully qualified objects and I have rebuilt the indexes and statistics and recompiled the SP.

    If you're still certain that the SP itself is the likely culprit, I will work with the profiler some more. I didn't want to bother you with details about the SP at this time, as that is probably another thread altogether!

    Thanks again.

    • Marked as answer by Mike Storms Monday, January 10, 2011 8:34 PM
    Friday, January 7, 2011 8:18 PM

All replies

  • What do yo mean with times out?  You get no result at all?  Maybe the passthrough query simply cannot connect to the sql-server?

    In order to eliminate any authentication isssues, do you have other passthrough queries that work properly or can you make this passthrough query to work properly (fast and with a result) by changing its Where condition so that something fast (like a single addressed record) will be returned?

    If you are calling a parameterised stored procedure (SP), you might be victim of a bad query plan.  What happens if you add the option WITH RECOMPILE to the SP?

    When an SQL-Server application is running slow, the first thing to do is to make sure that the statistics are up to date by using sp_updatestats stored procedure (or use UPDATE STATISTICS is you want to work on a more detailed level) and cleaning the caches after that:

    DBCC FLUSHPROCINDB
    DBCC DROPCLEANBUFFERS
    DBCC FREEPROCCACHE

    If this doesn't work, you can try reindexing everything.

    You should also search Google for « Sql-server parameter sniffing » as this is common optimizing problem with SQL-Server; with or without Access.

    Another possibility would be with the driver.  With this connection string, it looks like that you are using the older ODBC provider for SQL-Server that you can find on your server or on your client machine.  Has you have SSMS installed on your machine, you should be able to use the latest ODBC Native Driver.  See http://www.connectionstrings.com/ for more info.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    <Mike Storms> wrote in message news:7d449fe2-1470-4ddb-afa1-5df3cbb84f3f@communitybridge.codeplex.com...

    I have an Access 2003 pass-through query that runs very slowly on our production SQL server but runs quickly on our test server. Both servers have exact same copies of the database. Both servers are running SQL 2008 R2 on MS Server 2008 and both were configured to be as identical as possible.

    The query returns about 500 records. When executed from SSMS, the query takes :14 on the test server and :07 on the prod server (the prod server is a much faster machine). When run as a pass-through query to the test server, records are returned in :14 (as fast as the query runs on SQL). But when passed through to the prod server, it times out at over 3:00.

    I tested this by changing only the ServerName from the prod server to the test server in the ODBC connection string for the pass-through query. "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=mydb;UID=****;PWD=*****"

    Where can I begin troubleshooting this problem? I know very little about performance monitoring and troubleshooting, so tell me where to start. TIA for any help.

    Friday, January 7, 2011 11:15 AM
  • Thank you Sylvain for your very detailed response. I appreciate all the suggestions on how to optimize the query, but I guess I didn't make it clear that the query runs fine when executed directly on the SQL server (both test and production servers). I really don't think the problem is with the query or with SQL.

    You asked if the pass-through query does complete successfully if I change the parameters such that it only returns fewer records. Yes it does. Again, I didn't make it clear that I have exact copies of the database on both servers and that the only factor I change is which server the ODBC connection string points to in the pass-through query. When it points to the test server, the query completes in :14. When it points to the production server, it times out at 3:00. (I could set the timeout value to 0 to see exactly how long it takes, but I haven't done that yet.)

    I forgot to mention one very important difference: I'm running Access on the same computer as the test database! (Duh!) So obviously there is a big difference in that when querying the test server I'm not going over the network.

    Finally you asked if I have tried the newer ODBC Native Driver connection string. I have, and it makes no difference.

    I think I need to troubleshoot the ODBC connection, but I have no idea how to do that.

    Friday, January 7, 2011 3:02 PM
  • When you can get a valid connection, a bad query plan if probably the most common problem with SQL-Server after deadlocks and this is something that would give us this kind of symptom: something that is going when you connect to SQL-Server from another machine, application or login but that goes painfully slow when connecting from the intended application/login; so if I were you, I would definitely look into this first.

    Second, you didn't provide any detail at all about your query.  For exemple, you don't even say if you are calling a SP or a full text query and you don't say neither if your objects are fully qualified or not.  There is very big difference between writing "Select * from MyTable" and "Select * from dbo.MyTable".  This little dbo. can make all the difference in the world when it comes to getting a bad query from a particular user or application.

    Getting your hand on how to use the SQL-Server Profiler and particularly, how to capture the query plans would be a great idea in your case; however, you don't need to do this if first, you make the simple tests that I've shown you like clearing the buffer or adding the WITH RECOMPILE option if you are calling a SP.

    Another potentiel source of problem would be the Active Directory Server if you are using it for authentication.  Trying with a SQL-Server login - as it seems that you are already doing but we can never be sure when looking exclusively at the connection string - can rule out this potential problem.

    As to troubleshooting the ODBC connection, I think that's very unlikely that your problem is coming from that; especially if you have tried another with another provider.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    <Mike Storms> wrote in message news:a9298981-4f3f-4ccf-8a7b-cb027334aedf@communitybridge.codeplex.com...

    Thank you Sylvain for your very detailed response. I appreciate all the suggestions on how to optimize the query, but I guess I didn't make it clear that the query runs fine when executed directly on the SQL server (both test and production servers). I really don't think the problem is with the query or with SQL.

    You asked if the pass-through query does complete successfully if I change the parameters such that it only returns fewer records. Yes it does. Again, I didn't make it clear that I have exact copies of the database on both servers and that the only factor I change is which server the ODBC connection string points to in the pass-through query. When it points to the test server, the query completes in :14. When it points to the production server, it times out at 3:00. (I could set the timeout value to 0 to see exactly how long it takes, but I haven't done that yet.)

    I forgot to mention one very important difference: I'm running Access on the same computer as the test database! (Duh!) So obviously there is a big difference in that when querying the test server I'm not going over the network.

    Finally you asked if I have tried the newer ODBC Native Driver connection string. I have, and it makes no difference.

    I think I need to troubleshoot the ODBC connection, but I have no idea how to do that.

    Friday, January 7, 2011 7:32 PM
  • Sylvain,

    Again I want to thank you taking so much time to respond in detail. I hope you don't think I am obstinent or just dense, but I don't see how the problem could be with the stored procedure. Since the SP runs fast directly on the server (executed from SSMS), how could the problem be with the SP itself (given that I definitely do have connectivity from the pass-through query)? Likewise, since the pass-through query runs fast when connected to one server but slow when connected to another--and both servers have the same copy of the database, how could it be a problem with the SP? 

    BTW, I do use fully qualified objects and I have rebuilt the indexes and statistics and recompiled the SP.

    If you're still certain that the SP itself is the likely culprit, I will work with the profiler some more. I didn't want to bother you with details about the SP at this time, as that is probably another thread altogether!

    Thanks again.

    Friday, January 7, 2011 7:57 PM
  • Well, a bad query plan could be tied to a particular login; this would explain why the query is running fast from one machine but not from another if they are using different login.  Even if you have cleared the buffer and rebuilt the indexes and statictics, you could still be affected by that if you are hit by the parameter sniffing problem of SQL-Server.  That would also give you the very same symptoms because it could generate a bad query plan on a machine but not on another.

    This is why I've suggested to you to add the WITH RECOMPILE option to the SP.  It is not sufficient to recompile the SP for debugging this kind of problem.

    The network protocol used for the ODBC connection could also be the culprit here.  Make sure that you are using the TCP/IP protocol in both cases.  If the problem is still there, try with the Named Pipes protocol.  Don't forget that SSMS don't necessarily use the same protocol as your Access connection. Normally, the protocol used shouldn't affect the performance but if you have some kind of problem with your Active Directory Server - if you use one - or a network that is not well setup, maybe this is where the problem is coming from.  However, if I were you, I would loose to much time on that.

    If you know how to do it, you could try calling your SP through an ADO connection or an ADP project instead of going a MDB/ACCDB file with ODBC Linked Tables and Passthrough queries.  Maybe this could shed some light on this.  However, looking at what's going on on the server and especially, looking for any bad query plan would be my first option if I were you.

    Does all these machines running on the same version of Windows - including the Service Packs - and Office/Access?   Maybe it's a corruption problem or a bad reference in the MDB or ACCDB file.  Check the references and try to decompile/repair/compact your MDB file.  You could also try with a new, blank database file.


    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server (French)

    <Mike Storms> wrote in message news:263c2e11-e582-4db6-b963-9dbbeea4563a@communitybridge.codeplex.com...

    Sylvain,

    Again I want to thank you taking so much time to respond in detail. I hope you don't think I am obstinent or just dense, but I don't see how the problem could be with the stored procedure. Since the SP runs fast directly on the server (executed from SSMS), how could the problem be with the SP itself (given that I definitely do have connectivity from the pass-through query)? Likewise, since the pass-through query runs fast when connected to one server but slow when connected to another--and both servers have the same copy of the database, how could it be a problem with the SP?

    BTW, I do use fully qualified objects and I have rebuilt the indexes and statistics and recompiled the SP.

    If you're still certain that the SP itself is the likely culprit, I will work with the profiler some more. I didn't want to bother you with details about the SP at this time, as that is probably another thread altogether!

    Thanks again.

    • Marked as answer by Mike Storms Monday, January 10, 2011 8:34 PM
    Friday, January 7, 2011 8:18 PM