CODEWITHSUNDEEP
sqlsql-servert-sql
nfplee
nfplee

Reputation: 7977

T-SQL Calling Scalar Function in Where Speed

I am using SQL Server 2008 and I have the following query:

SELECT [Id] FROM [dbo].[Products] WHERE [dbo].GetNumOnOrder([Id]) = 0

With the following "GetNumOnOrder" Scalar-valued function:

CREATE FUNCTION [dbo].[GetNumOnOrder]
(
    @ProductId INT
)
RETURNS INT
AS
BEGIN
    DECLARE @NumOnOrder INT

    SELECT @NumOnOrder = SUM([NumOrdered] - [NumReceived])
    FROM [dbo].[PurchaseOrderDetails]
    INNER JOIN [dbo].[PurchaseOrders]
        ON [PurchaseOrderDetails].[PurchaseOrderId] = [PurchaseOrders].[Id]
    WHERE [PurchaseOrders].[StatusId] <> 5
        AND [PurchaseOrderDetails].[ProductId] = @ProductId

    RETURN CASE WHEN @NumOnOrder IS NOT NULL THEN @NumOnOrder ELSE 0 END
END

However it takes around 6 seconds to execute. Unfortunately I have no control over the initial SQL generated but I can change the function. Is there any way the function can be modified to speed this up? I'd appreciate the help. Thanks

Upvotes: 0

Views: 735

Answers (3)

db9dreamer
db9dreamer

Reputation: 1715

If you have the rights to add indexes to the tables (and dependant on the version of SQL Server you are using), I would investigate what performance gain adding the following would have:-

create index newindex1 on PurchaseOrders (id) 
    include (StatusId);
create index newindex2 on PurchaseOrderDetails (PurchaseOrderId) 
    include (ProductId,NumOrdered,NumReceived);

You probably already have indexes on these columns - but the indexes above will support just the query in your function in the most efficient way possible (reducing the number of page reads to a minimum). If the performance of this function is important enough, you could also consider adding a calculated column into your table - for NumOrdered-NumReceived (and then only include the result column in the index above - and your query). You could also consider doing this in an indexed view rather than the table - but schema binding a view can by tiresome and inconvenient. Obviously, the wider the tables in question are - the greater the improvement in performance will be.

Upvotes: 1

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

If you still want to use a function and can not live without it, use a in-line table value version. It is a-lot faster. Check out these articles from some experts.

http://aboutsqlserver.com/2011/10/23/sunday-t-sql-tip-inline-vs-multi-statement-table-valued-functions/

http://dataeducation.com/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post/

I have had a couple MVP friends say that this the only function they ever write since scalar functions are treated as a bunch of Stored Procedure calls.

Re-write using in-line table value function. Check the syntax since I did not. Use the Coalesce function to convert NULL to Zero.

--
-- Table value function
--

CREATE FUNCTION [dbo].[GetNumOnOrder] ( @ProductId INT )
RETURNS TABLE
AS
RETURN
(
    SELECT 
        COALESCE(SUM([NumOrdered] - [NumReceived]), 0) AS Num 
    FROM 
        [dbo].[PurchaseOrderDetails]
    INNER JOIN [dbo].[PurchaseOrders]
        ON [PurchaseOrderDetails].[PurchaseOrderId] = [PurchaseOrders].[Id]
    WHERE [PurchaseOrders].[StatusId] <> 5
        AND [PurchaseOrderDetails].[ProductId] = @ProductId
);

--
-- Sample call with cross apply
--

SELECT [Id] 
FROM [dbo].[Products] P
CROSS APPLY [dbo].[GetNumOnOrder] (C.Id) AS CI
WHERE CI.Num = 0;

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10264

If the data is unevenly distributed data in table PurchaseOrderDetails then cached query plans might impact your query performance. This is a case where "Parameter Sniffing" might create bad query plans. Actually SQL Server supports an optimization called "parameter sniffing", where it will choose different plan based on the particular values in @ProductId variable. So to improve performance of your query you can re-write your function as:

CREATE FUNCTION [dbo].[GetNumOnOrder]
(
    @ProductId INT
)
RETURNS INT
AS
BEGIN
    DECLARE @NumOnOrder INT,@v_ProductId INT
    SET @v_ProductId = @ProductId;

    SELECT @NumOnOrder = SUM([NumOrdered] - [NumReceived])
    FROM [dbo].[PurchaseOrderDetails]
    INNER JOIN [dbo].[PurchaseOrders]
        ON [PurchaseOrderDetails].[PurchaseOrderId] = [PurchaseOrders].[Id]
    WHERE [PurchaseOrders].[StatusId] <> 5
        AND [PurchaseOrderDetails].[ProductId] = @v_ProductId

    RETURN CASE WHEN @NumOnOrder IS NOT NULL THEN @NumOnOrder ELSE 0 END
END

or you can include a Recomplie hint.

Upvotes: 0

Related Questions