Problem Description / Requirement

A developer has heard from the new feature “Scalar UDF Inlining” and he wanted to improve a stored procedure [dbo].[get_last_order_months].This stored procedure is called 10 to 100 times per hour by the marketing department from a report that generates a variable list of customers. The stored procedure is passed—simplified for the purposes of this challenge—the number of customers to appear in the list.

He created a user defined scalar function which meets the conditions for inlining (see code)!

CREATE OR ALTER FUNCTION [dbo].[get_latest_order_months_inlining]
(
    @c_custkey BIGINT,
    @actual_date DATE
)
RETURNS INT
AS
BEGIN
RETURN
(
    SELECT DATEDIFF(MONTH, a.last_order_date, @actual_date)
    FROM
    (
        SELECT MAX(o_orderdate) AS last_order_date
        FROM dbo.orders
        WHERE o_custkey = @c_custkey
    ) AS a
);
END

Employees report that the report is very fast with small amounts of data. However, it becomes completely unusable with large lists.

Note: The goal is not to filter out specific customers, but solely—for the purposes of this challenge—to determine how performance changes as the number of customers in the list increases.

Problem

Employees report that the report is very fast with small amounts of data. However, it becomes completely unusable with large lists.

The developer is testing the stored procedure with both small and large datasets. He understands the employees’ observation.

/* Measure the cpu and execution time */
SET STATISTICS TIME ON;
GO

/*
    running the stored procedure with a small amount of data
    We clear the cache to avoid the side effects of parameter sniffing!
*/
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
EXEC dbo.get_last_order_months @num_customers = 100;
GO

/*
    running the stored procedure with a big amount of data
    We clear the cache to avoid the side effects of parameter sniffing!
*/
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
EXEC dbo.get_last_order_months @num_customers = 100000;
GO

Challenge

• Do a root cause analysis (execution plans, statistics, …)
• Rewrite the stored procedure so that it scales consistently for both small and large datasets.
• Explain to the customer the root cause of the bad performance.

Condition

• It is guaranteed that the user defined scalar function is inlineable!
• All indexes on the objects are sufficient for the performance.
• You are only allowed to change the code from the stored procedure

Connect to Microsoft SQL Server