Stored proc executes >30 secs when called from website, but <1 sec when called from ssms

views:

answers:

+1 Q:

Stored proc executes >30 secs when called from website, but <1 sec when called from ssms

I have a stored procedure that is called by a website to display data. Today the web page has started timing out so I got profiler going and saw the query that was taking too long. I then ran the same query in management studio, under the same user login, and it takes less than a second to return.

Is there anything obvious that could be causing this? I can't think of a reason why when ASP calls the stored proc it takes 30 secs but when I call it it's fine.

Thanks

I guess, there might be two reasons:

Network problem
Parameter sniffing

Cagdas 2010-05-11 08:30:09

Sounds like a dead lock.

Chris Bednarski 2010-05-11 08:35:27

No it doesn't! That would cause one of the participating transactions to be rolled back and you'd get an error message telling you that a deadlock had occurred.

Martin Smith 2010-05-11 09:05:39

+1 A:

This is usually because some of the SET-tings differ between the Management Studio connection and the ASP connection, such as SET ARITHABORT. This wouldn't explain why it's only started being problematic today from the website call, but there's a fair chance it's related.

Rob 2010-05-11 08:40:24

There's also some good answers in http://stackoverflow.com/questions/2736638/sql-query-slow-in-net-application-but-instantaneous-in-sql-server-management-stu/ that may well be related

Rob 2010-05-11 08:42:09

I'm not sure that it is the Settings per se that would explain the speed difference? More usually if you change one of these settings you will get a new or different execution plan so it looks like changing the setting fixed it but actually the culprit was parameter sniffing.

Martin Smith 2010-05-11 09:08:59

I experienced this, too, with ARITHABORT. The issue was to query an index on a computed column, which performs better when ARITHABORT is set.

devio 2010-05-11 20:20:51

It seemed to be parameter sniffing... I've stopped the sniffing by assigning the passed in parameters to local variables and it seems to be fine at the moment (i.e. it's running under a second from the website again). It'll be interesting to see if it stays like this or will degrade again.

I had assumed running with the option RECOMPILE would have temporarily 'fixed' the parameter sniffing problem for the query in question but it didn't.

Ah well. Thank you everyone for answering. I'll see what happens

Blootac 2010-05-11 08:52:04

If this ends up being the answer, please accept it once you're sure. I mention it since you're new here, and welcome!

rwmnau 2010-05-11 08:57:19

We had a similar issue with our IVR - when I ran a query through SSMS, it returned instantly, but when it was run through a webservice accessed by our IVR, it would time out about 20% of the time - really odd.

I ended up running SQL Profiler to see the queries being submitted and then added some additional indexes per the recommendations of the Index Tuning wizard, which sped up the IVR query to under a second every time. I suspect the problem was also something to do with parameters, and while I didn't compare the execution plan between the two different venues, I suspect they were quite different. SQL Profiler will help you sort this out, though, since you can see the query actually submitted to the engine, as well as the execution plan it uses to fetch the data.

rwmnau 2010-05-11 09:00:50

ansaurus

tags:

views:

answers:

Stored proc executes >30 secs when called from website, but <1 sec when called from ssms

related questions