CODEWITHSUNDEEP
sql-serverfunction
Mehrdad Ghaffari
Mehrdad Ghaffari

Reputation: 228

Function in select statement makes my query run very slowly

I have this query in a stored procedure:

SELECT      
    *,
     ISNULL(dbo.ReturnShortageByItemCodeLinePackage(LineId, TestPackageId, MaterialDescriptionId), 0) AS Shortage 
FROM
    dbo.ViewMTO

I am using a function inside the query to calculate an integer value as you can see here :

ALTER FUNCTION [dbo].[ReturnShortageByItemCodeLinePackage]
     (@lineId int,@testpackId int, @MaterialDescriptionId int)
RETURNS float
AS 
BEGIN
    DECLARE @shortageQuantity float
    DECLARE @MIVQuantity float
    DECLARE @totalQuantity float
    DECLARE @spoolQuantity float
    DECLARE @ExistInSiteQuantity float
    DECLARE @BeforeDoneQuantity float

    SELECT  
        @totalQuantity = Quantity,
        @spoolQuantity = QuantitySpool,
        @ExistInSiteQuantity = QuantityExistInSite,
        @BeforeDoneQuantity = QuantityBeforeDone
    FROM 
        [SPMS2].[dbo].Materials 
    WHERE
        LineId = @lineId 
        AND TestPackageId = @testpackId  
        AND MaterialDescriptionId = @MaterialDescriptionId

    SELECT
        @MIVQuantity = SUM(QuantityDeliver) 
    FROM
        MaterialIssueVoucherDetails miv
    JOIN
        MaterialRequestContractorDetails mrc ON miv.MaterialRequestContractorDetailId = mrc.Id
    WHERE
        TestPackageId = @testpackId 
        AND LineId = @lineId 
        AND miv.MaterialDescriptionId = @MaterialDescriptionId

    IF @MIVQuantity IS NULL
    BEGIN
        SET @MIVQuantity = 0    
    END

    SET @shortageQuantity = @totalQuantity - (@BeforeDoneQuantity + @ExistInSiteQuantity + @spoolQuantity + @MIVQuantity)

    RETURN round(@shortageQuantity, 3)
END

My query is executed in 3 minutes, it is catastrophic for my users! Is there any better solution?

Upvotes: 0

Views: 138

Answers (2)

Ajay Dwivedi
Ajay Dwivedi

Reputation: 328

Convert your Scaler function to Table-Valued function, and then place the function in FROM clause for LEFT JOIN. Do check execution plans to find any warning.

Testing performance of Scalar vs Table-valued functions in sql server

Upvotes: 0

Mike Gledhill
Mike Gledhill

Reputation: 29213

I can recommend three things:

A. The following line..

SELECT @totalQuantity= ... 
FROM [SPMS2].[dbo].Materials

Is this accessing a different database via a Linked Server connection ? How fast is this connection ?

B. Your SP contains two SELECT statements. Which of them is the bottleneck ? You can add some PRINT statements to show when each is started:

PRINT convert(nvarchar, GetDate(), 108) + ' This is the time !'

C. Try running the SQL show on my webpage below, which will highlight missing Indexes.

Find missing indexes

Hope this helps.

Upvotes: 1

Related Questions