Query runs slow as Stored Procedure

Question

Post reply

Query runs slow as Stored Procedure

baumgam1@sutterhealth.org

Old Hand

Points: 384
More actions
July 1, 2003 at 1:26 pm

#80389

Hi All,
We have a stored procedure that takes over a miniute to run as a stored procedure but takes only a second in query analyzer. The query in the sp uses an int variable that is passed at run time. If we provide a hard coded value in the sp then it runs quickly. Replace it with the varibale and takes a long time.
Any ideas?
Thanks for any help...
SQL 2K SP3, Windows 2000 Server

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Can you post the code or something similar to it that does the same?

baumgam1@sutterhealth.org Old Hand Points: 384 More actions · Answer 2

Below is a portion of sp. @COLLECTOR_ID is the culprit. If I replace the variable with a hard coded value it runs quickly.

CREATE PROCEDURE dbo.ABT_Select_Collector_LastWorkDate_NotReviewed

--Add Parameters Here

@COLLECTOR_ID INT,

@LAST_WORK_DATE VARCHAR(50),

@PROCESS_GROUP_ROLE_ID INT,

@NT_USER_NAME VARCHAR(50)

<snip>

WHERE

SGP.PROCESS_GROUP_ROLE_ID = @PROCESS_GROUP_ROLE_ID

ANDLWD.LAST_WORKDATE <= @LAST_WORK_DATE

ANDCU.CORE_USER_ID = @COLLECTOR_ID

AND LOAN.PrincipalBal >1

Thanks in advance

well0549 SSCrazy Points: 2259 More actions · Answer 3

Is there any looping / cursors done in the sproc......

jpipes SSChampion Points: 12230 More actions · Answer 4

Analyze the Execution plan for both the sproc and the statement in QA to see whre the differences are. Also, have you recompiled the sproc?

Stuart Davis Old Hand Points: 342 More actions · Answer 5

This is most likely a parameter sniffing issue.

Your proc will run fine if you declare and use a local variable for the query. SET the local variable to the value of the parameter passed in to the sp.

Hth

Stu

Randy Rabin SSCommitted Points: 1511 More actions · Answer 6

Idea 1: add WITH RECOMPILE to the proc.

Idea 2: turn the query into dynamic SQL using exec('...'), with the values of the parameters inserted into the query string.

croesus Grasshopper Points: 22 More actions · Answer 7

Use Query Analyzer to look at the execution plan for the statement when you pass in a variable and when you provide the value direct. If you have multiple indexes on a table, I've found that the optimizer can choose the wrong one when querying using a variable. If that's the case you can use an index hint to dictate which index should be used.

jonespm Ten Centuries Points: 1393 More actions · Answer 8

Hi,

I'll just relate an incident from just a couple of days ago. I had a 10 second proc that ran < 1 second in QA. Took me a while to do what you have been advised to do.

Instead of putting the sql into QA I put:

exec stp_MyProc

And the execution plan was completely different to the sql code!!

On closer inspection the code was found to be faulty (Left joins that where not needed). This was fixed and now both the raw SQL and proc have the same execution plan and run sub second.

Cheers, Peter

JteL SSC Enthusiast Points: 131 More actions · Answer 9

I recently observed the same problem. As Stu suggests, assign the parameters to variables and use the variables in the query. This worked for me.

pmoscattini SSC Enthusiast Points: 139 More actions · Answer 10

I have had this problem. In my stored proc I was using a temp (#) work table. I changed this to a permanent table that was created and dropped within the stored proc and all worked well.