Bad Scalar Functions (or other UDF)

Question

Post reply

Bad Scalar Functions (or other UDF)

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
May 18, 2016 at 8:45 pm

#327159

If you can share a story of a real scalar or multi-statement table UDF and how you fixed it, I'd like articles here. Even if the fix is changing application code.
The idea is to help people understand the issues with functions and performance by providing concrete examples.

Viewing 12 posts - 1 through 11 (of 11 total)

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

pietlinden SSC Guru Points: 63502 More actions · Answer 1

Steve,

See if Kevin Boles has any horror stories that aren't confidential. =)

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 2

Kevin doesn't like writing. I need someone that wants to document and describe an issue.

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

Steve Jones - SSC Editor (5/19/2016)
I need someone that wants to document and describe an issue.

I've recently fixed a monster that was necessarily (used in a Persisted Computed Column) a Scalar Function and I started to write about it.

Don't let this hold anyone else up, though. This sounds like a very wide open topic that a whole lot of people could have fun with.

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

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 4

Thanks, Jeff. Hoping for a few pieces. We have the one you wrote on moving a scalar UDF to a CROSS APPLY, but I'd like to get more references for specific functions that might do strange things.

sqltung Hall of Fame Points: 3962 More actions · Answer 5

I recently found one or two issues at work with scalar functions. I would be happy to contribute an article with examples.

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 6

sqltung (7/22/2016)
I recently found one or two issues at work with scalar functions. I would be happy to contribute an article with examples.

That would be great. IF you can do the setup, the bad performance, and how you fixed things, showing better performance, excellent. If this is 3 pages for each, then make it two articles.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

Steve Jones - SSC Editor - Friday, May 20, 2016 10:18 AM
Thanks, Jeff. Hoping for a few pieces. We have the one you wrote on moving a scalar UDF to a CROSS APPLY, but I'd like to get more references for specific functions that might do strange things.

My sincere apologies. I'm way overdue on this and other articles (it's been several years since I put one out there). Hopefully, things will lighten up on my end and I'll be able to crank the one I was thinking of for this out soon.

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

Brahmanand Shukla SSC Eights! Points: 990 More actions · Answer 8

Hi Jeff & Steve,

I wrote an article in 2016 on "Work around to scalar functions". I've mentioned 3 workarounds with example.

This was my first article! I hope you would find it relevant on this thread!

This reply was modified 4 years, 7 months ago by Brahmanand Shukla. Reason: Resolved typos

SQL Server Carpenter - Consultant & Trainer
Author of Learn T-SQL from Scratch

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 9

Thanks, but these threads are not asking for help. They are asking for authors to write articles for this site.

ChrisJenkins Grasshopper Points: 23 More actions · Answer 10

Hi Steve. Are you still looking for articles on this?

I've got an issue from a few years ago when someone used a lot of scalar functions in completely the wrong way for a system "upgrade". The system ground to a halt within a few days even though it was only new data being added. I fixed it within a couple of days with a variety of different solutions, including doing some of the hard work in instead of insert triggers to manipulate the data on the way in rather than always having to transform it at query time.

Anyway, if you are after something I can write that up.

Thanks,

Chris

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 11

Absolutely. The more specific examples, the more people learn to avoid the pattern