Stored PRocedures VS User Defined Functions | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Stored PRocedures VS User Defined Functions

I found this snippet on the site<br /><br />"rewriting stored procedures as functions will not give you any performance benefits, and quite possibly, they may cause a performance hit because of the extra overhead they incur as compared to stored procedures. The amount of the performance hit, if any, will depend on how the function is written and what it is doing. Keep in mind that user-defined functions are also pre-optimized and compiled similarly to stored procedures (unlike what you have been told). Even so, they have more overhead than corresponding stored procedures."<br /><br />I need to wrap the following lookup logic in either a SPROC or UDF:<br /> it needs to take @IATACODE(char(3))<br />and return @ID(int)<br />The Table contains ~1000 possible members<br /><br />SELECT @ID=ID<br />FROM dbo.IATACodes<br />WHERE [email protected]<br /><br />I found that the query plans were different for the two alternatives<br />for the SPROC I get a Clustered Index Scan(I/O cost: .0375,CPU cost: .00016<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />for the UDF I get a Constant Scan(I/O Cost: 0.000000 CPU cost: .000001)<br /><br />This would seem to contradict the above quote from the site however I think there may be something else going on here<br /><br />stylisticly I prefer the UDF<br /><br />The SPROC or UDF will most likly be executed over 1 million times so it needs to be as fast as possible.<br /><br />any ideas of comments?<br /><br /><br />
I don’t think the quote was meant to be absolutely in favour of SPs. IMHO performance differences will exist between SPs and UDFs (either way) and this will depend mostly on what you are trying to accomplish and the amount of data in question. The wise thing to do is test and verify whatever logic you are tryig to implement. There are be no hard and fast rules in matters like this – only general guidelines.
  • Repeat the UDF vs. SP battle with more data and check out if there is a difference again.
  • Test the performance of the major query that uses these two implementations and note any differences before deciding

Nathan H.O.