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....
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'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
-- 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
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.
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.
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.