locked
My Sql query takes so long time to execute RRS feed

  • Question

  • I have a sql query which is ;

          

    USE [Capretto_V3]
    GO
    /****** Object:  UserDefinedFunction [dbo].[CaprettoGuncelle]    Script Date: 15-Aug-17 13:17:35 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[CaprettoGuncelle] (@LangCode Char5,@BarcodeTypeCode Char20,@PriceGroupCode Char20, @LastNHour int ) 
    RETURNS TABLE 
    AS RETURN
    (

    SELECT   
    Barcode = ISNULL((SELECT TOP 1 Barcode 
    FROM prItemBarcode WITH(NOLOCK)
    WHERE prItemBarcode.ItemTypeCode = Product.ItemTypeCode
    AND prItemBarcode.ItemCode = Product.ItemCode
    AND prItemBarcode.ColorCode = Product.ColorCode
    AND prItemBarcode.ItemDim1Code = Product.ItemDim1Code
    AND prItemBarcode.ItemDim2Code = Product.ItemDim2Code
    AND prItemBarcode.ItemDim3Code = Product.ItemDim3Code
    AND prItemBarcode.BarcodeTypeCode = @BarcodeTypeCode), SPACE(0))

    , Product.ItemCode
    --, ProductDescription
    , ColorCode = Product.ColorCode
    , ItemDim1Code = Product.ItemDim1Code
    , ItemDim2Code = Product.ItemDim2Code
    , ItemDim3Code = Product.ItemDim3Code
    , cdItemDesc.ItemDescription
    , cdItem.ProductHierarchyID
    , Price2 = ISNULL((SELECT TOP 1 Price FROM ProductValidPrices(GETDATE(),GETDATE())
    WHERE ProductValidPrices.ItemTypeCode = 1
    AND ProductValidPrices.ItemCode = prItemVariant.ItemCode
    AND ProductValidPrices.ColorCode IN (prItemVariant.ColorCode, SPACE(0))
    AND ProductValidPrices.ItemDim1Code = prItemVariant.ItemDim1Code
    AND ProductValidPrices.PriceGroupCode = 'B2C'
    AND ProductValidPrices.PaymentPlanCode = SPACE(0)
    ORDER BY ProductValidPrices.ColorCode DESC
    ), SPACE(0))

    , Price1 = ISNULL((SELECT TOP 1 Price FROM ProductValidPrices(GETDATE(),GETDATE())
    WHERE ProductValidPrices.ItemTypeCode = 1
    AND ProductValidPrices.ItemCode = prItemVariant.ItemCode
    AND ProductValidPrices.ColorCode IN (prItemVariant.ColorCode, SPACE(0))
    AND ProductValidPrices.ItemDim1Code = prItemVariant.ItemDim1Code
    AND ProductValidPrices.PaymentPlanCode = 'ON'
    ORDER BY ProductValidPrices.ColorCode DESC
    ), SPACE(0))

    , WarehouseInventoryQty = ISNULL(WarehouseInv.AvailableInventory,0)


    , TransferNotApprovedQty1 = ISNULL((SELECT SUM(TransferNotApprovedQty1) 
                               FROM ItemTransferNotApprovedByDate(getdate())
                               WHERE ItemTransferNotApprovedByDate.ItemTypeCode = Product.ItemTypeCode
    AND ItemTransferNotApprovedByDate.ItemCode = Product.ItemCode
    AND ItemTransferNotApprovedByDate.ColorCode = Product.ColorCode
    AND ItemTransferNotApprovedByDate.ItemDim1Code = Product.ItemDim1Code
    AND ItemTransferNotApprovedByDate.ItemDim2Code = Product.ItemDim2Code
    AND ItemTransferNotApprovedByDate.ItemDim3Code = Product.ItemDim3Code),0)
    ,prItemNotes.Notes



    FROM prItemVariant AS Product  WITH (NOLOCK)
    --INNER JOIN prItemVariant WITH(NOLOCK)
    -- ON prItemVariant.ItemCode = Product.ProductCode 
    -- AND prItemVariant.ItemTypeCode = Product.ProductTypeCode 
    INNER JOIN cdItem WITH(NOLOCK)
    ON cdItem.ItemCode = Product.ItemCode 
    AND cdItem.ItemTypeCode = Product.ItemTypeCode 


    LEFT JOIN (
    SELECT ItemCode,ItemTypeCode,ColorCode,ItemDim1Code,ItemDim2Code,ItemDim3Code,
    SUM(AvailableInventoryQty1) AvailableInventory 
    FROM ItemInventory WITH (NOLOCK) where ItemInventory.WarehouseCode IN('D01','M01','D02','D03','D04')
    GROUP BY ItemCode,ItemTypeCode,ColorCode,ItemDim1Code,ItemDim2Code,ItemDim3Code
    ) AS WarehouseInv ON WarehouseInv.ItemTypeCode = Product.ItemTypeCode 
     AND WarehouseInv.ItemCode = Product.ItemCode
     AND WarehouseInv.ColorCode = Product.ColorCode
     AND WarehouseInv.ItemDim1Code = Product.ItemDim1Code 
     AND WarehouseInv.ItemDim2Code = Product.ItemDim2Code
     AND WarehouseInv.ItemDim3Code = Product.ItemDim3Code   
    LEFT OUTER JOIN prItemNotes WITH (NOLOCK) ON prItemNotes.ItemTypeCode=Product.ItemTypeCode
      AND   prItemNotes.ItemCode=Product.ItemCode
    LEFT OUTER JOIN trStock WITH (NOLOCK) ON trStock.ItemTypeCode=Product.ItemTypeCode
      AND   trStock.ItemCode=Product.ItemCode
      AND trStock.ItemTypeCode = Product.ItemTypeCode
    AND trStock.ItemCode = Product.ItemCode
    AND trStock.ColorCode = Product.ColorCode
    AND trStock.ItemDim1Code = Product.ItemDim1Code
    AND trStock.ItemDim2Code = Product.ItemDim2Code
    AND trStock.ItemDim3Code = product.ItemDim3Code
    INNER JOIN cdItemDesc WITH (NOLOCK) ON cdItemDesc.ItemTypeCode=Product.ItemTypeCode
    AND cdItemDesc.ItemCode=Product.ItemCode
    AND cdItemDesc.LangCode=@LangCode
    LEFT OUTER JOIN trPriceListLine WITH (NOLOCK) ON trPriceListLine.ItemTypeCode=Product.ItemTypeCode
    AND trPriceListLine.ItemCode=Product.ItemCode
    AND trPriceListLine.ColorCode=Product.ColorCode
    INNER JOIN prItemVariant ON prItemvariant.Itemcode=Product.ItemCode 
    AND prItemvariant.ItemTypeCode=Product.ItemTypeCode
    AND prItemVariant.ItemDim1Code=Product.ItemDim1Code
    AND prItemVariant.ItemDim2Code=Product.ItemDim2Code




    WHERE Product.IsBlocked = 0 
    AND cdItem.UseInternet= 1
    AND cdItem.IsBlocked = 0
    AND Product.ItemTypeCode=1

    AND (EXISTS (SELECT * FROM trStock WITH(NOLOCK) 
    WHERE trStock.ItemTypeCode = Product.ItemTypeCode
    AND trStock.ItemCode = Product.ItemCode
    AND trStock.ColorCode = Product.ColorCode
    AND trStock.ItemDim1Code = Product.ItemDim1Code
    AND trStock.ItemDim2Code = Product.ItemDim2Code
    AND trStock.WarehouseCode in ('D01','M01','D02','D03','D04') --BALGAT VE MERKEZ

    AND trStock.OperationDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND  GETDATE()  )

    OR (cdItem.LastUpdatedDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND  GETDATE() ) 
    OR (cdItemDesc.LastUpdatedDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND  GETDATE())  
    OR (Product.LastUpdatedDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND  GETDATE()) 
    OR (prItemNotes.LastUpdatedDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND  GETDATE()) 
    OR (trStock.LastUpdatedDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND  GETDATE())
    OR (trPriceListLine.LastUpdatedDate BETWEEN DATEADD(HOUR , -24 * @LastNHour, GETDATE() ) AND  GETDATE())



    )

    and when i run this query with; SELECT ItemCode,ColorCode,ItemDim1Code,Price1,Price2,WarehouseInventoryQty FROM  CaprettoGuncelle ('TR','','B2C',1) GROUP BY ItemCode,ColorCode,ItemDim1Code,Price1,Price2,WarehouseInventoryQty                        No matter how many rows is coming it take 27 seconds.Can you help me?

    Tuesday, August 15, 2017 10:29 AM

All replies

  • What does the execution plan look like? You would see that by clicking Query > Include Actual Execution Plan.

    What you want to look for, first off, would be full table scans. Index seeks or even scans are good. Look for what portion is costing you the most. This is to see if you might, first of all, have an index missing.


    Randy Sims

    Tuesday, August 15, 2017 1:11 PM
  • check missing index on this and index fragmentation on this tables.

    There should be index scan so data retrieval will faster so check execution plan will help you.Statics must be updated.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx https://social.technet.microsoft.com/wiki/contents/articles/37856.sql-server-on-linux-how-to-change-sa-password.aspx

    Tuesday, August 15, 2017 1:59 PM
  • Can you please post the execution plan?

    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Tuesday, August 15, 2017 4:10 PM