Did you try it without the 'EXEC' ?
Paul Mitchell
2009-04-29 11:28:32
Yes. Times out as well.
Ole Lynge
2009-04-29 11:29:31
A:
We had a similiar issue, where a query would complete in 2 seconds in SSMS and take more than 90 seconds when called from a .NET client (we wrote several VB/C# apps/sites to test it.)
We suspected that the query plan would be different, and rewrote the query with explicit looping ("inner loop join" and "with index") hints. This solved the problem.
Andomar
2009-04-29 11:30:44
Yeah, we have a weekly maintenance plan for statistics and indexes (runs every sunday.) We recreated the stored procedure to remove any cached query plans.
Andomar
2009-04-29 11:50:30
+2
A:
This is almost certainly due to an 'incorrect' cached query plan. This has come up on SO quite a few times.
Due you have up-to-date statistics? A regular scheduled index maintenance plan?
You can test if it is definitely due to the cached query plan by adding this to your stored procedure definition:
CREATE PROCEDURE usp_MyProcedure WITH RECOMPILE...
This will re-index an entire database (caution if database is very large!):
exec sp_msforeachtable "dbcc dbreindex('?')"
SO posts:
Big difference in execution time of stored proc between Managment Studio and TableAdapter.
Parameter Sniffing (or Spoofing) in SQL Server
Mitch Wheat
2009-04-29 11:35:12
I ran the exact same queries (EXEC sp_Stat @DepartmentID = NULL) right after each other. First the one that took 2 seconds with SSMS. Then (a few seconds later) the one that timed out from .NET. Do you think it might still be related to an incorrectly cached query plan?
Ole Lynge
2009-04-29 11:39:57
either that or statistics are out of date, or indexes need to be rebuilt (which updates stats on the respective columns)
Mitch Wheat
2009-04-29 11:43:46
I use exact same parameters. And the WITH RECOMPILE option does not change the outcome...
Ole Lynge
2009-04-29 12:17:42
The articles you reference seem to talk about parameterized queries with an execution time that depends on the value of the parameters. I have the exact same values in these tests...
Ole Lynge
2009-04-29 12:21:01
Thanks. The solution was related to parameter sniffing. It helped to store the values of the sproc's input parameters in local variables...
Ole Lynge
2009-05-13 13:30:17
+4
A:
Another thing that can be important is the SET options that are enabled. Some of these options change the query plan sufficiently to change the profile. Some can have a huge impact if you are looking at (for example) a calculated + persisted (and possibly indexed) column: if the SET options aren't compatible, it can be forced to re-calculate the values, rather than using the indexed value - which can change an index seek into a table scan + calculation.
Try using the profiler to see what SET options are "in play", and see if using those options changes things.
Another impact is the connection string; for example, if you enable MARS that can change the behaviour in subtle ways.
Finally, transactions (implicit (TransactionScope) or explicit) can have a huge impact, depending on the isolation level.
Marc Gravell
2009-04-29 11:56:14
Thanks. I will look into how to examine the SET options. I guess it might be related to the difference, because otherwise the queries are identical...
Ole Lynge
2009-04-29 13:13:35
Hi Marc,I have been having this issue for years on a DB that im running, and the only solution has been to SET ARITHABORT ON/OFF on the stored proc. Unfortuntley it keeps on happening and i have to change the ARITHABORT option to the opposite of what it was.Do you know of any way to tell my .NET app to use the same SET options as SSMS will use?
Eli Perpinyal
2010-02-10 11:02:08