CODEWITHSUNDEEP
sql-serversql-server-2008query-optimizationuser-defined-functions
Cavyn VonDeylen
Cavyn VonDeylen

Reputation: 4249

Convert scalar UDF in select to table valued function

I have 2 tables. Table 1 (DateTable) contains dates and item names, table 2 (PriceTable) contains prices over time and dates that the price changed.

Table 1 ( DateTable)

| itemName |   Date   |
|    A     | 2012-8-22|
|    A     | 2012-9-2 |

Table 2 ( PriceTable)

| Update Date | Price|itemName|
|  2012-7-1   | 5.00 |   A    |
|  2012-8-1   | 5.50 |   A    |
|  2012-9-1   | 6.00 |   A    |

My task is, given a date, I want to determine what the price of the item was on that date.

I'm currently doing this with a scalar function, which is really slow. Is there a way I could rewrite this, either as a table valued function or using something else that will help speed this up a bit? I've been staring at this for awhile now to no avail.

My current queries:

select
    d.date
    ,dbo.fn_GetPrice(d.date, 'A')
from DateTable d
where d.itemName = 'A'

and function

alter function fn_GetPrice(@date DateTime, @itemName varchar(50))
returns int
as
begin
declare @price int
    select @price = p.price
    from PriceTable p
    where p.itemName = @itemName
        and p.updateDate = (select MAX(p2.updateDate)
            from PriceTable p2
            where p2.updateDate < @date
                and p2.itemName = @itemName)
return @price
end

Upvotes: 0

Views: 1218

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

Would this work? Replacing the udf with just a join?

select
    d.date,
    p.Price
from 
    DateTable d 
    join PriceTable p
        on p.itemName = d.itemName
        and p.updateDate = (
            select max(p2.updateDate)
            from PriceTable p2
            where 
                p2.updateDate < d.date
                and p2.itemName = p.itemName
        )
where d.itemName = 'A'

Upvotes: 1

Related Questions