SP inside SP very slow

Hi All,

I've got an SP being passed a datetime param. The sql runs very efficiently if the store procedure is run with the string passed. However, I have a stored procedure that identifies the date to be passed and then passes that date time to the SP, it runs excessively slow. It takes < 1 minute with running the SP by itself, and then over 4 hours with it run from within the parent SP.

I've attempted recompiling, (i've got local variables set which helped for a while), option optimization....

I'm stuck. Any suggestions?

Can you isolate the long-running proc? e.g. if you comment out the call to the child proc in the parent proc and just run the parent proc, how long does it run?

I just commented out the slow running child proc. The parent proc took 20 seconds inclusive of identifying the date to be passed.

OK -- time to post some code.

I'm going to only copy the first part of this SP for now because i've got logging in place - it doesn't get past this first part. IF you think i need to paste the whole SP let me know, it's kind of a biggie. (This is the slow running proc called within the parent proc)

ALTER procedure [dbo].[SP_POS_SKU_IND_PriceArb] @ind_processing_date_tmp datetime
with recompile
as

DECLARE @vn_error_number numeric (20)
DECLARE @vs_error_message varchar(200)
DECLARE @vs_log_message varchar(200)
DECLARE @Ind_processing_date datetime

DECLARE @vn_step_ct int
DECLARE @vs_process varchar(30)
SELECT @vs_process = OBJECT_NAME(@@PROCID)
Select @ind_processing_date = @ind_processing_date_tmp

-- This will insert the line item refund of the Price Arbitrage records into a new line on POS_SKU_IND

update ind
set PriceArb = updt.netamount
from POS_SKU_IND ind
inner join (
select fls.TransDate
, fls.StoreNum
, fls.RegNum
, fls.TransNum
, fls.LineNum
, fls.NetAmount
from NORD_View_MERCH fls
inner join NORD_View_TND rtrn
on fls.TransDate = rtrn.Transdate
and fls.StoreNum = rtrn.StoreNum
and fls.RegNum = rtrn.RegNum
and fls.TransNum = rtrn.TransNum
inner join NORD_View_STORE flss
on flss.storenum = fls.StoreNum
inner join NORD_View_MERCH rack
on fls.UPC = rack.UPC
and rack.NetAmount > 0
inner join NORD_View_STORE racks
on racks.StoreNum = rack.storenum
inner join NORD_View_TND purch
on rack.TransDate = purch.Transdate
and rack.StoreNum = purch.StoreNum
and rack.RegNum = purch.RegNum
and rack.TransNum = purch.TransNum
and purch.AccountToken = rtrn.AccountToken
where (racks.business_unit = '2'
and flss.business_unit = '1'
and rack.NetAmount > 0 and fls.NetAmount < 0 ) --Identifies purchase at rack and return at FLS
and ABS(fls.netamount) > rack.NetAmount -- identifies they recieved more money than the purchase price
and (fls.TransDate > rack.TransDate or
(fls.TransDate = rack.TransDate and fls.TransTime > rack.TransTime)) -- identifies that the refund must happen on or after the purchase
and fls.POP_Ind in ('1','2','7') -- Identifies that the return must be either No POP, PRA (found UII at rack) or unapplied (found older UII at rack which won't infer PRA)
and fls.DateLoaded = @ind_processing_date
) updt
on ind.Transdate = updt.TransDate
and ind.Storenum = updt.StoreNum
and ind.Regnum = updt.RegNum
and ind.Transnum = updt.TransNum
and ind.tLinenum = updt.LineNum

I don't see a specific "code" entry

Additionally, I replace the variable in the master proc with a date string, and it ran quickly.

You should use the parameter directly in the query, and not create a local variable to replace it. That is, remove this line:
...
--DECLARE @Ind_processing_date datetime --remove this line!
--Select @ind_processing_date = @ind_processing_date_tmp --remove this line!
and change the WHERE condition:
and fls.DateLoaded = @ind_processing_date_tmp

Also, what is the datatype of column "fls.DateLoaded"? Is it a datetime/datetime2/date/other?

I can try this - but everything else i've read re: parameter sniffing says to create the a local parameter. Initially, i had this the way you are describing and it was slow also.

date loaded is datetime

So i made this change and admittedly it seems to be working for now. We didn't have many transactions for today's load date so it isn't really a benchmark date. I left the recompile in there. I'll keep an eye on this for a few days.

Also, thank you for your input.

Actually it's normally better to use the actual parameter value because that's what SQL builds the plan for. If there are still issues with parameter sniffing, then you add RECOMPILE as you did. But the practice of using local variables is now generally a mistake in SQL Server.

Of course there could be exceptions, mostly if you're forced to pass in a value type that has a higher precedence than the column it's being to. For example, say your parameter has to be an int -- it's been so decreed by some big cheese and you can't get around it -- but the column is a varchar. In that case, using the parameter directly would force SQL to do an implicit conversion on the column, which is horribly bad for performance. Therefore, in such cases, you do create a local variable of the correct type and convert the parameter to it.