Can i use option(recompile) for Functions?

Question

Post reply

Can i use option(recompile) for Functions?

ram1447

SSC-Addicted

Points: 499
More actions
January 2, 2016 at 3:09 am

#310579

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 ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Ed Wagner SSC Guru Points: 287015 More actions · Answer 3

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.

How to Make Scalar UDFs Run Faster[/url]

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software