Functions vs Stored Procs

Question

Functions vs Stored Procs

ImYourDBA

SSCertifiable

Points: 5654
More actions
November 12, 2014 at 2:50 pm

#298346

So I inherited this server that has a ton of UDF's written as simple select statements (at least one incoming parameter like start date and then return table with results...SELECT {colums} FROM joins WHERE @StartDate) and that's it. Obviously something you would typically see in a stored proc, not a function. And they are only used as a data source in an SSIS package that then writes the results to another table elsewhere.
So aside from the obvious "um, cause it's the wrong way to do it" type answers...can anyone point me to an article or any prior testing or pitfalls that they know of that can help me persuade the developers to recode these as stored procs instead of functions...and more importantly why? I need to be able to say "functions don't provide the best performance for this type of scenario because....". And these are massive data sets by the way, like 400M records.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Viewing 7 posts - 1 through 6 (of 6 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

I didn't write the book on UDFs and how bad they are, but I did write the chapter! 😎

SQL Server MVP Deep Dives 2 book, "Death by UDF". They are just DEVASTATINGLY BAD!! So many ways scalar and multi-statement-TVFs can harm you!

I also presented on this topic at SQL Rally Dallas, DevLink 2013 and numerous SQL Saturdays. You should be able to download my deck and samples from one or more of those sources.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

I immediately thought of your chapter in that book Kevin.

I agree 1000% that you should try to avoid scalar UDFs but when you are talking about 400M row result sets on a regular basis performance is going to be rough no matter what you do.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 3

SQLJocky (11/12/2014)
So I inherited this server that has a ton of UDF's written as simple select statements (at least one incoming parameter like start date and then return table with results...SELECT {colums} FROM joins WHERE @StartDate) and that's it. Obviously something you would typically see in a stored proc, not a function. And they are only used as a data source in an SSIS package that then writes the results to another table elsewhere.
So aside from the obvious "um, cause it's the wrong way to do it" type answers...can anyone point me to an article or any prior testing or pitfalls that they know of that can help me persuade the developers to recode these as stored procs instead of functions...and more importantly why? I need to be able to say "functions don't provide the best performance for this type of scenario because....". And these are massive data sets by the way, like 400M records.

Actually, there might not be anything wrong here. In fact, it might be the right thing to do. If the UDF's are written as iTVFs (Inline table valued functions), they can be just as effective as stored procedures but easier to use because they can be joined via a CROSS APPLY much like a view can be except the iTVFs can be controlled by criteria in the parameters.

If, however, the UDFs have the word BEGIN in them, then they're NOT iTVFs and can cause a performance problem although I'll remind folks that "It Depends".

--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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Jeff Moden (11/12/2014)
SQLJocky (11/12/2014)
So I inherited this server that has a ton of UDF's written as simple select statements (at least one incoming parameter like start date and then return table with results...SELECT {colums} FROM joins WHERE @StartDate) and that's it. Obviously something you would typically see in a stored proc, not a function. And they are only used as a data source in an SSIS package that then writes the results to another table elsewhere.
So aside from the obvious "um, cause it's the wrong way to do it" type answers...can anyone point me to an article or any prior testing or pitfalls that they know of that can help me persuade the developers to recode these as stored procs instead of functions...and more importantly why? I need to be able to say "functions don't provide the best performance for this type of scenario because....". And these are massive data sets by the way, like 400M records.
Actually, there might not be anything wrong here. In fact, it might be the right thing to do. If the UDF's are written as iTVFs (Inline table valued functions), they can be just as effective as stored procedures but easier to use because they can be joined via a CROSS APPLY much like a view can be except the iTVFs can be controlled by criteria in the parameters.
If, however, the UDFs have the word BEGIN in them, then they're NOT iTVFs and can cause a performance problem although I'll remind folks that "It Depends".

Quite true Jeff. Everyone who wants top-notch performance from their data warehouses should review Adam Machanic's Parallelism stuff to see how incredibly powerful APPLY can be!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 5

Awesome, thanks for all the replies. I'll definitely go take a look.

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Grant Fritchey SSC Guru Points: 398696 More actions · Answer 6

My query performance tuning book also talks about the dangers of multi-statement table valued user defined functions. The newest one has the most discussion around that.

"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