Stored Procedure
CREATE procedure [dbo].[ImproveProcedure] (@port varchar(50), @portdate datetime)
as
Declare @intdate datetime
select @intdate = max(rate_date) from Interestrate where rate_type = 'Zero'
and rate_date <= @portdate
Update transactiontable set NonDiscount = null, Discount = null, NonDiscountTcurr = null, DiscountTcurr = null,
NonDiscountNew = null, DiscountNew = null
where
port = @port and portdate = @portdate
Update tr set NonDiscount = (case sss_ind when 'P' then -exposure else exposure end) *
dbo.Foo(ccd, 'USD', @portdate,
case when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) < 1 then 1
when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) > 48 then 48
else datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature)))
end),
NonDiscountTcurr = (case sss_ind when 'P' then -exposure else exposure end)
from
Phy p1
where
port = @port and portdate = @portdate
and
tr.trans = p1.trans
and
p1.Sub <> 'Option'
Update tr set NonDiscount = (case when buysell in ('A', 'S') then -vol * markprice else vol * markprice end) *
dbo.Foo(ccd, 'USD', @portdate,
case when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) < 1 then 1
when datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature))) > 48 then 48
else datediff(m, portdate, isnull(priceend1, isnull(priceend2, mature)))
end),
NonDiscountTcurr = (case when buysell in ('A', 'S') then -vol * markprice else vol * markprice end)
from
Phy p1
where
port = @port and portfolio = @portfolio
and
tr.trans = p1.trans
and
p1.Sub = 'Option'
CREATE function [dbo].[Foo]
(@currency1 varchar(10),
@currency2 varchar(10),
@portdate datetime, @month int) returns float
As
BEGIN
Declare
@CurrentRate float,
@Ratedate datetime
select @Ratedate = max(rate_date) from fx where
(
currency1 = @currency1 and currency2 = @currency2 and forward_month = @month
or
currency2 = @currency1 and currency1 = @currency2 and forward_month = @month
)
and
@portdate >= Rate_date
IF exists ( select * from fx where currency1 = @currency1
and currency2 = @currency2 and rate_date = @Ratedate and forward_month = @month)
SELECT @CurrentRate = Rate from fx where currency1 = @currency1 and
currency2 = @currency2 and rate_date = @Ratedate and forward_month = @month
ELSE
IF exists ( select * from fx where currency1 =
@currency2 and currency2 = @currency1 and rate_date = @Ratedate and forward_month = @month)
select @CurrentRate = 1/Rate from fx where currency1 = @currency2
and currency2 = @currency1 and rate_date = @Ratedate and forward_month = @month
ELSE
select @CurrentRate = 1
return (@CurrRate )
END
I understand that scalar function calls can limit the performance of a query. Can someone see a way to remove the function calls, replacing them with a JOIN
or CROSS APPLY
or something instead, to see if this helps performance? I've tried, but haven't been able to find a way.
More info about current performance: A single run of the procedure, updating ~25,000 rows, takes 20-25 minutes to run. Looking at the execution plan, I see a lot of reads tied up in the function, and I'm thinking that with a JOIN
solution (or some such), that number of reads would drop dramatically, and the performance may improve. Plus, as noted above, I've heard scalar function calls are (in general) "bad" in queries.
Best Answer
Here's an attempt to rewrite the function into the procedure:
Your function uses
ccd
,portdate
, and the number of months betweenportdate
and three possible dates (using the first that's not NULL) to calculate the rate that theexposure
should be multiplied by.currency1
is 'USD', then you multiple byrate
;currency2
is 'USD', then you divide byrate
;rate
defaults to 1So, let's try to build a table with your available rates in it.
We have basically have two factors that identify the
rate_date
values we'll use:The maximum date for each of these will determine what rate is needed.
That gives us at most 48 * (number of currencies) rows to worry about, in practical terms. I'll guess that's 10-15 thousand rows, not an unreasonably large table.
For joining purposes, it will help to standardize the currency, so the first one is always
USD
- that way we don't have to worry about whether we're multiplying or dividing by the rate.So, we build a temporary table with the unique values of
currency1
,currency2
, andforward_month
(always putting 'USD' in the temp table'scurrency1
column), and the maximumrate_date
for each unique set of those three values.Then, we add in the rates. In case it's relevant, we'll do it just like you did; If we have a case where 'USD' is in
fx
ascurrency1
, we use that in preference over any rows where 'USD' is incurrency2
. Where 'USD' is incurrrency1
, we storerate
directly; where it's incurrency2
, we need to divide by the rate, so we store1 / rate
.Then, instead of using the function to find the rate, we join to the temporary table, based on:
currency1
being 'USD'currency2
being theccd
from the other tables in the queryrate_date
matching theportdate
column (which matches the passed-in@portdate
)portdate
, and the first ofpriceend1
,priceend2
, andmature
that's not NULL. (Note:COALESCE
takes a list of two or more values, and returns the first non-NULL value from the list; it's a little easier to read thanISNULL(x,ISNULL(y,z))
, but works the same)We make this a
LEFT JOIN
, so we include rows where we can't find a matching row in the temp table.Then, we simply multiply the appropriate value (depending on the value of
Sub
) by the rate returned (or by 1, if we get back a NULL, either because the temp table had no match, or because the temp table had a matching row with a NULLrate
).NOTE: I combined your original two UPDATE statements into one, adding another
CASE
on the value ofp1.Sub
. To guarantee that this is working like your original two statement version, I also put in a check to see ifp1.Sub
isNULL
; if that cannot beNULL
, then you can remove that check.Try this out and see if it works for you. It's not fully optimized (I think we could reduce the rows in the temp table by checking for currencies that are actually in our target transaction rows, for example) but it does remove the row-by-row nature of the function you were using.
NOTE: code is untested.