November 17, 2011 at 4:52 am
Hi All,
I have seen a UPDATE statement as below in my database.
update factor
set net_return = fn_calculate(id,ret)
fn_calculate = scalar function
say for example if i have 10000 rows in factor table, the function will be called for 10000 times! right?
it will impact the performance. right?
Please let me know.
karthik
November 17, 2011 at 4:58 am
There's a small overhead just for calling the function.
That being said, if the function simply takes 2 parameters and then return their sum, that function will perform very well.
If you take another function and then access tables in that function, then this function has a much much higher risk of being slow as hell if your query.
You can now use cross & outer apply which mitigates this slowness but you need to test to make sure.
The way around that last problem is to use a derived table or temp table where you save intermediate results and use that in a join with the main query.
November 17, 2011 at 5:12 am
Code inside the function:
select @out_factor = factor from test_xrv
where id = @id and return_date = @out_date
return @out_return
Code inside the proc:
update dbo.test_xrv
set returns = dbo.FnMonthlyFactorsToReturns(id,return_date)
this will impact the performance. right?
It seems like they are using the same table in both the proc and function. I am just thinking why don't i do the calculaion inside the proc itself.
karthik
November 17, 2011 at 5:23 am
I just don't understand why it is being done like that. Makes no obvious sense.
What's the table definition, what's the purpose of the update?
November 17, 2011 at 5:37 am
karthikeyan-444867 (11/17/2011)
Code inside the function:select @out_factor = factor from test_xrv
where id = @id and return_date = @out_date
return @out_return
Code inside the proc:
update dbo.test_xrv
set returns = dbo.FnMonthlyFactorsToReturns(id,return_date)
this will impact the performance. right?
It seems like they are using the same table in both the proc and function. I am just thinking why don't i do the calculaion inside the proc itself.
Can you post the whole function, Karthik? The bit you've posted doesn't make a lot of sense.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 17, 2011 at 7:00 am
You could try to turn it into an ITVF.
It wouldn't make much sense either, but it would be faster at least.
-- Gianluca Sartori
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy