x

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

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<=@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)>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>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

more ▼

asked Aug 03, 2012 at 11:38 AM in Default

avatar image

sravan.434
20 7 7 10

can Somebody modify the stored procedure for good performance please

Aug 07, 2012 at 12:30 PM sravan.434

In short, No.

This is a forum where we try to help people with problems work out how to resolve those problems. There is no payment for giving answers here, other than getting 'karma' points from people who click the tick or vote up answers are grateful for the assistance they got.

So far we have only seen this one section of code. We have no idea about the rest of the database or the system(s) that works with it. To diagnose and resolve an issue we need more than this 600 lines of code. If the advise you have so far doesnt help guide you to new resources or provide ideas on creating a fix then it may be that you need to wait a while for other answers to come along.

If you want your work done for you then that changes the whole scenario and you need to hire one of us as a contractor to work specifically on your system to resolve the issue you have.

There are people here that might be able to do this and I'll leave it up to them to volunteer their services. Alternately you could look to hire a contractor.

I'm sorry if this sounds blunt or unhelpful bet we are all working our own jobs and visit here to help out in a few spare minutes each day.

Aug 07, 2012 at 12:38 PM Fatherjack ♦♦

In order to completely rewrite this procedure, it would take hours and I would have to have access to your database in order to test the results as I went along. In short, it would have to be a paid consulting gig with a contract. There's just too much here.

Take some of the suggestions and run with them. Or, find a consultant who knows how to do this and hire them.

Aug 07, 2012 at 12:37 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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.

more ▼

answered Aug 06, 2012 at 11:27 AM

avatar image

Usman Butt
13.9k 6 13 21

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 06, 2012 at 02:04 PM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(comments are locked)
10|1200 characters needed characters left
 -- 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
  
 
  
more ▼

answered Aug 11, 2012 at 01:33 AM

avatar image

Scot Hauder
6.4k 13 16 22

  • simply for taking it on.

Aug 11, 2012 at 08:03 AM ThomasRushton ♦♦

You're a crazy person. +1

Aug 11, 2012 at 11:12 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Aug 06, 2012 at 03:19 PM

avatar image

venkata40
0

(comments are locked)
10|1200 characters needed characters left

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)
more ▼

answered Aug 07, 2012 at 06:04 AM

avatar image

sravan.434
20 7 7 10

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, 2012 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, 2012 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, 2012 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, 2012 at 12:32 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x457

asked: Aug 03, 2012 at 11:38 AM

Seen: 2286 times

Last Updated: Aug 11, 2012 at 11:12 AM

Copyright 2016 Redgate Software. Privacy Policy