June 13, 2012 at 3:05 am
I now that UDF are fare more slower than Stored procedure due to the compilation.
But what about Table value functions ?
Tks.
June 13, 2012 at 3:19 am
DZN61 (6/13/2012)
I now that UDF are fare more slower than Stored procedure due to the compilation.But what about Table value functions ?
Tks.
Yes, sure! And submarines are slower than tanks! The only problem is that:tanks cannot go far under the water and submarines are not very maneuverable on the ground.
UDF and Stored Procedures are two different types of object and comparing them by performance (which has nothing to do with compilation btw) is irrelevant.
Saying that, the Table-valued functions (TVF) do perform better than Scalar ones in cases where Scalar UDF functionality can be implemented as TVF. Again, you cannot compare them to stored procs!
June 13, 2012 at 6:01 am
The reason that functions are slower than stored procedures is not because of compilation. They're both compiled. It's how things get compiled that are the issue.
If you're talking about an in-line table valued function, they can perform very well, depending on how you write them (no different than any query). But multi-statement table valued functions are a different critter. These use table variables as their structure. Table variables have no statistics. So, they are compiled assuming one row will be there. This can work fine if there is only one row, or a few. But as the number of rows returned grows, these queries, compiled for a single row, become slower and slower. It just gets worse when you try to put WHERE clauses on them or JOIN against them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 13, 2012 at 6:05 am
Tks Grant for your answer.
My table value functions (both inline and multistatements) returns about 500 rows...
For what I have understood from your statement is this is not foreseen for such use....
What do you propose ?
-temp tables?
-other solution?
Tks for your help.
June 13, 2012 at 6:07 am
Depends on what you're doing. The in-line TVF will probably be fine if you can use that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2012 at 6:10 am
Hi Gail,
as specified I'm using both inline and multistatement table functions to get the final results.
What's surprising me is that is take 8 seconds to get the results which is pretty much... (a lot too much)....
Of course the process is not that simple...
June 13, 2012 at 6:14 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/, the one with the inline function as those are easier to optimise.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2012 at 6:18 am
Hi Gail,
will do this with pleasure.
Give me some times to check first myself and I come back !
Many tks for your help !
June 13, 2012 at 6:33 am
DZN61 (6/13/2012)
Tks Grant for your answer.My table value functions (both inline and multistatements) returns about 500 rows...
For what I have understood from your statement is this is not foreseen for such use....
What do you propose ?
-temp tables?
-other solution?
Tks for your help.
Like Gail says, it depends on what you're doing. But no, the multi-statement UDF is not designed to support hundreds of rows, at least no if you're also filtering or joining those things. They don't have statistics, so they can't support that approach.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply