May 28, 2008 at 3:09 pm
We recently upgrade from SQL 7 to 2005. We have access database and VB dlls/exes that query the databases. In some cases some of the sprocs now have timeout expired issues. If run the same exec statement in query analyzer the query takes less than 1 second to as much as 6 seconds. In access or using ADO it takes 4 minutes or more if I increase the timeout above the default 30 seconds. It also seems that if I use a pass through query and use the SQL Native CLient driver instead of the SQL Server driver it performs like query analyzer does so I tried updating my connection strings in ADO to use the same driver, but it has not helped.
Any ideas/recommendations?
May 29, 2008 at 6:00 am
The first thing that comes to mind... Have you updated the statistics and/or rebuilt the indexes after moving the data from 7 to 2005? It's my understanding that this is one of the biggest problems after a migration of this type (I've been lucky and just built new databases for upgrades, so far, knock on wood). After that... I'd be curious what the queries look like and what your indexes are like. By and large, 2005 should operate faster than 7 in most cases.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 29, 2008 at 8:41 am
We did rebuld the indexes. The problem only seems to be in vb using ADO. The problem with Access Pass-thorugh queries seems to be fixed by switching DSN's to use the SQL Native Client driver instead of the SQL Server driver, but the same fix did not work for ADO. The sprocs we use contain subqueries, and in at least one case seems to have been resolved by adding a fix for parameter sniffing that I found in another thread.
May 29, 2008 at 8:46 am
So the query would run fine in SSMS, but when run from VB it times out?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 29, 2008 at 9:09 am
yes when I run it from query analyzer as a sproc or if I copy the code script and run it as an ad hoc query it takes no more than 6 seconds. If I run the same sproc using the ADO command object it times out. I can increase the timeout for the command object and it will run, but I have to increase the timeout to about 5 minutes which seems way to long for the query
May 29, 2008 at 9:13 am
OK. Run Profiler. Be sure you have one of your ADO connections on line. You can just use the defaults. Check the connection settings of the ADO connections. I'll be they've got some non-standard ANSI settings or something that's causing the problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 29, 2008 at 9:14 am
Have you tried running profiler while trying the ADO solution? In other words - double check that what is being passed really looks like you'd expect it. I've seen some thing mess up the parameter typing pretty badly, confusing the Stored proc to no end.
Also - have you tried defining the Stored Proc WITH RECOMPILE? It might help.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply