February 7, 2003 at 12:05 am
Greetings all.
I have quite a few procs which exec other procs to do away with rewriting code.
I am (was) quite happy with the speed at which they ran.
I then started playing with UDF's. I found, what I think to be unrealistic, improvement in speed.
Example:
A proc accepts 2 values, one of which is an output. It does an index search on a table with about 25000 rows and returns the found record. It's a distinct select.
I then made a function which does exactly the same.
Running these two in a loop (Seperate loops) 100 times was what surprised me.
The proc too about 1700ms to run 100 times. The function however only took 30ms to run 100 times. Is this correct?
I timed it by getting the date (GetDate()) before the loop started then again when complete and doing a dateDiff on the two values.
Am I missing something?
I want to convert all the procs I exec to functions. Should I beware of something?
I have not found anything saying UDF's are evil.
Thanks!
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
February 7, 2003 at 3:53 am
UDFs are quite great and have several advanatage factors. However, keep in mind a UDF does not store an execution plan so it loses that advantage as opposed to an SP unless run extrememly often then it may take advantage of an in memory execution plan in the cache.
Couple of thoughts as I have seen the opposite as well. What are you doing different in the function that you did not do in the SP.
Did you remember SET NOCOUNT ON in the SPs being called. Some overhead involved in that not being on.
Have you made any changes to tables, indexes, etc and no ran did an sp_recompile against the related SPs to have them rebuild their execution plans. If you didn't they may have an out of sync execution plan with regards to current infrastructure.
Which did you run first the SP or the UDF. If the SP then keep in mind when you ran the UDF it may have seen the execution plan last used by the SP as well as data in the cache related to it which gave it a performance boost. Especially if since the SP may have had to call the data from the disk first time and not from the data cache in memory. So here it is a simple matter of apples to oranges. Best way to be sure is to:
Run DBCC FREEPROCCACHE
Run DBCC DROPCLEANBUFFERS
Run SP
Run DBCC FREEPROCCACHE
Run DBCC DROPCLEANBUFFERS
Run UDF
Run DBCC FREEPROCCACHE
Run DBCC DROPCLEANBUFFERS
Run SP again
Run DBCC FREEPROCCACHE
Run DBCC DROPCLEANBUFFERS
Run UDF again
Then compare the results of each. ALso, then run SP and UDF with Show Execution Plan and see if any unexpected differences in the plan occurr.
February 7, 2003 at 4:32 am
Crappy,
I have seen systems with functions.
Rewriting the UDF to SP's have solved a lot of performance problems....
I think your test is unrealistic.
February 7, 2003 at 5:00 am
Hi Antares686,
Thanks for the reply.
I did what you suggested and they all slowed down. BUT, the UDF's still ran much faster.
Proc (100 times) was 1913 and UDF (100 times) was 210.
As far as your comment about the execution plan, all the function will, as the proc does, run in a loop. The main proc which calls them is also run often.
Set NoCount is off in the proc and cannot see any other way it might be in-efficient.
Looksing at a another users post, he/she says UDF's are slower than procs?
Do you know of any reason?
As much as I want to believe the stats, I still find it hard to believe the time differences.
When I ran the function and SP with the DBCC commands around them, the proc took 10ms and the function 0ms.
Any suggestions? I would hate to change all the code to use UDF's as apposed to SP's basing it on misinterpretation.
Thanks for the help and advice.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
February 7, 2003 at 5:10 am
Not sure why the UDF would be faster but there has to be an underlying factor here. Can you pots some details of the Tables referneced, the SP and UDF sample and the output from running the following.
SET SHOWPLAN_TEXT ON
GO
Run SP single time
GO
Run UDF single time
GO
The output will allow you to see if the execution is being handled in someway different. I have not myself seen a UDF faster than a Proc but knowning what each is doing may give me a better idea why.
February 7, 2003 at 5:19 am
Greetings, Here's the test.
I can see why it's faster but not sure why it would be...
Crispin
StmtText
-----------------------
SET SHOWPLAN_TEXT ON
(1 row(s) affected)
StmtText
---------------------------------------------------------------------------------------------------
Exec Test_CP 410, ''
CREATE Procedure Test_CP
@MakeCodeInt,
@MakeDescVarchar(100) OUTPUT
as
Set NoCount On
Select DISTINCT @MakeDesc = MakeDesc From CCMakeModel Where MakeCode = @MakeCode
(3 row(s) affected)
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------
|--Sort(DISTINCT ORDER BY:([Expr1002] ASC))
|--Compute Scalar(DEFINE:([Expr1002]=Convert([CCMakeModel].[MakeDesc])))
|--Clustered Index Scan(OBJECT:([PUMA].[dbo].[CCMakeModel].[IX_CCMakeModel]), WHERE:(Convert([CCMakeModel].[MakeCode])=[@MakeCode]))
(3 row(s) affected)
============================================================================================
StmtText
-----------------------
SET SHOWPLAN_TEXT ON
(1 row(s) affected)
StmtText
-------------------------------------------
Select dbo.GetVehicleMakeByCode('410')
(1 row(s) affected)
StmtText
-------------------
|--Constant Scan
(1 row(s) affected)
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
February 7, 2003 at 5:37 am
Sorry, can I get the def of GetVehicleMakeByCode UDF. I assume like the SP but want to be sure how you defined it exaclty. Then I will have a way to test apples to apples later today when I get a chance.
February 7, 2003 at 5:48 am
Here you go.
Cheers,
Crispin
CREATE FUNCTION GetVehicleMakeByCode
(
@MakeCodeVarchar(20)
)
RETURNS Varchar(100) AS
BEGIN
Declare
@vMakeNameVarchar(100)
If IsNumeric(@MakeCode) = 1
Begin
Set @vMakeName = (Select Distinct MakeDesc From CCMakeModel Where MakeCode = @MakeCode)
End
Else
Begin
Set @vMakeName = (Select Distinct MakeDesc From CVMakeModel Where MakeCode = @MakeCode)
End
Set @vMakeName = IsNull(@vMakeName, 'Unknown')
Return @vMakeName
END
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
February 7, 2003 at 6:04 am
Does MakeCode have a lot of duplicates?
Can you tell me the definition of this clustered index [PUMA].[dbo].[CCMakeModel].[IX_CCMakeModel]?
Is this a composite index with more than one column involved and if so which item is first?
Also, is MakeCode part of any other index?
February 7, 2003 at 11:29 am
Here are the only indexes on that table.
CREATE CLUSTERED INDEX [IX_CCMakeModel] ON [dbo].[CCMakeModel]([MakeCode]) ON [PRIMARY]
CREATE INDEX [IX_CCMakeModel_1] ON [dbo].[CCMakeModel]([ModelCode]) ON [PRIMARY]
CREATE INDEX [IX_CCMakeModel_2] ON [dbo].[CCMakeModel]([CombinedModelSfx]) ON [PRIMARY]
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
February 7, 2003 at 11:48 am
Try running DBCC DBREINDEX against that table and it's indexes to see if anything in the SP changes. I get a seek when I test on 25000, 50000 and 100000 records. Which outperforms the UDF with it's COSTANT SCAN. So something is causing the SP to do a SCAN instead of seek when for a single or small range should be what occurrs.
February 7, 2003 at 12:09 pm
No luck, still runs faster.
What I have noticed is the proc and UDF run the same, or close to, speed if only one is run. It's when you run them in a loop, which is what it's gonna do in production, when the UDF appears faster.
The table design, in my view, is poor design.
It's a listing of all vehicle make and models. There are duplicate makecodes. One table used instead of two.
I tried using a different make code in the loop with the same time difference.
eg. Print dbo.GetVehicleMakeByCode(410+ @vCount)
I would think that this would ensure that SQL actually searched for the data.
Could it be that SQL does not "like" running a proc in quick succession and a UDF is designed to do that?
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
February 7, 2003 at 2:23 pm
I think I understand now. And it makes perfect sense. See the key is I only tested a single run and when I increased the conditions to scan thru the UDF got slower.
So the clue is the multiple runs.
Now some good reading that may or may not help. See SQL BOL "Deterministic and Nondeterministic Functions" and "CREATE FUNCTION" the section "Function Determinism and Side Effects".
What seems to be happening is the Function contains a condition that SQL Server implemented it as deterministic. Meaning the Function only actually runs one time, the data is kept and reused every single run during the operation.
This is similar to creating a variable for the output of your SPs output variable once and then instead of running the SP every again you just reference the variable instead.
Concerns that you then should test. If the UDF is acting deterministic it should when the input changes change the result but may not so you should verify this as will impact you in production run if that occurrs.
Now the SP would still have a better operating expense overall, especially if the table size increases drastically in the long run. If you could I would suggest a method to create a variable and populate with data from the SP only when condition to be submitted to SP is changed (in otherwords only run the SP when you know it will be needed). This will have the same effect as the UDF for now but as I said major size increase will not has as bad an effect as UDF I am sure would show (now I could be wrong here as I don't know at what point it may freak out, could take forever to occurr).
Now another cosideration the UDF may present as an issue as would my SP and variable suggestion is this. If the input doesn't change and your query work does need to be subjected to INSERTS, UPDATES and DELETES (as maybe it the MakeDesc changed while processing now you need the changed value instead of the one held) then SP will be your best bet. I am sure if I am right about the determinism situation which I would explain your results, that that will be the case.
Hope that all makes sense.
February 7, 2003 at 2:52 pm
ahhhh, the penny drops.
Thanks for the help. I shall go read BOL and see what it says.
From the apparent scans, a UDF does not use the index. Does this seem correct or am I missing something?
One of the tests I did do was pass make code as @vCount. This ensured that SQL actually had to look for the data each time and return them. Surly that would prove that SQL was not caching the result of the function.
What I'm gonna to is take the slowest proc and convert all the execed SP's to UDF's and see the improvement/decrease in speed.
Antares, Thanks for your help! Much appreciated.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
February 7, 2003 at 3:00 pm
quote:
From the apparent scans, a UDF does not use the index. Does this seem correct or am I missing something?
That is correct, or at least in all my testing it ignored them.
quote:
One of the tests I did do was pass make code as @vCount. This ensured that SQL actually had to look for the data each time and return them. Surly that would prove that SQL was not caching the result of the function.
Hope so, but it may run it nondeterministic then and cause it to perform as I first expected. Still not exactly sure the decisions for determinsitic and nondeterministic.
quote:
What I'm gonna to is take the slowest proc and convert all the execed SP's to UDF's and see the improvement/decrease in speed.
As long as you needs are met without being a potential threat to database consistancy then always go with the faster solution.
quote:
Antares, Thanks for your help! Much appreciated.
I just love a challenge and this seemed like the most interesting challenge on the board at the time.
"Actually, my shrink said I should share. Said it would help me learn to let go of my personal issues. So here I share." -- Me about 30 seconds before this post.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply