Function + Performance

Question

Function + Performance

karthik M

One Orange Chip

Points: 29139
More actions
November 17, 2011 at 4:52 am

#250766

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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.

karthik M One Orange Chip Points: 29139 More actions · Answer 2

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

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?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

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