Stored Procedure taking time in execution

views:

answers:

Stored Procedure taking time in execution

Dear All,

I am breaking my head on this issue since long. I have stored procedure in MS SQl and when I try to execute that procedure by providing all the parameters in SQL Query, it takes long time to execute but when I try to directly run the query which is there in SP it executes in no time. This is affecting my application performance also as we are using stored procedures to fetch the data from DB Server.

Please help.

Regards,

Vikram

+1 A:

Looks like parameter sniffing.

Here is a nice explanation: I Smell a Parameter!

Basically, sql server has cached query execution plan for the parameters it was first run with so the plan is not optimal for the new values you are passing. When you run the query directly the plan is generated at that moment so that's why it's fast.

You can mark the procedure for recompilation manually using sp_recompile or use With Recompile option in its definition so it is compiled on every run.

Giorgi 2010-10-18 11:26:38

+1. I'd look at `OPTIMIZE FOR` or assigning to local variables before resorting to `WITH RECOMPILE` though.

Martin Smith 2010-10-18 12:57:12

@Martin - Yes, I agree with you. Unfortunately sql server 2005 does not support OPTIMIZE FOR UNKNOWN.

Giorgi 2010-10-18 13:00:11

Dear Giorgi,One of my colleague came up with a solution on the Trail and Run Basis and it worked fine.We did LTRIM and RTRIM of the varchar parameter in the query in the SP as shown below.Select CustomerID , CustomerName from Customer where LTRIM(RTRIM(CustomerName)) = LTRIM(RTRIM(@CustomerName))We are not sure if this is the right way to resolve this issue, and how is it helping out in resolving this issue.Please suggest

Vikram 2010-10-18 13:06:13

@Vikram - I guess it has the same effect as assigning parameters to local variables. However I would not use the solution you posted as it is will not be able to utilize indexes. The articles I linked to show a much better alternatives.

Giorgi 2010-10-18 13:08:34

@Giorgi - I tried assigning parameters to the local variables but it didn't help me to sort out this problem. Any other help would be appreciated.

Vikram 2010-10-18 13:35:46

ansaurus

tags:

views:

answers:

Stored Procedure taking time in execution

related questions