SQL Server Function running very slow drop and recreate function fix the issue. Why it happens

user_19482124 0
Viewed 44

I have one very simple function to do a sum based on the give year, month and part.

CREATE FUNCTION [dbo].[GetOpenOrders]
     (@Part PartType, 
      @Month int, 
      @Year int)  
RETURNS DECIMAL(19,8)
AS  
BEGIN 
    DECLARE @OpenOrders int

    SELECT @OpenOrders = SUM(CASE WHEN (QtyReturned <> 0 AND QtyShipped = 0) THEN (QtyOrdered - QtyShipped) ELSE (QtyOrdered - QtyShipped+ QtyReturned) END)
    FROM CustomerOrder
    LEFT JOIN CustomerOrderItem ON CustomerOrder.CustomerOrderNum = CustomerOrderItem.CustomerOrderNum 
    WHERE Part = @Part 
      AND MONTH(ISNULL(ISNUL(CustomerOrderItem.DueDate, CustomerOrderItem.PromiseDate), CustomerOrder.OrderDate)) = @Month
      AND YEAR(ISNULL(ISNULL(CustomerOrderItem.DueDate, CustomerOrderItem.PromiseDate), CustomerOrder.OrderDate)) = @Year
      AND CHARINDEX(CustomerOrder.Status, ' O ') > 0
      AND CHARINDEX(CustomerOrderItem.stat, ' O') <> 0
 
    RETURN ISNULL(@OpenOrders, 0)
END

There are 200000 rows for customer orders, and 400000 rows for Customer order items.

We noticed the issue yesterday, the function is taking 300ms to completed, with parameters (not specific value, just regular part, Year and Month).

The index on the CustomerOrder and CustomerOrderItem has been created.

Not sure what is going on.

But drop the function and recreate it fix the issue. The execution time drop to 4-5ms.

Want to understand what is cause of it. Since we didn't do any change to this function since 2020

Thank you in advance

function sql-server sql
1 Answers

Think I misread your problem at first so here is an updated set of suggestions.

First of all I've seen many times when you really have to have things perform, that the solution is decomposing the function entirely into the query instead. Try it out and measure, just as an aside, you'd probably mostly want to not use functions in SQL server to let the query optimizer work without fixed stuff it cannot tune on. Still presume we have them and what can you do?

Scalar functions are not very efficient, besides the DRY element of stuff from a cosmetic and maintenance point of You, but you can consider rewriting it to use table valued like when you for instance call this function per row, like "for each part check open orders", which by the looks of it is a likely usage pattern, this is the second recommendation, that you should really change the function to become a Table Valued Function in so far that you will have to change the usage pattern of the function, you will see increased performance in that.

Secondly if You just would like to optimize this single function while perhaps having created the TVF and are slowly updating queries that use the old one, you can change it to be a Single statement function which also tends to work better, because of how the query optimizer works with execution plans and cardinality estimates.

ALTER FUNCTION [dbo].[GetOpenOrders]
     (@Part PartType, 
      @Month int, 
      @Year int)  
RETURNS DECIMAL(19,8)
AS  
BEGIN 
    
    RETURN COALESCE(SELECT SUM(
                        CASE WHEN (QtyReturned <> 0 AND QtyShipped = 0) 
                            THEN (QtyOrdered - QtyShipped) 
                        ELSE (QtyOrdered - QtyShipped+ QtyReturned) END)
    FROM CustomerOrder
    LEFT JOIN CustomerOrderItem ON CustomerOrder.CustomerOrderNum = CustomerOrderItem.CustomerOrderNum 
    WHERE Part = @Part 
      AND MONTH(ISNULL(ISNULL(CustomerOrderItem.DueDate, CustomerOrderItem.PromiseDate), CustomerOrder.OrderDate)) = @Month
      AND YEAR(ISNULL(ISNULL(CustomerOrderItem.DueDate, CustomerOrderItem.PromiseDate), CustomerOrder.OrderDate)) = @Year
      AND CHARINDEX(CustomerOrder.Status, ' O ') > 0
      AND CHARINDEX(CustomerOrderItem.stat, ' O') <> 0, 0)
END

This will in most cases perform better, so but when things get hairy and You see the performance degrade, use the SQL actual execution plan to know what went wrong in SQL Management Studio hit ctrl+M

user_29156150
Related