Stored procedure taking more time to execute please suggest best optimization techniques

Question

Hi my sp is taking more time to execute please suggest me the alternative ways to optimize it for performance gain

USE [Psql-1aug-2012]
GO

/****** Object:  StoredProcedure [dbo].[stp_rpt_Reconciliation]    Script Date: 08/03/2012 14:39:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


alter PROCEDURE [dbo].[stp_rpt_Reconciliation]
@dParam1 DATETIME,
@dParam2 DATETIME,
--@vBadSite VARCHAR(3) OUTPUT,
--@iRecStatus INT,
@bUseTC BIT
AS
SET NOCOUNT ON
BEGIN

 DELETE FROM SiteReconciliation --Delete all existing records in table 

 DECLARE @vDatePeriod VARCHAR(100)
 DECLARE @vOrderBy VARCHAR(100)
 -- Declaring temp table variable for SiteTankDelivery
 create table  #tSiteTankDelivery(Sno INT IDENTITY(1,1) primary key,SiteID VARCHAR(3),TankID VARCHAR(2),DeliveryDate DATETIME,
 DeliveryTime DATETIME,Delivered FLOAT,DeliveredNet FLOAT,DeliveredWater FLOAT,
 StartTime DATETIME,ChangedOrRecGauge BIT,BOL FLOAT)
 -- Declaring temp table variable for SiteTankInventory
 create table  #tSiteTankInventory (Sno INT IDENTITY(1,1) primary key,SiteID VARCHAR(3),TankID VARCHAR(2),InventoryDate DATETIME,
 InventoryTime DATETIME,InventoryGross FLOAT,InventoryNet FLOAT,ProductHeight FLOAT,
 Ullage FLOAT,WaterHeight FLOAT,WaterVolume FLOAT,Temperature FLOAT,UseInRec BIT)


 -- Declaring temp table variable for Transactions
 create table #tTransactions (Sno INT IDENTITY(1,1) primary key ,Date DATETIME,Qty VARCHAR(8))

 --open Inventory based on period and putting it into temp inventory table
 INSERT INTO #tSiteTankInventory(SiteID,TankID,InventoryDate,
 InventoryTime,InventoryGross,InventoryNet,ProductHeight,
 Ullage,WaterHeight,WaterVolume,Temperature,UseInRec)
 SELECT SiteID,TankID,InventoryDate,InventoryTime,InventoryGross,InventoryNet,ProductHeight,
 Ullage,WaterHeight,WaterVolume,Temperature,UseInRec 
 FROM SiteTankInventory WHERE UseInRec=1 AND InventoryDate BETWEEN @dParam1 AND @dParam2 
 ORDER BY InventoryDate, SiteID, TankID

 DECLARE @iTotalRecords INT
 DECLARE @iTempCount INT

 --Getting count of temp inventory table
 SELECT @iTotalRecords= COUNT(Sno) FROM #tSiteTankInventory

 DECLARE @fMeteredSales FLOAT
 DECLARE @fBlendedSales FLOAT
 DECLARE @fOpenVal FLOAT
 DECLARE @fCloseVal FLOAT
 DECLARE @fCloseWaterHeight FLOAT
 DECLARE @fCloseWaterVol FLOAT
 DECLARE @fDelivered FLOAT
 DECLARE @fMeteredSalesTemp FLOAT
 DECLARE @fCalcDel FLOAT
 DECLARE @bRecFound BIT
 DECLARE @vComment VARCHAR(1000)

 /*New field added according to discussion made with dan*/
 DECLARE @fBOL FLOAT


 DECLARE @dDateOpenDate DATETIME
 DECLARE @dDateCloseDate DATETIME
 DECLARE @vCurSiteID VARCHAR(3)
 DECLARE @vCurTankID VARCHAR(2)
 DECLARE @vBlendTankID VARCHAR(2)

 DECLARE @rPrimBlendPercentage REAL
 DECLARE @rSglBlendPercent REAL
 DECLARE @fBlendSales FLOAT

 DECLARE @iTankType INT
 DECLARE @iProdId VARCHAR(2)

 DECLARE @iNextRecord INT

 SET @iTempCount=1

 WHILE (@iTempCount&lt;=@iTotalRecords) --Outer Loop
 BEGIN


 SELECT @fMeteredSales=0 --Setting value to initial state
 SELECT @fBlendedSales=0 --Setting value to initial state
 SELECT @fDelivered=0 --Setting value to initial state
 SELECT @fBOL=0 --Setting value to initial state
 SELECT @fCloseVal=0 --Setting value to initial state
 SELECT @fBlendSales=0 --Setting value to initial state
 SELECT @vComment='' --Setting value to initial state

 --Selecting first record from temp inventory table and doing calculation with Net 
 --or gross depends upon the user input and setting open date and open volumes
 SELECT @dDateOpenDate=InventoryDate,@vCurSiteID=SiteID,@vCurTankID=TankID,
 @fOpenVal=CASE @bUseTC
 WHEN 1 THEN InventoryNet
 ELSE InventoryGross
 END 
 FROM #tSiteTankInventory WHERE Sno=@iTempCount

 --Is there another record in table to use as period close date? 
 IF EXISTS(SELECT SiteID FROM #tSiteTankInventory WHERE SiteID=@vCurSiteID AND TankID=@vCurTankID GROUP BY SiteID HAVING COUNT(SiteID)&gt;1)
 BEGIN
 --So, is there a Closing Date of period! and getting tanktype and prodid for the currentsite and tank

 SELECT @iTankType=Ttype,@iProdId=ProdId FROM SiteTanks WHERE SiteID=@vCurSiteID AND TankID=@vCurTankID

 IF(@iTankType=4)
 BEGIN
 --move on, no reconcile on this type...
 print 'type4'
 END

 ELSE
 BEGIN
 --Getting next record sno from temp inventory to get close period date
 SELECT @iNextRecord=Max(Sno) FROM #tSiteTankInventory WHERE Sno IN(SELECT TOP 2 Sno FROM #tSiteTankInventory WHERE SiteID=@vCurSiteID AND TankID=@vCurTankID) 
 --Getting close period date and cloing volume for current site and current tank with next record of the same site
 SELECT @dDateCloseDate=InventoryDate, --Set period closing date
 @fCloseVal=CASE @bUseTC --Set period closing volume
 WHEN 1 THEN InventoryNet
 ELSE InventoryGross
 END,
 @fCloseWaterHeight=WaterHeight, --Set closing water height
 @fCloseWaterVol=WaterVolume --Set closing water volume
 FROM #tSiteTankInventory WHERE Sno=@iNextRecord AND SiteID=@vCurSiteID AND TankID=@vCurTankID

 --Getting Delivery for opendate and close date if it available of current site and tank
 INSERT INTO #tSiteTankDelivery
 (SiteID,TankID,DeliveryDate,DeliveryTime,Delivered,DeliveredNet,DeliveredWater,StartTime,
 ChangedOrRecGauge,BOL)
 SELECT SiteID,TankID,DeliveryDate,DeliveryTime,Delivered,DeliveredNet,DeliveredWater,StartTime,
 ChangedOrRecGauge,BOL
 FROM SiteTankDeliveries 
 WHERE SiteID=@vCurSiteID AND TankID=@vCurTankID 
 AND DeliveryDate BETWEEN @dDateOpenDate AND @dDateCloseDate


 SELECT @fDelivered=0 --Initializing the value
 SELECT @fBOL=0 --Initializing the value

 --Taking sum of all deliveries if more than one delivery within that period from temp delivery table
 SELECT @fDelivered=ISNULL(SUM(CASE @bUseTC WHEN 1 THEN ISNULL(DeliveredNet,0) ELSE ISNULL(Delivered,0) END),0),
 @fBOL=ISNULL(SUM(ISNULL(BOL,0)),0) 
 FROM #tSiteTankDelivery

 --Getting Qty and Date of all transactions for that date period comparing with pumps and hoses
 INSERT INTO #tTransactions
 SELECT Date+[Time],Qty FROM Transactions WHERE SiteID=@vCurSiteID AND DontIncludeInRec = 0 AND Date+[Time] BETWEEN @dDateOpenDate AND @dDateCloseDate
 AND Pump 
 IN(SELECT Pump+'1' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose1=@vCurTankID
 UNION
 SELECT Pump+'2' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose2=@vCurTankID
 UNION
 SELECT Pump+'3' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose3=@vCurTankID
 UNION
 SELECT Pump+'4' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose4=@vCurTankID
 UNION
 SELECT Pump+'5' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose5=@vCurTankID
 UNION
 SELECT Pump+'6' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose6=@vCurTankID
 UNION 
 SELECT Pump+'7' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose7=@vCurTankID
 UNION
 SELECT Pump+'8' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose8=@vCurTankID)

 SELECT @fMeteredSalesTemp=0 --Initializing the value
 --Taking sum of all sales from transactions 
 SELECT @fMeteredSalesTemp=ISNULL(SUM(CONVERT(FLOAT,ISNULL(Qty,0))),0) FROM #tTransactions

 SELECT @fCalcDel=0 --Initializing the value

 --select * from @tTransactions
 IF EXISTS(SELECT * FROM #tSiteTankDelivery)
 BEGIN


 --Using Delivery table add in Calculated deliveries as needed.
 SELECT @fCalcDel=ISNULL(SUM(CONVERT(FLOAT,ISNULL(T.Qty,0))),0) FROM #tTransactions T JOIN #tSiteTankDelivery STD
 ON T.Date BETWEEN STD.StartTime AND STD.DeliveryDate
 WHERE STD.ChangedOrRecGauge=0

 IF(@fCalcDel&gt;0)
 BEGIN

 SET @bRecFound=1

 END
 END

 IF(@bRecFound=1)
 BEGIN

 SET @vComment='Delivery Calculated'--Need to generate loadstring
 -- Couldnt generate load string so hard coded
 END
 SELECT @bRecFound=0 --Initializing the value

 SET @fDelivered=@fDelivered+@fCalcDel --Adding calculated delivery with delivery if it is available

 SET @fMeteredSales=@fMeteredSalesTemp --Move temp metered sales value to final

 --At this point we have reconciled a Single tank. Now we must consider those tanks that are
 --configured as Manifolded or those that contribute to a blended product.
 IF(@iTankType IN(3,5,6))
 BEGIN

 IF EXISTS(SELECT * FROM SiteTanks WHERE SiteID=@vCurSiteID AND Ttype=4 AND PrimaryTankId =@vCurTankID)
 BEGIN
 --AddManifoldChildInventory Calculation for that date period
 SELECT @fOpenVal=@fOpenVal+ISNULL(SUM(CASE @bUseTC WHEN 1 THEN ISNULL(InventoryNet,0) ELSE ISNULL(InventoryGross,0) END) ,0)
 FROM #tSiteTankInventory
 WHERE SiteID=@vCurSiteID 
 AND TankID IN(SELECT TankID FROM SiteTanks WHERE SiteID=@vCurSiteID AND Ttype=4 AND PrimaryTankId =@vCurTankID)
 AND InventoryDate = @dDateOpenDate

 SELECT @fCloseVal=@fCloseVal+ISNULL(SUM(CASE @bUseTC WHEN 1 THEN ISNULL(InventoryNet,0) ELSE ISNULL(InventoryGross,0) END),0) 
 FROM #tSiteTankInventory
 WHERE SiteID=@vCurSiteID 
 AND TankID IN(SELECT TankID FROM SiteTanks WHERE SiteID=@vCurSiteID AND Ttype=4 AND PrimaryTankId =@vCurTankID)
 AND InventoryDate = @dDateCloseDate 

 END
 ELSE
 BEGIN

 Print 'Record Count 0'

 END 
 END

 IF(@iTankType IN(1,2,5))
 BEGIN

 --Doing calculation for blended tanks
 SELECT @vComment='' --Initializing the comment
 IF(@iTankType IN(1,5))
 BEGIN
 --Primary tank configuration
 SELECT @vBlendTankID=TankID,@rPrimBlendPercentage=PrimBlendPercentage FROM SiteTanks WHERE SiteID=@vCurSiteID AND Ttype=7 AND PrimaryTankId =@vCurTankID 
 SET @vComment='Primary Blend: '
 END
 ELSE
 BEGIN
 --Secondray tank configuration
 SELECT @vBlendTankID=TankID,@rPrimBlendPercentage=PrimBlendPercentage  FROM SiteTanks WHERE SiteID=@vCurSiteID AND Ttype=7 AND SecondaryTankId =@vCurTankID
 SET @vComment='Secondary Blend: '
 END

 DELETE FROM #tTransactions
 --Getting transactions of blended tanks
 INSERT INTO #tTransactions
 SELECT Date+[Time],Qty FROM Transactions WHERE SiteID=@vCurSiteID AND DontIncludeInRec = 0 AND Date+[Time] BETWEEN @dDateOpenDate AND @dDateCloseDate
 AND Pump 
 IN(SELECT Pump+'1' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose1=@vBlendTankID
 UNION
 SELECT Pump+'2' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose2=@vBlendTankID
 UNION
 SELECT Pump+'3' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose3=@vBlendTankID
 UNION
 SELECT Pump+'4' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose4=@vBlendTankID
 UNION
 SELECT Pump+'5' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose5=@vBlendTankID
 UNION
 SELECT Pump+'6' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose6=@vBlendTankID
 UNION 
 SELECT Pump+'7' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose7=@vBlendTankID
 UNION
 SELECT Pump+'8' 'Pump' FROM SitePumps WHERE SiteID=@vCurSiteID AND Hose8=@vBlendTankID)

 SELECT @fMeteredSalesTemp=0
 --Tanking sum of bleneded deliveries
 SELECT @fMeteredSalesTemp=ISNULL(SUM(CONVERT(FLOAT,ISNULL(Qty,0))),0) FROM #tTransactions

 --Setting blend percentage that depends upon tank type
 SET @rSglBlendPercent=CASE @iTankType WHEN 1 THEN @rPrimBlendPercentage ELSE 100-@rPrimBlendPercentage END

 --setting blended sales
 SET @fBlendSales=@fMeteredSalesTemp*(@rSglBlendPercent/100)
 --setting comment how much percentage of blending
 SET @vComment=@vComment+  CONVERT(VARCHAR(5),(@rSglBlendPercent)) +'%'


 END

 SET @fMeteredSales=ISNULL(@fMeteredSales,0) --setting metered sales if it is null
 SET @fDelivered=ISNULL(@fDelivered,0) --setting delived if it it is null
 SET @fBOL=ISNULL(@fBOL,0) --setting BOL if it it is null
 --SET @vComment=ISNULL(@vComment,'-') --setting Comment if it it is null
 --SET @fBlendSales=ISNULL(@fBlendSales,0) --setting BlendSales if it it is null

 --Saving reconciliation values into reconcilitaion table
 INSERT INTO SiteReconciliation(
 SiteID,TankID,OpenDate,ProductID,OpenVolume,CalcDelivery,MeteredSales,
 PolledInventory,WaterHeight,WaterVol,BlendedSales,CloseDate,Comment,TotalBOL)
 VALUES (@vCurSiteID,@vCurTankID,@dDateOpenDate,@iProdId,@fOpenVal,@fDelivered,@fMeteredSales,
 @fCloseVal,@fCloseWaterHeight,@fCloseWaterVol,@fBlendSales,@dDateCloseDate,@vComment,@fBOL)

 END 
 END 
 DELETE FROM #tTransactions --Clearing existing records for new calculation
 DELETE FROM #tSiteTankDelivery --Clearing existing records for new calculation
 DELETE FROM #tSiteTankInventory WHERE Sno=@iTempCount --Removing current record from Temp inventory
 SET @iTempCount=@iTempCount+1 --Incresing count to perform reconciliation for next record.
 END
 SET NOCOUNT OFF

END






GO

Answer 1

0

The best option is to re-write it with set based approach. This Row by Row (modernly RBAR) approach is bound to make your code slow. I can see that this is a reconciliation process and at the end each row is inserted into a base table. Hence, I am pretty certain that you can turn it into a set based solution with options like use of CASE instead of IF etc.

Sorry, this may not be the precise answer to your question, but with such lengthy code I see the chances are slim that someone would have the time to turn it into a set based solution.

answered Aug 06 '12 at 11:27 AM

Usman Butt
13.8k ● 6 ● 8 ● 14

(comments are locked)

Answer 2

First, I agree with @Usman, you need to completely rearchitect what you've got here.

But, assuming you can't (even though you absolutely should), then you need to identify how each statement is behaving. Which ones are getting index scans? which ones are not using existing indexes? Can you restructure to use a JOIN instead of the IN statements? Can you restructure to use EXISTS instead of the IN statements? Can you use UNION ALL instead of the aggregate function UNION in all those statements?

In short, identify the longest running statements, look at their execution plans and determine what you can do to modify the query or the indexing structure to make it faster.

Answer 3

-- first you need to add a column to SiteReconciliation to facilitate set-based updates
ALTER TABLE dbo.SiteReconciliation ADD TType int NULL

-- add these indexes
CREATE CLUSTERED INDEX [CL_sr] ON [dbo].[SiteReconciliation] 
(
 [SiteID] ASC,
 [TankID] ASC,
 [OpenDate] ASC,
 [CloseDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [CL_std] ON [dbo].[SiteTankDeliveries] 
(
 [SiteID] ASC,
 [TankID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [CL_sti] ON [dbo].[SiteTankInventory] 
(
 [SiteID] ASC,
 [TankID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_sti] ON [dbo].[SiteTankInventory] 
(
 [SiteID] ASC,
 [TankID] ASC,
 [InventoryDate] ASC
)
INCLUDE ( [InventoryGross],
[InventoryNet],
[WaterHeight],
[WaterVolume]) 
WHERE ([UseInRec]=(1))
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [CL_st] ON [dbo].[SiteTanks] 
(
 [SiteID] ASC,
 [TankID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_t] ON [dbo].[Transactions] 
(
 [SiteID] ASC
)
INCLUDE ( [Date],
[Time],
[Qty],
[Pump]) 
WHERE ([DontIncludeInRec]=(0))
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


-- here is another way to solve your problem with a set-based approach. 
-- i don't know what your data looks like so you may have to make some modifications

-- * note i had to add a space when adding t.[Date]+ ' ' +t.[Time] below, 
--   you might have to remove that as it is not in your original query

CREATE PROC [dbo].[stp_rpt_Reconciliation2]
 @dParam1 datetime,
 @dParam2 datetime,
 @bUseTC bit

AS

SET NOCOUNT ON

BEGIN
 TRUNCATE TABLE SiteReconciliation

 INSERT SiteReconciliation(SiteID, TankID, OpenDate, CloseDate, OpenVolume, PolledInventory, WaterVol, WaterHeight, ProductID, TType, CalcDelivery, TotalBOL, MeteredSales, BlendedSales, Comment)
 SELECT od.SiteID, od.TankID, od.InventoryDate, cd.InventoryDate, od.OpenVolume, cd.CloseVolume, cd.WaterVolume,  cd.WaterHeight, st.ProdID, st.TType ,0 ,0 ,0 ,0, ''  
 FROM(
 SELECT SiteID, TankID, InventoryDate, CASE @bUseTC
       WHEN 1 THEN InventoryNet
   ELSE InventoryGross
   END [OpenVolume], 
    rn = ROW_NUMBER() OVER (PARTITION BY SiteID, TankID ORDER BY InventoryDate)
 FROM SiteTankInventory
 WHERE UseInRec=1 
 AND InventoryDate BETWEEN @dParam1 AND @dParam2 
 ) od -- open date
 JOIN
 (
 SELECT SiteID, TankID, InventoryDate, WaterHeight, WaterVolume, CASE @bUseTC
                 WHEN 1 THEN InventoryNet
                 ELSE InventoryGross
                 END [CloseVolume],
    rn = ROW_NUMBER() OVER (PARTITION BY SiteID, TankID ORDER BY InventoryDate)
 FROM SiteTankInventory
 WHERE UseInRec=1 
 AND InventoryDate BETWEEN @dParam1 AND @dParam2 
 ) cd -- close date
 ON (od.SiteID = cd.SiteID AND od.TankID = cd.TankID AND cd.rn = od.rn + 1)
 JOIN SiteTanks st ON (st.SiteID = od.SiteID AND st.TankID = od.TankID)
 WHERE st.TType <> 4

 UPDATE sr2
 SET CalcDelivery = cd,
     TotalBOL = tbol
 FROM (
 SELECT sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate,
    ISNULL(SUM(CASE @bUseTC WHEN 1 THEN ISNULL(std.DeliveredNet,0) 
                            ELSE ISNULL(std.Delivered,0) 
                            END) ,0)[cd],
    ISNULL(SUM(ISNULL(std.BOL,0)),0)[tbol]
 FROM SiteReconciliation sr
 JOIN SiteTankDeliveries std ON (std.SiteID = sr.SiteID AND std.TankID = sr.TankID 
                       AND std.DeliveryDate BETWEEN sr.OpenDate AND sr.CloseDate)
 GROUP BY sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate
 )d
 JOIN SiteReconciliation sr2 ON (sr2.SiteID = d.SiteID AND sr2.TankID = d.TankID
                             AND sr2.OpenDate = d.OpenDate AND sr2.CloseDate = d.CloseDate)


 UPDATE sr2
 SET MeteredSales = ms   
 FROM (
 SELECT sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate, ISNULL(SUM(CONVERT(FLOAT,ISNULL(Qty,0))),0)[ms]
 FROM Transactions t

 JOIN (
 SELECT SiteID, TankID, Pump + RIGHT(Hose,1) [Pump]
 FROM (SELECT SiteID, Pump, Hose1, Hose2, Hose3, Hose4, Hose5, Hose6, Hose7, Hose8 FROM SitePumps)p
 UNPIVOT (TankID FOR Hose IN (Hose1, Hose2, Hose3, Hose4, Hose5, Hose6, Hose7, Hose8))unpvt
 )sp
 ON (sp.SiteID = t.SiteID AND sp.Pump = t.Pump)
 JOIN SiteReconciliation sr ON (sp.SiteID = sr.SiteID AND sp.TankID = sr.TankID 
 AND CAST(t.[Date]+ ' ' +t.[Time] AS datetime) BETWEEN sr.OpenDate AND sr.CloseDate)
 WHERE t.DontIncludeInRec = 0 
 GROUP BY sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate
 )d
 JOIN SiteReconciliation sr2 ON (sr2.SiteID = d.SiteID AND sr2.TankID = d.TankID
                            AND sr2.OpenDate = d.OpenDate AND sr2.CloseDate = d.CloseDate)


 UPDATE sr2
 SET CalcDelivery = CalcDelivery + cd,
 Comment = CASE WHEN SR2.TType IN (3,5,6) 
                THEN 'Delivery Calculated' 
                ELSE '' END  
 FROM (
 SELECT sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate, ISNULL(SUM(CONVERT(FLOAT,ISNULL(Qty,0))),0)[cd]
 FROM Transactions t

 JOIN (
 SELECT SiteID, TankID, Pump + RIGHT(Hose,1) [Pump]
 FROM (SELECT SiteID, Pump, Hose1, Hose2, Hose3, Hose4, Hose5, Hose6, Hose7, Hose8 FROM SitePumps)p
 UNPIVOT (TankID FOR Hose IN (Hose1, Hose2, Hose3, Hose4, Hose5, Hose6, Hose7, Hose8))unpvt
 )sp
 ON (sp.SiteID = t.SiteID AND sp.Pump = t.Pump)
 JOIN SiteReconciliation sr ON (sp.SiteID = sr.SiteID AND sp.TankID = sr.TankID 
 AND CAST(t.[Date]+ ' ' +t.[Time] AS datetime) BETWEEN sr.OpenDate AND sr.CloseDate)
 JOIN SiteTankDeliveries std ON (std.SiteID = sr.SiteID AND std.TankID = sr.TankID 
                       AND std.DeliveryDate BETWEEN sr.OpenDate AND sr.CloseDate)
 WHERE t.DontIncludeInRec = 0 
 AND std.ChangedOrRecGauge = 0
 AND CAST(t.[Date]+ ' ' +t.[Time] AS datetime) BETWEEN std.StartTime AND std.DeliveryDate
 GROUP BY sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate
 )d
 JOIN SiteReconciliation sr2 ON (sr2.SiteID = d.SiteID AND sr2.TankID = d.TankID
                            AND sr2.OpenDate = d.OpenDate AND sr2.CloseDate = d.CloseDate)

 UPDATE sr2
 SET OpenVolume = OpenVolume + ov  
 FROM (
 SELECT sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate,
    ISNULL(SUM(CASE @bUseTC WHEN 1 THEN ISNULL(InventoryNet,0) 
                            ELSE ISNULL(InventoryGross,0) 
                            END) ,0)[ov]
 FROM SiteTankInventory sti
 JOIN SiteReconciliation sr ON (sr.SiteID = sti.SiteID AND sr.OpenDate = sti.InventoryDate)
 WHERE sti.TankID IN(SELECT TankID 
     FROM SiteTanks 
     WHERE SiteID = sr.SiteID 
     AND Ttype = 4 
     AND PrimaryTankId = sr.TankID)
 AND sti.UseInRec = 1
 AND sr.TType IN (3,5,6)
 GROUP BY sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate
 )d
 JOIN SiteReconciliation sr2 ON (sr2.SiteID = d.SiteID AND sr2.TankID = d.TankID
                            AND sr2.OpenDate = d.OpenDate AND sr2.CloseDate = d.CloseDate)

 UPDATE sr2
 SET PolledInventory = PolledInventory + cv  
 FROM (
 SELECT sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate,
    ISNULL(SUM(CASE @bUseTC WHEN 1 THEN ISNULL(InventoryNet,0) 
                            ELSE ISNULL(InventoryGross,0) 
                            END) ,0)[cv]
 FROM SiteTankInventory sti
 JOIN SiteReconciliation sr ON (sr.SiteID = sti.SiteID AND sr.CloseDate = sti.InventoryDate)
 WHERE sti.TankID IN(SELECT TankID 
     FROM SiteTanks 
     WHERE SiteID = sr.SiteID 
     AND Ttype = 4 
     AND PrimaryTankId = sr.TankID)
 AND sti.UseInRec = 1
 AND sr.TType IN (3,5,6)
 GROUP BY sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate
 )d
 JOIN SiteReconciliation sr2 ON (sr2.SiteID = d.SiteID AND sr2.TankID = d.TankID
                            AND sr2.OpenDate = d.OpenDate AND sr2.CloseDate = d.CloseDate)

 UPDATE sr2
 SET BlendedSales = bs,
 Comment = 'Primary Blend: ' + CONVERT(varchar(5),pbp) + '%'
 FROM (
 SELECT sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate, 
       (ISNULL(SUM(CONVERT(FLOAT,ISNULL(Qty,0))),0) * CASE WHEN sr.TType = 1 
                                                           THEN st.PrimBlendPercentage 
                                                           ELSE (100 - st.PrimBlendPercentage) END)/100 [bs],
       CASE WHEN sr.TType = 1 
            THEN st.PrimBlendPercentage 
            ELSE (100 - st.PrimBlendPercentage) END [pbp]
 FROM Transactions t

 JOIN (
 SELECT SiteID, TankID, Pump + RIGHT(Hose,1) [Pump]
 FROM (SELECT SiteID, Pump, Hose1, Hose2, Hose3, Hose4, Hose5, Hose6, Hose7, Hose8 FROM SitePumps)p
 UNPIVOT (TankID FOR Hose IN (Hose1, Hose2, Hose3, Hose4, Hose5, Hose6, Hose7, Hose8))unpvt
 )sp
 ON (sp.SiteID = t.SiteID AND sp.Pump = t.Pump)
 JOIN SiteTanks st ON (st.SiteID = sp.SiteID AND st.TankID = sp.TankID)
 JOIN SiteReconciliation sr ON (st.SiteID = sr.SiteID AND st.PrimaryTankID = sr.TankID 
 AND CAST(t.[Date]+ ' ' +t.[Time] AS datetime) BETWEEN sr.OpenDate AND sr.CloseDate)
 WHERE t.DontIncludeInRec = 0 
 AND sr.TType IN (1,5)
 AND st.TType = 7
 GROUP BY sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate, sr.TType, st.PrimBlendPercentage
 )d
 JOIN SiteReconciliation sr2 ON (sr2.SiteID = d.SiteID AND sr2.TankID = d.TankID
                            AND sr2.OpenDate = d.OpenDate AND sr2.CloseDate = d.CloseDate)                             

 UPDATE sr2
 SET BlendedSales = bs,
 Comment = 'Secondary Blend: ' + CONVERT(varchar(5),pbp) + '%'
 FROM (
 SELECT sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate, 
        ISNULL(SUM(CONVERT(FLOAT,ISNULL(Qty,0))),0) * (100 - st.PrimBlendPercentage)/100 [bs],
        (100 - st.PrimBlendPercentage) [pbp]
 FROM Transactions t

 JOIN (
 SELECT SiteID, TankID, Pump + RIGHT(Hose,1) [Pump]
 FROM (SELECT SiteID, Pump, Hose1, Hose2, Hose3, Hose4, Hose5, Hose6, Hose7, Hose8 FROM SitePumps)p
 UNPIVOT (TankID FOR Hose IN (Hose1, Hose2, Hose3, Hose4, Hose5, Hose6, Hose7, Hose8))unpvt
 )sp
 ON (sp.SiteID = t.SiteID AND sp.Pump = t.Pump)
 JOIN SiteTanks st ON (st.SiteID = sp.SiteID AND st.TankID = sp.TankID)
 JOIN SiteReconciliation sr ON (st.SiteID = sr.SiteID AND st.SecondaryTankID = sr.TankID 
 AND CAST(t.[Date]+ ' ' +t.[Time] AS datetime) BETWEEN sr.OpenDate AND sr.CloseDate)
 WHERE t.DontIncludeInRec = 0 
 AND sr.TType = 2
 AND st.TType = 7
 GROUP BY sr.SiteID, sr.TankID, sr.OpenDate, sr.CloseDate, sr.TType, st.PrimBlendPercentage
 )d
 JOIN SiteReconciliation sr2 ON (sr2.SiteID = d.SiteID AND sr2.TankID = d.TankID
                            AND sr2.OpenDate = d.OpenDate AND sr2.CloseDate = d.CloseDate)                             


END

Answer 4

0

The best option is to rewrite using CTE than creating temp tables and doing inserts.

answered Aug 06 '12 at 03:19 PM

venkata40
0

(comments are locked)

Answer 5

0

Thanks Venkat for your suggestion but when i see the execution plan inserting to Temporary table is more costlier can anyone suggest for improving the performance attached the execution plan

alt text

execplan.jpg (109.3 kB)

answered Aug 07 '12 at 06:04 AM

sravan.434
20 ● 4 ● 7 ● 9

sravan how many rows are you typically inserting into the temp inventory table and temp delivery table? The problem isn't inserting into the temp tables but rather the method for inserting into SiteReconciliation--one row at a time. I can help you out but need to know what you are trying to do with this:

SELECT @iNextRecord=Max(Sno) FROM #tSiteTankInventory WHERE Sno IN(SELECT TOP 2 Sno FROM #tSiteTankInventory WHERE SiteID=@vCurSiteID AND TankID=@vCurTankID)

Since there is no ORDER BY in the subquery you are not guaranteed to get the rows you want. Are you trying to get the very next inventory record for that particular site/tank in order of InventoryDate?

Aug 07 '12 at 07:14 AM Scot Hauder

The only way to make the insert into the temp table less costly is to insert less data (fewer rows or fewer columns) or to ensure that the table has no indexes until after the insert is complete.

Aug 07 '12 at 07:34 AM Fatherjack ♦♦

Yes Scot the same i want to achieve can u suggest alternative for doing calculations row by row

Aug 07 '12 at 10:46 AM sravan.434

@fatherjack has it. Moving data is expensive. That's one reason why this approach, loading temp tables and then trying to manipulate them as filtered sets of data, is so expensive. Generally, the best approach is to look for set-based solutions first.

Aug 07 '12 at 12:32 PM Grant Fritchey ♦♦

(comments are locked)

Stored procedure taking more time to execute please suggest best optimization techniques

Follow this question

Related Questions