October 7, 2008 at 12:30 pm
Please give me all pointers, tips and techniques to improve this monster... Thanks a lot
CREATE PROCEDURE sp_CTReplenishStoreInventory
(
@StorePartner_UID as int,
@Client_UID as int,
@Location_UID as int
)
AS
BEGIN
DECLARE
@StoreHierarchy_UID as int,
@CurrentDayOfWeek as varchar(4),
@StoreReplenishmentRule_UID as int,
@DistributionCenter_UID as int,
@ProductType_UID as int,
@OrderMaxQty as int,
@OrderMinQty as int,
--@Location_UID as int,
@StoreDayOfWeek as int,
@ProcessProductsAboveMinFlag char(1),
@TotalCardsOnOrderBelowMin as int,
@TotalCardsOnOrderAboveMin as int,
@TotalCardsOnOrder as int,
@OrderReferenceNumber as bigint,
@errorcode as int,
@ErrorMessage as varchar(300),
@StoreReplenishment_UID as int,
@Creator as varchar(30),
@CurrentDate as DateTime,
@ReplDateOut datetime,
@return_value int,
@cmd varchar(500)
--SET @Location_UID =4
--IF @StorePartner_UID not in (68 ,697, 698)
-- BEGIN
--Select 0 as 'StoreReplenishment_UID'
-- RETURN
--END
SET @Creator = 'DSD-AR-' + convert(varchar(10),GetDate(),10)
--ADDED BY CHANDAN SHARMA ON 14/08/07
--UPDATE ON_HAND_QTY AND ON_ORDER_QTY OF STORE INVENTORY IF IT HAS NEGATIVE VALUE
EXEC sp_BHUpdateStoreInventory
--TILL HERE
EXEC sp_CTGetReplenishmentUID
@Creator,
@StorePartner_UID,
@StoreReplenishment_UID output
SET @CurrentDayOfWeek =Datepart(dw,GetDate())
DECLARE c_StoreHierarchies CURSOR FOR
SELECT
StoreHierarchy_UID
FROM
V_CTStoreHierarchies HS
INNER JOIN CTAgentInfo AI ON AI.Agent_UID = HS.Agent_UID
WHERE
StorePartner_UID = @StorePartner_UID
ANDAutoReplenishmentFlag = 'Y'
--AND AI.Location_UID = @Location_UID
AND STOREHIERARCHY_UID IN(SELECT STOREHIERARCHY_UID FROM CTSTOREINVENTORIES WHERE ISNULL(LOCATION_UID,0)=@LOCATION_UID AND REPLENISHFLAG='Y')
Order by
StoreHierarchy_UID
OPEN c_StoreHierarchies
FETCH c_StoreHierarchies
INTO
@StoreHierarchy_UID
WHILE @@Fetch_Status = 0
BEGIN
set @cmd =''
set @cmd ='echo .................................................. >> C:\logfile.txt'
exec master..xp_cmdshell @cmd,no_output
set @cmd =''
set @cmd = 'echo ('+CAST(getdate()AS VARCHAR)+')--- StoreHierarchy_UID= '+CAST(@StoreHierarchy_UID AS VARCHAR)+' StorePartner UID= '+CAST(@StorePartner_UID AS VARCHAR)+' >> C:\logfile.txt'
exec master..xp_cmdshell @cmd,no_output
set @cmd =''
--Make sure the store has a property record-for card type and distribution center.
EXEC sp_CTGetStoreProperties
@StoreHierarchy_UID,
@StoreReplenishmentRule_UID output,
@DistributionCenter_UID output,
@ProductType_UID output
--if @StoreReplenishmentRule_UID = 0 or @DistributionCenter_UID = 0 or @ProductType_UID = 0
--Concept of distribution center is obsolete. The location UID of the products do the
--Same thing.
set @cmd = 'echo sp_CTGetStoreProperties --- StoreReplenishmentRule_UID = '+Cast(@StoreReplenishmentRule_UID as varchar)+' DistributionCenter_UID = '+Cast(@DistributionCenter_UID as varchar)+' ProductType_UID = '+Cast(@ProductType_UID as varchar)+' >> C:\logfile.txt'
exec master..xp_cmdshell @cmd,no_output
set @cmd =''
if @StoreReplenishmentRule_UID = 0 or @ProductType_UID = 0
BEGIN
EXEC sp_CTStoreReplenishmentErrorLog_Insert
@StoreHierarchy_UID,
@StoreReplenishment_UID,
'Error in getting store properties for this store.'
GOTO ContinueLoop
END
--Check to see if it is the store's day for replenishment
--SET @CurrentDate = CAST(FLOOR(CAST(GetDate() AS float)) AS datetime)
SET @CurrentDate = GetDate()
--SET @CurrentDate = N'03-14-2007'
EXEC@return_value = [dbo].[sp_CTGetNextReplenishmentDate]
@StoreHierarchy_UID,
@CurrentDate,
@ReplDateOut OUTPUT
set @cmd = 'echo sp_CTGetNextReplenishmentDate --- REPLANISHMENT DATE OUTPUT = '+Convert(varchar(12),@ReplDateOut,106)+' >> C:\logfile.txt'
exec master..xp_cmdshell @cmd,no_output
set @cmd =''
IF @return_value = 1000
BEGIN
EXEC sp_CTStoreReplenishmentErrorLog_Insert
@StoreHierarchy_UID,
@StoreReplenishment_UID,
'Error in getting next replenishment date for this store... check Store Replenishment Schedules.'
--PRINT Convert(varchar,@StoreHierarchy_UID) + ' missing store sched'
GOTO ContinueLoop
END
--check if this store is scheduled for replenishment today
IF ( convert(varchar(10),@ReplDateOut, 101) <> convert(varchar(10),@CurrentDate,101)
OR @return_value = 1010)
BEGIN
--PRINT Convert(varchar,@StoreHierarchy_UID) + ' not sched'
GoTo ContinueLoop
END
--PRINT Convert(varchar,@StoreHierarchy_UID) + ' scheduled for rep'
--GoTo ContinueLoop
EXEC sp_CTGetStoreOrderMaxMin
@StoreHierarchy_UID,
@StoreReplenishmentRule_UID,
@OrderMaxQty output,
@OrderMinQty output
set @cmd = 'echo sp_CTGetStoreOrderMaxMin --- ORDER MAX = '+ Cast(@OrderMaxQty AS VARCHAR)+' '+'ORDER MIN = '+Cast(@OrderMinQty AS VARCHAR)+' >> C:\logfile.txt'
exec master..xp_cmdshell @cmd,no_output
set @cmd =''
if @OrderMaxQty = 0 and @OrderMinQty = 0
BEGIN
EXEC sp_CTStoreReplenishmentErrorLog_Insert
@StoreHierarchy_UID,
@StoreReplenishment_UID,
'Error in getting Order max and min quantity for this store.'
GOTO ContinueLoop
END
--location is now passed in as a parameter so batches are run by location
--Set @Location_UID = dbo.udf_CTGetDefaultWarehouseByStore(@StoreHierarchy_UID)
if @Location_UID = 0
BEGIN
EXEC sp_CTStoreReplenishmentErrorLog_Insert
@StoreHierarchy_UID,
@StoreReplenishment_UID,
'Error in getting default warehouse for this store.'
GOTO ContinueLoop
END
if @Location_UID = 2 GOTO ContinueLoop--Only Sacramento and Contac Toronto are on DSD
if @Location_UID = 3 GOTO ContinueLoop--Only Sacramento and Contac Toronto are on DSD
--OrderReferenceNumber is used for store order items for processing later.
SET @OrderReferenceNumber = dbo.udf_CTGetOrderReferenceNumber(@StoreHierarchy_UID, GetDate())
/*Reset Client_Uid,
This has done by Chandan Sharma on 21/09/07 because order was not being
dropped for agents that deals with Blackhawk Canada.*/
SELECT @Client_UID=CLIENT_UID FROM CTHIERARCHYSTORES WHERE STOREHIERARCHY_UID=@StoreHierarchy_UID
/*Till Here*/
--Make 2 passes. One to get all products below min and the other to get ones above (if required)
SET @ProcessProductsAboveMinFlag = 'N'
EXEC sp_CTReplenishStoreProducts
@StoreHierarchy_UID,
@ProductType_UID,
@OrderMaxQty,
@OrderMinQty,
@Location_UID,
@Client_UID,
@OrderReferenceNumber,
@StoreReplenishment_UID,
@ProcessProductsAboveMinFlag,
@StorePartner_UID,
@TotalCardsOnOrderBelowMin output
set @cmd = 'echo sp_CTReplenishStoreProducts --- TOTAL CARDS ON ORDER BELOW MIN = '+ Cast(@TotalCardsOnOrderBelowMin AS VARCHAR) +' >> C:\logfile.txt'
exec master..xp_cmdshell @cmd,no_output
set @cmd =''
set @cmd = 'echo sp_CTReplenishStoreProducts --- TOTAL CARDS ON ORDER ABOVE MIN = '+ Cast(@TotalCardsOnOrderAboveMin AS VARCHAR)+' >> C:\logfile.txt'
exec master..xp_cmdshell @cmd,no_output
set @cmd =''*/
SET @TotalCardsOnOrder = @TotalCardsOnOrderBelowMin-- + @TotalCardsOnOrderAboveMin
set @cmd = 'echo --- TOTAL CARDS ON ORDER = '+ Cast(@TotalCardsOnOrder AS VARCHAR)+' >> C:\logfile.txt'
exec master..xp_cmdshell @cmd,no_output
set @cmd =''
if @TotalCardsOnOrder < @OrderMinQty
BEGIN
--Not enough quantity for a shipment. Do house keeping.
set @cmd = 'echo --- Not enough cards ordered for an order. Cards on order = '+ Cast(@TotalCardsOnOrder as varchar)+ '--Min Order Qty: '+ Cast(@OrderMinQty as varchar)+' >> C:\logfile.txt'
exec master..xp_cmdshell @cmd,no_output
set @cmd =''
SET @ErrorMessage = 'Not enough cards ordered for an order. Cards on order: '
+ convert(varchar(6),@TotalCardsOnOrder)
+ '--Min Order Qty: ' + convert(varchar(6), @OrderMinQty)
EXEC sp_CTStoreReplenishmentErrorLog_Insert
@StoreHierarchy_UID,
@StoreReplenishment_UID,
@ErrorMessage
SET @ErrorMessage = ''
EXEC sp_CTTempOrderItemsDelete
@OrderReferenceNumber,
@ErrorMessage
--Roll back Transfer-if there are any.
SET @CurrentDate = GetDate()
EXEC sp_CTStoreCaseToInnerPackTransferCancel
@OrderReferenceNumber,
@Creator,
@CurrentDate
if @errorcode <> 0
BEGIN
EXEC sp_CTStoreReplenishmentErrorLog_Insert
@StoreHierarchy_UID,
@StoreReplenishment_UID,
@ErrorMessage
END
GOTO ContinueLoop
END
if @Location_UID = dbo.udf_CTGetLocationByName('NDC Kentucky')
BEGIN
--Temporary store this info.
INSERT into CTNonOrderReplenishments
( StoreReplenishment_UID, StoreHierarchy_UID, OrderReferenceNumber, UpdateBy)
Values
( @StoreReplenishment_UID, @StoreHierarchy_UID, @OrderReferenceNumber, @Creator)
END
Else
BEGIN
BEGIN TRANSACTION
set @cmd = 'echo --- BEGIN TRANSACTION >> C:\logfile.txt'
exec master..xp_cmdshell @cmd,no_output
set @cmd =''
--Changed by Chandan Sharma on 05/08/08 for IMS integration.
--EXEC sp_CTUpdateStoreOrderQuantity
--EXEC sp_CTUpdateStoreOrderQtyOnPrimaryUPC
EXEC sp_CTUpdateStoreOrderQtyOnSecondaryUPC
@OrderReferenceNumber,
@StoreHierarchy_UID,
@Creator,
@errorcode output,
@ErrorMessage output
if @errorcode <> 0
BEGIN
set @cmd = 'echo --- CALLING SP_CTUPDATESTOREORDERQUANTITY FINISHED-- ERROR MESSAGE = '+@ErrorMessage+' >> C:\logfile.txt'
exec master..xp_cmdshell @cmd,no_output
set @cmd =''
EXEC sp_CTStoreReplenishmentErrorLog_Insert
@StoreHierarchy_UID,
@StoreReplenishment_UID,
'Error updating on order qty for store.'
Select @@TranCount
if @@TranCount > 0 Rollback Transaction
GOTO ContinueLoop
END
EXEC sp_CTProcessStoreOrder
@StoreHierarchy_UID,
@OrderReferenceNumber,
@Location_UID,
@Client_UID,
@Creator,
@StoreReplenishment_UID,
@errorcode output,
@ErrorMessage output
if @errorcode <> 0
BEGIN
set @cmd = 'echo --- CALLING SP_CTPROCESSSTOREORDER FINISHED. ERROR MESSAGE = '+@ErrorMessage +' >> C:\logfile.txt'
exec master..xp_cmdshell @cmd,no_output
set @cmd =''
EXEC sp_CTStoreReplenishmentErrorLog_Insert
@StoreHierarchy_UID,
@StoreReplenishment_UID,
'Error processing order.'
Select @@TranCount
if @@TranCount > 0 Rollback Transaction
GOTO ContinueLoop
END
COMMIT TRANSACTION
set @cmd = 'echo --- COMMIT TRANSACTION >> C:\logfile.txt'
exec master..xp_cmdshell @cmd,no_output
set @cmd =''
END
ContinueLoop:
FETCH c_StoreHierarchies
INTO
@StoreHierarchy_UID
END
CLOSE c_StoreHierarchies
DEALLOCATE c_StoreHierarchies
EXEC sp_CTUpdateReplenishCompletedFlag
@StoreReplenishment_UID
Select @StoreReplenishment_UID as 'StoreReplenishment_UID'
Print 'Done'
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
pa
October 7, 2008 at 12:52 pm
There are far too many other stored procedures being executed in there to even allow for a reasonable guess at what the performance problems might be with this procedure.
I would highly recommend running as much of this individually, i.e. the other stored procedures called from this one and looking at the IO and execution time for those procedures. then work your way up into the procedure. Use the set statistics IO and set statistics TIME options to get the outputs mentioned.
Additionally, take a look at the execution plans to see what steps appear to be the most costly.
Grant Fitchey from this site wrote a free e-book (have to talk to him about that...) on reading execution plans. That book was focused on SQL Server 2005 but would still be profitable for your situtation. Check out Red-Gate's site for that book. http://www.red-gate.com
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 7, 2008 at 12:59 pm
Just outta curiosity... How long does it take to go 1 iteration through your cursor? It may only take 3 seconds for 1 time, but if you have to iterate through it 100 or 1000 or 10,000 times you can see how the time used grows exponentially.
There's just way too much going on there to look at, but what I'd do is set up a trace running to see exactly what all is happening when. Look at the individual procedures that are being called and the execution plans for them. Are you seeing a lot of table scans on the same table time and time again (because of your cursor)?
You need to break it up into chunks small enough to be dealt with and move from one to the next.
-Luke.
October 7, 2008 at 1:13 pm
I have to agree with the other posters. I would also look at changes to the entire process over time. This procedure is RBAR (a Modenism for Row By Agonizing Row).
A cursory look tells me that it would be possible to rewrite this using set-based processing, but we'd have to know the details of all the stored procedures and the tables involved. We'd also need plenty of time (which I don't think many of us have much of) to work on it. This is a project on its own.
I'd start with what the others have suggested. Just keep in mind, that you may want to take the necessary time to rewrite the entire process.
😎
October 7, 2008 at 2:21 pm
Thanks so much for all the tips and techniques to approach this monster proc. I am going to following the guidelines to break this thing up into chunks and analyze individually. A developer brought to me to tune for him. I have got work to do with this really..
Thanks to David and Luke as well for their suggestions
Great quick respond
You guys rock
pa
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply