Reputation: 543

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

Upvotes: 1

Answers (5)

SqlRyan

Reputation: 33924

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.

Upvotes: 0

Blootac

Reputation: 543

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

Upvotes: 0

Rob

Reputation: 45789

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.

Upvotes: 1