SQL Server Performance Forum – Threads Archive
migrating from sp to functions
We are planning to rewrite the some of stored proceedures into table valued functions. IS there any major performance issue?kIRAN
SQL DBA
there can be perf hits for functions used queries. are you planning this? see:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78601 www.elsasoft.org
Please do not double-post.
Thanks for your reply. I am not using the function inside the query.
i have query inside the function. and same function is exists in all the databases. from stored proceedure we making union of all results of functions outputs.
So function will execute only once per database.
Can tell me is it worth write to functions or not?
kIRAN
SQL DBA
A function will execute each time that you call it. Perhaps you are thinking of a view? However, data will be retrieved each time you refer to the view. In a stored procedure, you can use a Common Table Expression as a "data buffer". A CTE will be executed once each time that you call the stored procedure.
If i use the table value function, is there any performance issue with recompiling or query plan? kIRAN
SQL DBA
The use of scalar functions can lead to table scans instead of index scans/seeks. The reason for that is that SQL Server cannot predict the value that will be returned from the function. I would imagine that table-valued functions can have the same effect – but I would be intrigued to know what others may have found.
You’ll be fine if you are using inline table valued function. And if you are not, A stored procedure is a better option. My rule of thumb is, if the requirement can be satisfied with a procedure, go for it. Use function only when there is no other option. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
Since we need to migrate 100 stored proceedures to table valued functions, development work will be less in table valued functions compared to procedures.
Is there any other performance issues anybody has in table-valued functions.? kIRAN
SQL DBA
Views and functions have the advantage of closure (the output is of the same form as other objects like tables). This is a good thing from a code-design point of view. Basically, compare these: select col, col2 from myView inner join someTable on myView.key = someTable.key select col, col2 from dbo.myfunction( ‘foo’ ) inner join someTable on myfunction.key = someTable.key select col, col2 from aStoredProc ‘Foo’ inner join sometable on oops.itsbroken = this.does.not.have.closure For this reason, views and table-valued functions have some advantage. From a performance point of view, you want code that will optimize fully and will allow plan reuse; views I think are the best, allowing both, followed by functions, which I think enable the second but not the first (I might be wrong here — I’m not sure if SQL server can "expand" a table-valued function and optimize its body with the rest of the surrounding code). Stored procs that only return single result sets are (my opinion, mind you) not the best idea. This closure thing makes them quite unflexible. I had a super article on this topic, but cannot locate the book mark – if I find it I’ll post it here.
This is not the link I was thinking of, but it’s interesting: http://allinthehead.com/retro/247/user-defined-functions-considered-harmful You have to watch out that the modularity of the UDF code doesn’t defeat what the optimizer could otherwise do to give you performance advantage. In other words, if a query has "straight up" SQL (tables, views, joins) then the optimizer can rewrite it to any logically equivalent execution plan; if the query is broken up into a bunch of UDF "modules," on the other hand, it cannot perform this rewrite, or at least its options are limited because it will not be able to tell what other possible execution plans are logically equivalent. Note, however, that the same thing is true of stored procs.
merrillaldrich
Thanks for your support. Please provide me the correct link if you get it. kIRAN
SQL DBA
Link is fine and it is working for me… MohammedU.
Moderator
SQL-Server-Performance.com
]]>