February 3, 2003 at 2:17 am
I don't know if this is true so I would like some Expert opinion about this.....
Lots of performance problems are caused by functions. I rewrite them into a Stored Proc and the system is fast again....
But why are functions so slow...
When should they be used ????
And WHY are they so slow ?????
Can anybody give me some background on this..... ????
February 3, 2003 at 2:49 am
User defined functions are typically used in select queries to manipulate some data i.e.
select a,b,dbo.fnUDF(c) from table
A stored procedure could not be used in this case.
Not sure about the performance problems you talk about, I've never experienced anything like this. Can you give more information on what the UDF is doing?
Regards,
Andy Jones
.
February 3, 2003 at 3:16 am
The function is a lookup in anotjer table and returns a Table
February 3, 2003 at 3:19 am
Keep in mind that a function is inline processing of every row with it's set of functionality. Depednin on what you are doing and how many rows are processed it can get processor consuming. It might help give you a more concise answer thou if you would post what the code in the UDF looked like and how the SP looks. Also keep in mind that SPs can save their execution pan so even then you are getting a performance boost for the query itself.
As stated, if retireves from another table and returns a table then most likely it is the fact that it doesn't store an execution plan. There must be some intensivness to the query and thus of course the SP has several advantages the UDF doesn't with the execution plan.
Edited by - antares686 on 02/03/2003 03:24:01 AM
February 3, 2003 at 3:48 am
Antares are you telling me that a UDF doesn't store a execution plan ?????
That implies that you must never use data retrieval functions in a UDF ...
Am i wrong here ?
February 3, 2003 at 5:37 am
Unless I am wrong, I would not personally suggest doing so unless you need the function inline or have special needs for it. However after going thru the documentation in SQL BOL I have never found mention of a stored/saved execution plan with UDFs, so I would not put faith in it being there.
February 3, 2003 at 7:11 am
Thanks Antares,
I looked in the books as well (First thing I do) but i couldn't find anything...
I was wondering... So I decided to post this question. And the answer is what I thought myself.
Thanks A lot.
February 7, 2003 at 3:29 pm
New discovery for me. Functions don't seem to take advantage of indexes and may executre only once (run as determinsitic) depending on various factors which means the results only output once and get reused each time on multiple runs. Check out this thread http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=9837&FORUM_ID=8&CAT_ID=1&Topic_Title=UDF%27s+vs+Procs&Forum_Title=T%2DSQL
February 10, 2003 at 9:24 am
I had an experience where the performance was much worse with a UDF. I was summarizing values in a table with several million rows and was using a UDF in the select and group by clauses. The UDF returned the date portion of a datetime column. This function was
CREATE FUNCTION utlcvt_StripTime (@CallStartTime datetime)
RETURNS datetime
AS
BEGIN
RETURN (CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,@CallStartTime))))
END
GO
Although this is probably not the most effective way to strip the time, it is serviceable for my discussion. To test the UDF I tried running the following two queries (see below). One query had the UDF in it (in both the select and group by clauses). The other query has the function directly in the SQL statement. The Usage table has 10 million rows in it, and I found that the query with the UDF took 5 times longer than having the function “in-line”. My assumption is that I am calling the UDF at least 20 million times (twice for each row; Once for the select clause, and once for the group by clause), and that the overhead of the UDF calling is becoming significant. When I deal with a much smaller data set the overhead is not nearly as noticeable. I apologize for not giving more specific numbers, but I ran the tests 6 months ago and didn’t document it for posterity.
Do you think I am correct in my assumption about the overhead of the function call, or could there be something else more likely?
/* SLOW QUERY */
SELECT
CI.BillingGroupID,
dbo.utlcvt_StripTime(CallStartTime) As DateOfUsage,
Sum(Duration) As Seconds,
Count(*) As NumberCalls,
Sum(Charge) As Charge,
Sum(Cost) As Cost
FROM Usage U, CustomerInfoTemp CI
WHERE U.CustID = CI.CustID
GROUP BY BillingGroupID, dbo.utlcvt_StripTime(CallStartTime)
/* FASTER QUERY */
SELECT
CI.BillingGroupID,
CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,CallStartTime)))) As DateOfUsage,
Sum(Duration) As Seconds,
Count(*) As NumberCalls,
Sum(Charge) As Charge,
Sum(Cost) As Cost
FROM Usage U, CustomerInfoTemp CI
WHERE U.CustID = CI.CustID
GROUP BY
BillingGroupID,
dbo.utlcvt_StripTime(CallStartTime)
Robert F Knopf
Robert F Knopf
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply