A Little About Function Rewrites In SQL Server

Become a member! www.youtube.co...
Click here for 50% off a health check: training.erikd...
If you like what you see here, you'll love my advanced performance tuning training:
training.erikd...

[@plancktime9750]

Thank you for the free content

If you are going to use it several times in the result, won't putting it in the select list run it several times per row but maybe not if it is in the outer apply?
Also the use cases where you can't put the shared logic in a view and just join to it property are not that many.
I think it may just be easier to tell people never to put more then 1 select above the from clause, because they will end up putting 5 sub-selects in there that could all have been joins on an poorly filtered query...

[@ErikDarlingData]

Why would I tell people something wrong? That seems like a bad idea for credibility. 🫢

[@douglascoats7081]

I was going to give you another epitaph and then you beat me too it. :|

[@ErikDarlingData]

I’ll take what I can get.

[@douglascoats7081]

@@ErikDarlingData Sure thing, lol
"Erik 'Ill Take What I Can Get' Darling"

[@dforck42]

i typically avoid correlated subqueries (that's what it's called when you have a sub-select in a select statement because it runs for each row returned from the parent statement) cause they tend to cause a lot of performance issues, typically i've seen better performance writing it as a subquery

[@ErikDarlingData]

Where do you get this stuff from. Seriously. I want to hunt it down.

[@dforck42]

@@ErikDarlingData mostly from personal experience, but i'm willing to have my opinion changed

[@dforck42]

it's also possible what i saw was an issue with a specific version, a job i had years ago someone loved writing correlated subqueries and they always caused performance issues, ended up rewriting a bunch of them to do proper joins

[@ErikDarlingData]

Okay, let’s start with your example: if you put this in cross apply, which is also primarily optimized as nested loops, how is that not “once per row” execution? Worse, if the cross apply is pushed deeper into the plan, it would once per a lot more rows.

[@dforck42]

@@ErikDarlingData i'd have to play the specifics, but glancing through: join users to posts, filter users on start date and reputation, group by user id, figure out avg posts per day (looks like it'd be something like min(p.CreationDate) / sum(Distinct postID)
then take that sub query and join it to the rest of the stuff

[@mjswart73]

I dream of retiring and spending as much time playing pickleball as my knees will let me.

[@ErikDarlingData]

I think it’s harder on the ears than the knees.

[@FlaggedStar]

6:52 It's because they forgot that it's valid syntax. It's super rare that it ever comes up. It's like those JOINs that put their ON clause after five other JOINs.

[@ErikDarlingData]

Is it that rare?