How to Optimize this SQL Script

Question

I use SQL Server 2008 . I try to Make an report and need to calculate a new Column about each row of my table . I write this function :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[fn_CalcProductStock]
    (
      @ProductID INT = NULL ,
      @ToDate DATETIME = NULL ,
      @FinYear INT = NULL ,
      @InventoryID INT = NULL
    )
RETURNS FLOAT
AS
    BEGIN

        DECLARE @stock FLOAT
        SET @stock = ISNULL(( SELECT    SUM(IDI.InvDocItemNumbers)
                              FROM      InvDocItem IDI
                                        LEFT OUTER         JOIN InvDoc ID ON IDI.CenterID = ID.CenterID
                                                              AND IDI.InvDocID = ID.InvDocID
                              WHERE     IDI.ProductID = @ProductID
                                        AND ID.FinYearID = @FinYear
                                        AND ( ID.InventoryID = @InventoryID
                                              OR @InventoryID IS NULL
                                            )
                                        AND ID.InvDocDate < @ToDate
                                        AND ( ID.InvTransTypeID = 1  
                                              OR ID.InvTransTypeID = 4  
                                              OR ID.InvTransTypeID = 6 
                                              OR ID.InvTransTypeID = 13 
                                              OR ID.InvTransTypeID = 9  
                                            )
                            ), 0)
            - ISNULL(( SELECT   SUM(II.InvoiceItemNumbers)
                       FROM     InvoiceItem II
                                LEFT OUTER    JOIN Invoice I ON ( II.CenterID = I.CenterID
                                                              AND II.InvoiceID = I.InvoiceID
                                                              )
                       WHERE    II.ProductID = @ProductID
                                AND I.FinYearId = @FinYear
                                AND I.InvoiceDate < @ToDate
                                AND ( I.InventoryID = @InventoryID
                                      OR @InventoryID IS NULL
                                    )   --Tehran:1  Tehrantakh:101  Mashhad:21  Tabriz:6
                                AND ( I.InvoiceType = 1      
                                      OR I.InvoiceType = 3   
                                    )
                     ), 0)
            + ISNULL(( SELECT   SUM(II.InvoiceItemNumbers)
                       FROM     InvoiceItem II
                                LEFT OUTER     JOIN Invoice I ON II.CenterID = I.CenterID
                                                              AND II.InvoiceID = I.InvoiceID
                       WHERE    II.ProductID = @ProductID
                                AND I.FinYearId = @FinYear
                                AND I.InvoiceDate < @ToDate
                                AND ( I.InventoryID = @InventoryID
                                      OR @InventoryID IS NULL
                                    )
                                AND ( I.InvoiceType = 2   
                                      OR I.InvoiceType = 4   
                                    )
                     ), 0)
            - ISNULL(( SELECT   SUM(IDI.InvDocItemNumbers)
                       FROM     InvDocItem IDI
                                LEFT OUTER JOIN InvDoc ID ON IDI.CenterID = ID.CenterID
                                                             AND IDI.InvDocID = ID.InvDocID
                       WHERE    IDI.ProductID = @ProductID
                                AND ID.InvTransTypeID = 3 
                                AND ID.FinYearID = @FinYear
                                AND ( ID.InventoryID = @InventoryID
                                      OR @InventoryID IS NULL
                                    )
                                AND ID.InvDocDate < @ToDate
                     ), 0)

    -- Return the result of the function
        RETURN @stock

    END

also I try to use this fonction in this case :

SELECT  Prdct.InventoryID ,
        Prdct.ProductID ,
        ( dbo.fn_CalcProductStock(Prdct.ProductID, '2014-04-29', 92, 101) )
FROM    ProductInv AS Prdct

This have true Calculation Result , but takes long time Like as 40 mins ...!!! for 3000 Products

How i can Make it's Performance better ?

Beyond any performance problems, your function is probably not behaving as you want. The only variable that's safe to leave out is InventoryId. FinYear and ToDate will require a value to return any results. The positioning of the conditions for ProductId means that the joins are actually INNER JOINs - if you don't supply a value, you also get no results (because null does not equal null). Your query seems to be getting a quantity of items, but to me ItemNumber would be essentially a key - is this just an ambiguously named variable, or is it a count? — Clockwork-Muse
– Clockwork-Muse, Commented May 1, 2014 at 10:05

dean · Accepted Answer · 2014-04-30 05:34:27Z

1

Unfortunatelly, scalar UDFs with data access are notorious performance killers. They're executed in a RBAR fashion and not optimized together with the main query.

There is no simple way to fix it. What I usually try to do is to store the result of the main query into a temp table, and then think how to rewrite the query from the function so I can execute it once for all the rows. Or, since you actually have four selects there, run each of them separatelly, storing the results into temp table, and join them all together later.

The point is not to execute it once for each row, this way it has much better chance to be optimized correctly.

answered Apr 30, 2014 at 5:34

dean

10.1k2 gold badges27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aasish Kr. Sharma · Accepted Answer · 2014-05-01 09:24:40Z

I think this will work

CREATE FUNCTION [dbo].[usp_CalcProductStock] (
    -- Add the parameters for the function here
    @ProductID INT = NULL
    ,@ToDate DATETIME = NULL
    ,@FinYear INT = NULL
    ,@InventoryID INT = NULL
    )
RETURNS FLOAT
AS
BEGIN
    DECLARE @stock FLOAT
    DECLARE @DocItems TABLE (
        InvTransTypeID INT
        ,SumOfInvDocItemNumbers BIGINT
        )

    INSERT INTO @DocItems
    SELECT ID.InvTransTypeID
        ,SUM(IDI.InvDocItemNumbers) AS SumOfInvDocItemNumbers
    FROM (
        SELECT InvDocItemNumbers
            ,CenterID
            ,InvDocID
        FROM InvDocItem
        WHERE ProductID = @ProductID
        ) IDI
    LEFT JOIN (
        SELECT CenterID
            ,InvDocID
            ,InvTransTypeID
        FROM InvDoc
        WHERE ID.FinYearID = @FinYear
            AND (
                ID.InventoryID = @InventoryID
                OR @InventoryID IS NULL
                )
            AND ID.InvDocDate < @ToDate
        ) ID ON IDI.CenterID = ID.CenterID
        AND IDI.InvDocID = ID.InvDocID HERE
    GROUP BY ID.InvTransTypeID

    DECLARE @InvoiceItems TABLE (
        InvoiceType NVARCHAR(250)
        ,SumofInvoiceItemNumbers BIGINT
        )

    INSERT INTO @InvoiceItems
    SELECT I.InvoiceType
        ,SUM(II.InvoiceItemNumbers) AS SumofInvoiceItemNumbers
    FROM (
        SELECT CenterID
            ,InvoiceID
            ,InvoiceItemNumbers
        FROM InvoiceItem
        WHERE ProductID = @ProductID
        ) II
    LEFT JOIN (
        SELECT CenterID
            ,InvoiceID
            ,InvoiceType
        FROM Invoice
        WHERE I.FinYearId = @FinYear
            AND I.InvoiceDate < @ToDate
            AND (
                I.InventoryID = @InventoryID
                OR @InventoryID IS NULL
                )
        ) I ON II.CenterID = I.CenterID
        AND II.InvoiceID = I.InvoiceID
    GROUP BY I.InvoiceType

    DECLARE @DocItems_I FLOAT;
    DECLARE @DocItems_II FLOAT;
    DECLARE @InvoiceItems_I FLOAT;
    DECLARE @InvoiceItems_II FLOAT;

    SELECT @DocItems_I = ISNULL(SUM(SumOfInvDocItemNumbers), 0)
    FROM @DocItems
    WHERE InvTransTypeID IN (
            1
            ,4
            ,6
            ,9
            ,13
            )

    SELECT @InvoiceItems_I = ISNULL(SUM(SumofInvoiceItemNumbers), 0)
    FROM @InvoiceItems
    WHERE InvoiceType IN (
            1
            ,3
            )

    SELECT @InvoiceItems_II = ISNULL(SUM(SumofInvoiceItemNumbers), 0)
    FROM @InvoiceItems
    WHERE InvoiceType IN (
            2
            ,4
            )

    SELECT @DocItems_II = ISNULL(SUM(SumOfInvDocItemNumbers), 0)
    FROM @DocItems
    WHERE InvTransTypeID IN (3)

    SET @stock = @DocItems_I - @InvoiceItems_I + @InvoiceItems_II - @DocItems_II

    RETURN @stock
END
GO

Here the difference is only I have used variable table and variable table is similar to temporary table. Though variable table have some limitation over temp table that does not mean we should not use it. It just depends on your requirement.

Clockwork-Muse · Accepted Answer · 2014-05-03 03:37:20Z

You may want to try a table-valued function instead:

CREATE FUNCTION Calculated_Product_Stock (@toDate DATETIME,
                                          @finYear INT,
                                          @inventoryId INT = NULL)

RETURNS table AS
RETURN (SELECT COALESCE(Doc.productId, Inv.productId) AS productId,
               COALESCE(Doc.stock, 0) + COALESCE(Inv.stock, 0) AS stock
        FROM (SELECT Item.productId,
                     SUM(CASE WHEN Doc.invTransTypeId = 3 
                                   THEN 0 - Item.invDocItemNumbers
                                   ELSE Item.invDocItemNumbers END) AS stock
              FROM InvDoc Doc
              JOIN InvDocItem Item
                ON Item.centerId = Doc.centerId
                   AND Item.invDocId = Doc.invDocId
              WHERE (@inventoryId IS NULL OR Doc.inventoryId = @inventoryId)
                    AND Doc.finYearId = @finYear
                    AND Doc.invDocDate < @toDate
                    AND Doc.invTransTypeId IN (1, 3, 4, 6, 9, 13)
              GROUP BY Item.productId) Doc

        FULL JOIN (SELECT Item.productId,
                          SUM(CASE WHEN Inv.invoiceType IN (1, 3) 
                                        THEN 0 - Item.invoiceItemNumbers
                                        ELSE Item.invoiceItemNumbers END) AS stock
                   FROM Invoice Inv
                   JOIN InvoiceItem Item
                     ON Item.invoiceId = Inv.invoiceId
                        AND Item.centerId = Inv.centerId
                   WHERE (@inventoryId IS NULL OR Inv.inventoryId = @inventoryId)
                         AND Inv.finYearId = @finYear
                         AND Inv.invoiceDate < @toDate
                         AND Inv.invoiceType IN (1, 2, 3, 4)
                   GROUP BY Item.productId) Inv
               ON Inv.productId = Doc.productId);

The function can then be included in a query like so:

SELECT Product.inventoryId, Product.productId, COALESCE(Invoice.stock, 0)
FROM ProductInv Product
LEFT JOIN Calculated_Product_Stock('2014-04-29', 92, 101) Invoice
       ON Invoice.productId = Product.productId

(nothing's been tested, as I have nothing to run it against. And I've never used this feature)
As always, test to see if it works for your situation.

Thank you , but when I try to Save the Function , this Error has been showed : Msg 4104, Level 16, State 1, Procedure Calculated_Product_Stock, Line 30 The multi-part identifier "Doc.inventoryId" could not be bound.

Collectives™ on Stack Overflow

How to Optimize this SQL Script

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related