January 2, 2016 at 3:09 am
Hi All,
I need your help to clarify the compilation of SP and Function.
My understand is SQL stored procedure gets compiled during the first execution ( but not during the creation, during the creation it checks the syntax and create the object and makes an entry in sys tables sysobjects, syscomments etc) and it saves the execution plan in the cache so that for the further execution it will use that plan (provided we are not forcing any recompilation) But in case of Functions it get compiled for each call.
Is my understanding correct?, In some article I read both SPs and Functions compiles only once and it will use that plan for the further execution if we are not recompiling it, but some other article says as I mentioned above ( ie Function get recompiled in each call).
Can some one let me know which is correct ?.
Also, do we have WITH RECOMPILE option for function? as we have it in Procedure ?
January 2, 2016 at 7:17 am
I am going to address what could be a root flaw here WAY more important than (re)compilations: if you are using Scalar or Multi-Statement UDFs THEY MUST GO!!! There is almost no exception to this guidance. They are HORRIBLY BAD in MANY ways!!
Please get a copy of my "Death by UDF" chapter in the SQL Server MVP Deep Dives 2 book (proceeds to charity). 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 2, 2016 at 8:00 am
Kevin is correct. If you look in "Books Online", you'll find that there are 3 types of functions. Scalar Function, Multi-statement Table Valued Function (mTVF), and Inline Table Valued Functions (iTVFs). The first two are terrible for both performance and resource usage. The iTVF works more like a parameterized view and (depending on how the code is written, of course) is as fast as if the code were written "inline".
What do you do if you need a Scalar Function? Simple... write it as an "iSF" (Inline Scalar Function). It's used differently that a Scalar Function but it's worth it for reduced CPU and increased performance. Please see the following article.
How to Make Scalar UDFs Run Faster[/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2016 at 8:34 am
Jeff Moden (1/2/2016)
Kevin is correct. If you look in "Books Online", you'll find that there are 3 types of functions. Scalar Function, Multi-statement Table Valued Function (mTVF), and Inline Table Valued Functions (iTVFs). The first two are terrible for both performance and resource usage. The iTVF works more like a parameterized view and (depending on how the code is written, of course) is as fast as if the code were written "inline".What do you do if you need a Scalar Function? Simple... write it as an "iSF" (Inline Scalar Function). It's used differently that a Scalar Function but it's worth it for reduced CPU and increased performance. Please see the following article.
Piling on, ITVFs are definitely the way to go. In a recent redesign of an ETL system, the previous author had an MTVF to calculate a custom checksum for a given string of numbers. There was a loop over the characters and several checks involved. I converted it to an ITVF using cascading CTEs and achieved a 93.8% reduction in run time over a 1M row test table. They're definitely worth it.
January 2, 2016 at 8:44 am
The biggest issue for multi-statement table valued functions is that, regardless of recompile, since they don't have statistics and are always based off of 100 rows (in 2014 using the new cardinality estimator), or 1 row (pre-2014 cardinality estimator), you're always going to get the same plan anyway, so the recompile is a waste of time.
I'm with Jeff & Kevin. Chuck these things.
"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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply