-4

How do i optimize following sql query, it has 4 times same 'select' statement, and then perform 4 different where function. can i just have one 'select statement'. this runs slow.

SELECT I.ItemID,
       I.ItemCode,
       I.ItemName,
       B.Brandname                                                                                                                                                                                                                                                                                                                                                                                                           AS 'Brand',
       PM.Productname                                                                                                                                                                                                                                                                                                                                                                                                        AS 'Product',
       CASE
         WHEN I.ItemName LIKE '%LED%' THEN 'LED'
         ELSE TM.TypeName
       END                                                                                                                                                                                                                                                                                                                                                                                                                   AS 'Type',
       ( V.OpeningStock + PurchaseVirtual + SalesReturnVirtual + VirChangeReplaceIn + VirBranchTransIn ) - ( SalesVirtual + PurchaseReturnVirtual + VirZeroReplaceOut + VirChangeReplaceOut + VirCNOut + PVCNOut + VirBranchTransOut )                                                                                                                                                                                       AS 'VirtualStock',
       ( V.OpeningStock + PurchasePhysical + TransferIn + TransferOutReturn + SalesReturnPhysical + PhyReplaceCompanyIn + FromSSIRepairIn + PhyChangeReplaceIn + PhyReapirCompanyInAPhyBranchTransIn ) - ( SalesPhysical + TransferOut + PurchaseReturnPhysical + ToSSIRepairOut + PhyZeroReplaceOut + PhyChangeReplaceOut + PhyReplaceCompanyOut + PhyCNOut + PhyRepairCompanyOut + PhyReplaceCustOut + PhyBranchTransOut ) AS 'PhysicalStock',
       ( ( TransferOut + ToSSIRepairOut + PhyZeroReplaceOut + PhyChangeReplaceOut + PhyReplaceCompanyOut + PhyCNOut + PhyRepairCompanyOut + PhyReplaceCustOut ) - ( VirChangeReplaceOut + VirZeroReplaceOut + VirCNOut ) - ( TransferOutReturn + PhyReplaceCompanyIn + FromSSIRepairIn + PhyChangeReplaceIn + PhyReapirCompanyIn - VirChangeReplaceIn ) )                                                                    AS 'T.Out/Service',
       ( (SELECT CASE
                   WHEN Cast(Round(SUM(PVID.UnitNLC) / Count(PVID.UnitNLC), 0) AS INTEGER) IS NULL THEN 0
                   ELSE Cast(Round(SUM(PVID.UnitNLC) / Count(PVID.UnitNLC), 0) AS INT)
                 END
          FROM   SanketSales.dbo.pur_vir_inw_det PVID
          WHERE  I.ItemID = PVID.ItemID) * ( ( V.OpeningStock + PurchaseVirtual + SalesReturnVirtual + VirChangeReplaceIn + VirBranchTransIn ) - ( SalesVirtual + PurchaseReturnVirtual + VirZeroReplaceOut + VirChangeReplaceOut + VirCNOut + PVCNOut + VirBranchTransOut ) ) )                                                                                                                                             AS 'Stock Value(On AVG.NLC)',
       (SELECT TOP 1 Cast(Round(PVID.UnitNLC, 0) AS INT)
        FROM   SanketSales.dbo.pur_vir_inw_det PVID
               LEFT JOIN SanketSales.dbo.pur_vir_inw_mst PVIM
                 ON ( PVIM.VPINo = PVID.VPINo )
        WHERE  I.ItemID = PVID.ItemID
        ORDER  BY PVIM.Date DESC)                                                                                                                                                                                                                                                                                                                                                                                            AS 'Last NLC',
       (SELECT TOP 1 Cast(Round(SUM(PVID.UnitNLC) / Count(PVID.UnitNLC), 0) AS INT)
        FROM   SanketSales.dbo.pur_vir_inw_det PVID
        WHERE  I.ItemID = PVID.ItemID)                                                                                                                                                                                                                                                                                                                                                                                       AS 'AVG NLC',
       (SELECT Cast(Round(ID.DP, 0) AS INT)
        FROM   SanketUniversal.dbo.itemprice ID
        WHERE  ID.ItemID = I.ItemID
               AND ID.[Current] = 1)                                                                                                                                                                                                                                                                                                                                                                                         AS 'DP',
       (SELECT TOP 1 Cast(Round(SUM(SVOD.Rate * SVOD.Qty) / SUM(SVOD.Qty), 0) AS INT)
        FROM   SanketSales.dbo.sales_vir_out_det SVOD
        WHERE  SVOD.ItemID = I.ItemID)                                                                                                                                                                                                                                                                                                                                                                                       AS 'Avg. Sales Value',
       (SELECT TOP 1 SUM(SVOD.CurQty)
        FROM   SanketSales.dbo.sales_vir_out_det SVOD
               LEFT JOIN SanketSales.dbo.sales_vir_out_mst SVOM
                 ON ( SVOM.SVONo = SVOD.SVONo )
        WHERE  SVOD.ItemID = I.ItemID
               AND CONVERT(VARCHAR(10), SVOM.Date, 120) BETWEEN CONVERT(VARCHAR(10), DATEADD(d, -15, GETDATE()), 120) AND CONVERT(VARCHAR(10), GETDATE(), 120))                                                                                                                                                                                                                                                              AS '15 Day Qty',
       (SELECT TOP 1 SUM(SVOD.CurQty)
        FROM   SanketSales.dbo.sales_vir_out_det SVOD
               LEFT JOIN SanketSales.dbo.sales_vir_out_mst SVOM
                 ON ( SVOM.SVONo = SVOD.SVONo )
        WHERE  SVOD.ItemID = I.ItemID
               AND CONVERT(VARCHAR(10), SVOM.Date, 120) BETWEEN CONVERT(VARCHAR(10), DATEADD(d, -30, GETDATE()), 120) AND CONVERT(VARCHAR(10), DATEADD(d, -16, GETDATE()), 120))                                                                                                                                                                                                                                             AS '30 Day Qty',
       (SELECT TOP 1 SUM(SVOD.CurQty)
        FROM   SanketSales.dbo.sales_vir_out_det SVOD
               LEFT JOIN SanketSales.dbo.sales_vir_out_mst SVOM
                 ON ( SVOM.SVONo = SVOD.SVONo )
        WHERE  SVOD.ItemID = I.ItemID
               AND CONVERT(VARCHAR(10), SVOM.Date, 120) BETWEEN CONVERT(VARCHAR(10), DATEADD(d, -90, GETDATE()), 120) AND CONVERT(VARCHAR(10), DATEADD(d, -31, GETDATE()), 120))                                                                                                                                                                                                                                             AS '90 Day Qty',
       (SELECT TOP 1 SUM(SVOD.CurQty)
        FROM   SanketSales.dbo.sales_vir_out_det SVOD
               LEFT JOIN SanketSales.dbo.sales_vir_out_mst SVOM
                 ON ( SVOM.SVONo = SVOD.SVONo )
        WHERE  SVOD.ItemID = I.ItemID
               AND CONVERT(VARCHAR(10), SVOM.Date, 120) BETWEEN CONVERT(VARCHAR(10), DATEADD(d, -180, GETDATE()), 120) AND CONVERT(VARCHAR(10), DATEADD(d, -91, GETDATE()), 120))                                                                                                                                                                                                                                            AS '180 Day Qty',
       CASE
         WHEN ( B1.VirtualBlockStockCount = 1
                 OR B1.PhysicalBlockStockCount = 1 ) THEN 'Block'
         ELSE 'UnBlock'
       END                                                                                                                                                                                                                                                                                                                                                                                                                   AS 'BlockStatus'
FROM   SanketUniversal.dbo.ItemMaster I
       LEFT JOIN SanketSales.dbo.VirtualStock V
         ON ( V.ItemID = I.ItemID )
       LEFT OUTER JOIN SanketUniversal.dbo.ProductMaster PM
         ON I.ProductId = PM.ProductId
       LEFT OUTER JOIN SanketUniversal.dbo.TypeMaster TM
         ON I.typeId = TM.TypeId
       LEFT OUTER JOIN SanketUniversal.dbo.BrandMaster B
         ON I.BrandID = B.BrandID
       LEFT JOIN SanketSales.dbo.itemblock B1
         ON ( I.ItemID = B1.ItemID )
WHERE  I.ActiveItem = 1
ORDER  BY I.ItemName
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Teknas
  • 541
  • 5
  • 17
  • sorry, i was trying to format it, but could not find how to...did use {} but in preview it was showing same plain text format. so did not use it. – Teknas Feb 22 '14 at 14:14
  • 2
    `SUM(CASE WHEN SVOM.Date condition 1 ... THEN SVOD.CurQty END),SUM(CASE WHEN SVOM.Date condition 2 ... THEN SVOD.CurQty END) ...` – Martin Smith Feb 22 '14 at 14:26
  • apart from what @Martin suggested.remove CONVERT(VARCHAR(10), SVOM.Date, 120) from any date.simply let it be datecolumn if it is really datetime datatype – KumarHarsh Feb 22 '14 at 14:36
  • why use `CONVERT` in query!? use `SVOM.Date` directly, and indexing `SVOM.Date`,`I.ItemID`,`SVOD.ItemID` – MajidTaheri Feb 22 '14 at 14:37
  • how come this is off topic. everything is there and people have tried to answer. problem is clearly mentioned. what else is missing? – Teknas Feb 23 '14 at 05:46
  • My vote was "too broad" FWIW. Not "lacks sufficient information". The question has sufficient information but IMO it is not reasonable to expect anyone here to rewrite that whole thing. Also it is far too localized and won't help any future readers. You should hire a contractor to sort that mess out if you can't do it yourself. – Martin Smith Feb 23 '14 at 11:41

3 Answers3

3

You want conditional aggregation:

SELECT I.ITEMId,
       SUM(case when SVOM.Date BETWEEN cast(GETDATE() - 15 as date) and cast(GETDATE() as date)
          ) as Qty_15day,
       SUM(case when SVOM.Date BETWEEN cast(GETDATE() - 30 as date) and cast(GETDATE() as date)
          ) as Qty_30day,
       SUM(case when SVOM.Date BETWEEN cast(GETDATE() - 90 as date) and cast(GETDATE() as date)
          ) as Qty_60day,
       SUM(case when SVOM.Date BETWEEN cast(GETDATE() - 180 as date) and cast(GETDATE() as date)
          ) as Qty_180day
FROM Items I LEFT JOIN
     SanketSales.dbo.sales_vir_out_det SVOD
     on SVOD.ItemID = I.ItemID LEFT JOIN
     SanketSales.dbo.sales_vir_out_mst SVOM
     ON SVOM.SVONo = SVOD.SVONo
GROUP BY I.ItemId;

This changes the subqueries to case statements in the sum() function. Note that I am guessing on the existence of an Items table, because your query is syntactically incorrect. Also, the date comparisons have been simplified, to use native date comparisons rather than string comparisons. (And, with apologies to Aaron Bertrand, I'm leaving in the between comparisons for the dates.)

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • i just posted whole query above. Gordon, could you pls see and let me know, coz i tried with your query but not working. – Teknas Feb 22 '14 at 17:13
  • What does "not working" mean? – Gordon Linoff Feb 22 '14 at 18:19
  • i copied your code and inserted into my query. The table, Item, actually it is SanketUniversal.dbo.ItemMaster, so i replaced item with SanketUniversal.dbo.ItemMaster in your code, and tried to execute. but gave error. hence i updated main post with full query. so you can have idea. – Teknas Feb 22 '14 at 18:28
  • @user1041627 . . . Add `group by i.item_id` or something like that to the end of your query. – Gordon Linoff Feb 22 '14 at 18:43
0

I have two observations

TOP is not required is the query as you are just doing a sum

Google the word SARGable, it would help you a lot. You should try avoid functions in the where clause as it will prevent the optimizer from using an available index.

What makes a SQL statement sargable?

Try this query and see if it improves the performance 

WITH CTE as
(Select * From SanketSales.dbo.sales_vir_out_det SVOD 
Left JOIN SanketSales.dbo.sales_vir_out_mst SVOM on (SVOM.SVONo = SVOD.SVONo) 
Where SVOD.ItemID = I.ItemID)


select ( SELECT SUM(SVOD.CurQty) FROM CTE WHERE
SVOM.Date
BETWEEN DATEADD(d,-15,GETDATE()) and GETDATE()) as '15 Day Qty',
( SELECT SUM(SVOD.CurQty) FROM CTE WHERE
SVOM.Date
BETWEEN DATEADD(d,-30,GETDATE()) and DATEADD(DAy,-16,GETDATE())) as'30 Day Qty',

( SELECT SUM(SVOD.CurQty) FROM CTE WHERE
SVOM.Date
BETWEEN DATEADD(d,-90,GETDATE()) and DATEADD(DAy,-31,GETDATE())) as '90 Day Qty',

( SELECT SUM(SVOD.CurQty) FROM CTE WHERE
SVOM.Date
BETWEEN DATEADD(d,-180,GETDATE()) and DATEADD(DAy,-91,GETDATE())) as '180 Day Qty',
Community
  • 1
  • 1
Satheesh Variath
  • 680
  • 4
  • 10
0

Use CASE to sum your column:

DECLARE     @now AS date = GETDATE();                   -- using variables to make the query more readable    
DECLARE     @day15 AS date = DATEADD(d, -15, @now);     -- by using the "date" datatype, the time is truncated
DECLARE     @day30 AS date = DATEADD(d, -30, @now);     -- for me, it's easier than CONVERT(varchar(10), DateField, 120)
DECLARE     @day90 AS date = DATEADD(d, -90, @now);     -- and it doesn't rely on string comparisons
DECLARE     @day180 AS date = DATEADD(d, -180, @now);
DECLARE     @day16 AS date = DATEADD(d, -16, @now);
DECLARE     @day31 AS date = DATEADD(d, -31, @now);
DECLARE     @day91 AS date = DATEADD(d, -91, @now);

SELECT      CASE WHEN CAST(SVOM.Date AS date) BETWEEN @day15 AND @now THEN SUM (SVOD.CurQty) ELSE 0 END AS '15 Day Qty'
            , CASE WHEN CAST(SVOM.Date AS date) BETWEEN @day30 AND @day16 THEN SUM (SVOD.CurQty) ELSE 0 END AS '30 Day Qty'
            , CASE WHEN CAST(SVOM.Date AS date) BETWEEN @day90 AND @day31 THEN SUM (SVOD.CurQty) ELSE 0 END AS '90 Day Qty'
            , CASE WHEN CAST(SVOM.Date AS date) BETWEEN @day120 AND @day91 THEN SUM (SVOD.CurQty) ELSE 0 END AS '180 Day Qty'
FROM        SanketSales.dbo.sales_vir_out_det SVOD
            LEFT JOIN SanketSales.dbo.sales_vir_out_mst SVOM
                ON  SVOM.SVONo = SVOD.SVONo
WHERE       SVOD.ItemID = I.ItemID  -- I have no idea where you are defining "I"
pete
  • 24,141
  • 4
  • 37
  • 51