Friday, March 9, 2012

Function performances

I've made a user defined frunction that calculate a percentage reading data
from tables in db.
If i run the function with that code
set dateformat dmy
declare @.aaa DECIMAL(5,2)
set @.aaa=dbo.fn_name()
print @.aaa
or with that code
select dbo.fn_name()
it takes 21 seconds...too long
If i copy the code of the function and execute it in query analyzer, it
takes less than 1 second.
How is it possible?
Function is not necessarily good for all kind of operation especially
for manipulating data in sets.
If your need to manipulate data in sets, you might as well do it in
stored proc to give you better performance and try to avoid cursor as
much as possible.
Roberto Lo Baido wrote:
> I've made a user defined frunction that calculate a percentage reading data
> from tables in db.
> If i run the function with that code
> set dateformat dmy
> declare @.aaa DECIMAL(5,2)
> set @.aaa=dbo.fn_name()
> print @.aaa
> or with that code
> select dbo.fn_name()
> it takes 21 seconds...too long
> If i copy the code of the function and execute it in query analyzer, it
> takes less than 1 second.
> How is it possible?

No comments:

Post a Comment