Reputation: 228
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
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
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.
Hope this helps.
Upvotes: 1