SQL ODBC Connection running slow
-
We use SQL Server 2000 SP3. We have a query that when executed in query analyzer takes about 3 minutes. When this same query is run thru a third-party application using an ODBC Connection it takes about 20 minutes. This query inserts a large number of records into a temporary table. Are there any ODBC Settings that may affect the execution time of a query? What other things may I check to troubleshoot why this query takes so long when executed from the third-party application via ODBC?
Thanks in advance, Kevin
-
I don't think the problem is necessarily ODBC.
I have seen this problem before but was unable to specifically find the issue with the stored procedure. You need to evaluate the stored procedure execution plan and ensure it is running efficiently, this may include reindexing. and or updating statistics. Also Recompile the stored procedure by executing with Recompile. Also make sure the application server is not overloaded with other traffic ie disk io and CPU are not out of whack doing other things during the execution of the procedure.
Tough one to answer not being able to see the actual hardware.
-
1. Is this a STORED-PROCEDURE or an AD-HOC QUERY?
2. Is this using DYNAMIC SQL?
A possible reason for QA running MUCH faster is because QA is running off of the SERVER and NOT your PC.
The application is probably running from your PC when run via the 3rd party tool
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Is it possible that the network be slower on one side (if the proc returns large amount of data).. or maybe it's just the app that process the data harder, while QA just presents it.
-
ODBC and OLEDB use SQL Server features that need to be kept in sync with the MDAC version the 3rd party application is using. So check the 3rd party application (client machine) MDAC version and discuss this with the author of the 3rd party application.
When you install SQL Server or apply a service pack you will find a script in: C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql. When you update the client machine's MDAC this same script is installed in the system32 folder. These days the Windows service packs handle MDAC for you, however for legacy Windows you should keep client MDAC versions as close as you can with the Server MDAC version.
You can check your server's "catalog" version for SQL 2000 using:
SELECT attribute_value, LTRIM(RTRIM(SUBSTRING(@@VERSION,CHARINDEX('-',@@VERSION)+1,(CHARINDEX('(',@@VERSION)-1)-CHARINDEX('-',@@VERSION))))
FROM master.dbo.spt_server_info
WHERE attribute_name = 'SYS_SPROC_VERSION'
If the version numbers do not match, then run the instcat.sql script on the master database.
If the 3rd party application is receiving the error with SQLState: 01000 - The ODBC catalog stored procedures installed on server <server name> are version <old version>; version <new version> or later is required to ensure proper operation. Please contact your system administrator.
Then this is the fix, however many times you never see this message unless the application handles multiple errors as 1st is the ODBC error, and 2nd is the SQL Server driver error.
I have seen this affect the performance and expected results from applications.
This also affects linked servers, so if you are linking SQL 7 or SQL 6.5, then you have to install the latest SQL Server 2000 instcat.sql on your older version SQL Servers.
Andy
-
could be DNS lookup issues on the machine using the 3rd party app...?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply