SQL Function with multiple CASE WHEN in the WHERE clause - slow performance

Question

UPDATE

I have tried most of the offered solutions and they work only when I pass a specific 'material_id'.

When I run the function on a bigger dataset it runs forever...

I work in Azure Synapse on a serverless SQL pool, so I am not sure if I can create indexes, as this was mentioned few times.

Could the problem really be in the indexing and not in the query itself?

It might be a silly question, but I am not as experienced as most of you so you will have to forgive me!!

I have a SQL function which works just fine when I am running it on a smaller table.

However, when I run the function in bigger queries the performance is so bad that I wouldn't even call it a working function...

From what I have read in some posts here, the fact that I have CASE WHEN statements as a nested query in a WHERE clause is the first problem.

The second, I imagine, is the fact that the query in the function is being execute for each row in the dataset (not sure, it is my understanding).

I am hoping that someone can help me re-write the query so that it works on bigger datasets.

The purpose of the function is to find the most recent material price at the time a material order is created.

CREATE FUNCTION my_db_name.get_price_at_mo_order
    (@Material_Id VARCHAR(20), 
     @MO_Date DATE)
RETURNS table AS  
RETURN 
    (SELECT  
         mp.price, m.product_code 
     FROM 
         my_db_name.price mp 
     LEFT JOIN 
         my_db_name.material m ON mp.material_id = m.material_id 
     WHERE 
         mp.material_id = @Material_Id 
         AND mp.start_date = (SELECT DISTINCT 
                                  CASE 
                                      WHEN (SELECT COUNT(*)
                                            FROM my_db_name.price
                                            WHERE material_id = @Material_Id
                                              AND start_date <= @MO_Date) >= 1 
                                          THEN (SELECT MAX(CAST(start_date AS DATE))
                                                FROM my_db_name.price
                                                WHERE material_id = @Material_Id
                                                  AND start_date <= @MO_Date)
                                      WHEN @MO_Date < (SELECT MIN(CAST(start_date AS DATE))
                                                       FROM my_db_name.price
                                                       WHERE material_id = @Material_Id)
                                          THEN (SELECT MIN(CAST(start_date AS DATE)) 
                                          FROM my_db_name.price
                                          WHERE material_id = @Material_Id)
                                      ELSE  
                                          (SELECT MAX(CAST(start_date   AS DATE)) 
                                           FROM my_db_name.price
                                           WHERE material_id = @Material_Id)
                                  END AS 'test'
                              FROM 
                                  my_db_name.price))

For query performance questions we need at a minimum: the full query involved (please show where you call the function), the table and index definitions involved, and please share the query plan via pastetheplan.com. Otherwise this question is not answerable. — Charlieface
– Charlieface, Commented Jan 29 at 15:32
The problem has more to do with the nested selects than the CASE expression. Find a way to JOIN to this table first to get the values, and then filter after, and it can be faster. — Joel Coehoorn
– Joel Coehoorn, Commented Jan 29 at 15:33
I'd consider a window function or cross apply to get just the 1 record from price needed. Assuming you want the minimum price for a material when provided or the min price after a certain date — xQbert
– xQbert, Commented Jan 29 at 15:39
Why left join from price to material and not the other way? Wouldn't you want all materials and the price with date => the date requested? What should happen if the material has no price record? — xQbert
– xQbert, Commented Jan 29 at 15:50
Use the answer section, @FredAlisson . Comments aren't for full solutions, like you have here. — Thom A
– Thom A ♦, Commented Jan 29 at 15:51

ValNik · Accepted Answer · 2025-01-29 22:21:15Z

Main part of query is calculate best fit start_date for target date @MO_Date.
Best date is

Nearest date before target date - date1
Nearest (least) date after targe - date2
Max date overall - date3. I don't think this date is really necessary.If date2 is null, this date will also be null.

We calculate this dates in one subquery and apply coalesce(date1,date2,date3)=mp.start_date

JOIN with table material is not necessary, since only one row will be selected, it is easier to select this row from table material using a subquery.

I added 2 columns to the output result of the function for clarity.
Remove them as unnecessary.
See example.
Test data:

id	material_id	price	start_date
1	material-100	2	2024-12-20
2	material-100	2.1	2024-12-23
3	material-100	2.3	2025-01-29
4	material-100	2.4	2025-02-28
5	material-100	2.5	2025-03-29
6	material-101	4	2025-01-02

material_id	product_code
material-100	material-100_code
material-101	material-101_code
material-102	material-102_code

Function definition

CREATE FUNCTION get_price_at_mo_order(@Material_Id VARCHAR(20), @MO_Date DATE)
RETURNS table AS  
RETURN 
( 
  SELECT mp.price
    ,(select product_code from material m2 
      where m2.material_id=mp.material_id) as product_code 
       -- additional columns for clarity
    ,mp.start_date
    ,@MO_Date as paramDate
  FROM price mp 
  WHERE mp.material_id = @Material_Id 
    and mp.start_date=(   
      SELECT coalesce(
           MAX(case when start_date <= @MO_Date then start_date end) -- max date<=@MO_date near to @MO_date
          ,MIN(CAST(start_date AS DATE))    -- min date (after @MO_date if no rows before)
          ,MAX(CAST(start_date AS DATE))    -- max date at all  - no need real
        ) dt
      FROM price p2 WHERE p2.material_id = mp.material_Id
   )
)

Tests
We have not rows before target date and have after. Take first after.

select * from get_price_at_mo_order('material-100',cast('2024-12-01' as date));

price	product_code	start_date	paramDate
2	material-100_code	2024-12-20	2024-12-01

We have rows before and after. Take last before.

select * from get_price_at_mo_order('material-100',cast('2025-01-01' as date));

price	product_code	start_date	paramDate
2.1	material-100_code	2024-12-23	2025-01-01

We have rows before and after. Take last before.

select * from get_price_at_mo_order('material-100',cast('2025-02-20' as date));

price	product_code	start_date	paramDate
2.3	material-100_code	2025-01-29	2025-02-20

We have rows before and not after. Take last before.

select * from get_price_at_mo_order('material-100',cast('2025-06-01' as date));

price	product_code	start_date	paramDate
2.5	material-100_code	2025-03-29	2025-06-01

Other tests

select * from get_price_at_mo_order('material-101',cast('2025-01-01' as date))

price	product_code	start_date	paramDate
4	material-101_code	2025-01-02	2025-01-01

select * from get_price_at_mo_order('material-101',cast('2025-06-01' as date))

price	product_code	start_date	paramDate
4	material-101_code	2025-01-02	2025-06-01

And last test will return empty result

select * from get_price_at_mo_order('material-102',cast('2025-06-01' as date))

price	product_code	start_date	paramDate

fiddle

Ragnar · Accepted Answer · 2025-01-29 15:49:59Z

Maybe you can try instead of running multiple subqueries, you can try function using TOP 1 with ORDER BY to get the most relevant price directly.

Having data sample would greatly help create better query.

CREATE FUNCTION my_db_name.get_price_at_mo_order(@Material_Id VARCHAR(20), @MO_Date DATE)
RETURNS TABLE 
AS  
RETURN 
(
    SELECT TOP 1 
        mp.price, 
        m.product_code 
    FROM my_db_name.price mp
    LEFT JOIN my_db_name.material m
        ON mp.material_id = m.material_id
    WHERE mp.material_id = @Material_Id 
    ORDER BY 
        CASE 
            WHEN mp.start_date <= @MO_Date THEN mp.start_date 
            ELSE NULL 
        END DESC, 
        mp.start_date ASC 
);

Finally to have a normalized and optimized query, you should also have a normalized db. Here are some steps and normalization forms you need to follow: Database Normalization

xQbert · Accepted Answer · 2025-01-29 15:53:30Z

1

cross apply: Return material price and product code for a material whose pricing date is provided. Ensure to retrun the price record with the date before the provided date and closest to it.

Something like

SELECT mp.price, m.product_code 
FROM my_db_name.material m
CROSS APPLY (SELECT TOP 1 * FROM my_db_name.price mp 
             WHERE mp.material_id = @Material_Id
               and start_Date <= @mo_Date
               and mp.material_id = m.material_id
             ORDER start_date Desc)

answered Jan 29 at 15:53

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

2 Comments

Charlieface Jan 29 at 15:59

That doesn't take into account the final WHEN clause where there are no rows before the @MO_Date

xQbert Jan 29 at 18:04

agreed. THus the reason for "something like", it was a response in passing to provide direction; not a full and complete answer with demo and explanation

Charlieface · Accepted Answer · 2025-01-29 15:58:26Z

It looks like you can simplify your code like this, by using TOP (1) in an apply to grab the first row of the price table before the date. Then a second apply to grab any first row if the previous apply returned nothing.

CREATE OR ALTER FUNCTION my_db_name.get_price_at_mo_order(
  @Material_Id VARCHAR(20),
  @MO_Date DATE
)
RETURNS table
AS RETURN 
( 
    SELECT
      ISNULL(p1.price, p2.price) AS price,
      m.product_code 
    FROM my_db_name.material m
    OUTER APPLY (
        SELECT TOP (1)  -- possibly WITH TIES?
          p.*
        FROM my_db_name.price p
        WHERE p.material_id = @Material_Id
          AND p.start_date <= CAST(@MO_Date AS datetime)
        ORDER BY p.start_date DESC
        -- do you need multiple rows for a single date?
        -- Then use ORDER BY CAST(p.start_date AS DATE) DESC
    ) p1
    OUTER APPLY (
        SELECT TOP (1)
          p.*
        FROM my_db_name.price p
        WHERE p.material_id = @Material_Id
          AND p1.price IS NULL    -- previous APPLY was empty
        ORDER BY p.start_date DESC
        -- do you need multiple rows for a single date?
        -- Then use ORDER BY CAST(p.start_date AS DATE) DESC
    ) p2
    WHERE m.material_id = @Material_Id
      AND ISNULL(p1.price, p2.price) IS NOT NULL
);

I would expect the following indexes to be the best:

Clustered index on material (material_id)
Non-clustered index on price (material_id, start_date DESC) INCLUDE (price)

Collectives™ on Stack Overflow

SQL Function with multiple CASE WHEN in the WHERE clause - slow performance

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related