September 7, 2005 at 2:32 pm
Hi,
I have a stored procedure that joins several tables and returns about 100 rows from that. Two of these tables are pretty large, one has 500 million records and the other has 6 million records. The other tables are relatively small with about 8000 rows, 3000 rows or less. I'm using SQL Server 2000 as the database server.
When I ran the stored procedure from Query Analyzer, it completes in 2 seconds. The same stored procedure when called from ASP.NET however takes 18 seconds to execute. Any ideas on why this happens ?
Thanks
Bala.
September 7, 2005 at 2:39 pm
Hard to diagnose,
Try running
dbcc freeproccache
which will clean sql server procedure cache and destroy any saved execution plans then try to run again.
Other than this and verifying the parameters are the same as your running in QA I do not know what to do.
September 7, 2005 at 6:37 pm
Ray,
Thanks for your reply. Clearing the procedure cache definitely helped. It seems to me that for the same stored procedure, there is one cached execution plan for Query Analyzer and another one for the .NET SqlClient. Here's what seems to be happening.
Two arguments of the stored procedure are @StartDate and @EndDate. Currently, the maximum difference between @StartDate and @EndDate is 1 day. After I create the stored procedure, if I first run with parameters @StartDate = '7/1/05 00:00' and @EndDate = '7/1/05 23:59' in Query Analyzer, this executes in 2 seconds. The execution plan for this gets cached and now if I call the stored proc with @StartDate = '7/1/05 06:00' and @EndDate = '7/1/05 07:59', it takes only about 1 second.
But if I do the opposite, i.e. re-create the stored procedure and run the 2 hour query first, in Query Analyzer, it generates a bad execution plan and the 2 hour query takes 9 seconds to execute. Now if I run the full day query with this execution plan, it takes more than a minute to complete.
I think what happened in my case was Query Analyzer got the good execution plan and ran fast but the SqlClient got the bad execution plan and was slow. I cleared the stored procedure cache and ran the full day query first from the SqlClient and that solved the problem. Future runs were pretty fast from the .NET code as well.
But this new problem of good and bad execution plans is still there and I'm posting that as a new thread for discussion as "Stored Procedures - How to control execution plans".
Bala.
September 8, 2005 at 5:07 am
It looks like it has been due to 'Parameter Sniffing' technique used by the query optimizer.
You may find useful information regaring this issue from the following link.
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
September 8, 2005 at 2:21 pm
Thanks. The link was very useful. It solved the problem.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply