APPLY - Your New Best Friend
sqlsorcery.com/apply-your-new-best-friend/Posted Oct 30, 2019, 6:34 AM UTC
2825
sqlsorcery.com
Cool article, explained really well imo. I've bookmarked your blog, looking forward to more of these!
2
Excellent post. Apply is one if the most underused predicates in SQL.
2
How is apply a predicate?
2
It would be interesting to see a performance run down of using an apply compared to other techiques. For example a couple of our devs love doing sub queries projecting the row_Number then an outer where clause that ties to the first record which causes a rather nasty spool in the plan. How this compares in different situations to a left anti-join and an apply would be very interesting and a potential topic for a future blog post if you feel inspired.
Well written for a first article.
2
Hey, I totally agree! I intended to show the performance differences in the second example, but I then realized that you can't get the query plan in Azure Data Studio. So I need to install SSMS, and I just ran out of time.
Anyways, I did check the STATISTICS IO and noted that the GROUP BY/ROW_NUMBER query had a work table and a lot more logical reads than the APPLY query.
Thanks for reading!
2
As per what u/Thriven wrote this could be quite a performance pitfall.
I'd be wary about calling this a new best friend until we see what happens with lots of data. Thinking about this further there could be a reason not many people use APPLY like this. I've only personally used it for functions such as when dealing with XML and didnt really even think to use it like this.
I just ran out of time
So could the webserver timeout once the users start clicking hard enough:)
Get SSMS/SentryOne plan explorer going and generate a load of data and see what happens, if i get time tomorrow I might.
If you seriously want to get your blog going then what tends to impress proper SQL people (not just the ORM 'fullstack' crowd) is performance rundowns of different techniques with good sized sample data. Performance should always be a high priority as thats where the major pitfalls and challenges pop up.
1
Duly noted.
1
I don't want to be a downer but essentially what you get is a correlated sub query.
I mean your comments literally say
--we can write a table valued expression here that will execute once
--for each "input" row in SalesLt.ProductCategory
For small tables this code is one developers means to an end. It gets the job done. In fact, SQLserver optimizer will evaluate and see if they can take the table in the outer apply, run it once and then simply join the results.
When it doesn't though on a very large dataset could be suspect for performance issues. As u/therealcreamCHEESUS stated, I'd like to see the performance of this on larger datasets as I would normally write this in a different way to avoid the nTh amount of executions across a large dataset.
2
I don't want to be a downer but essentially what you get is a correlated sub query.
I didnt have time to test this today at work when I wrote that comment and made a mental note to come back later to run a few tests but your suspicion that performance would fall off on larger datasets sounds likely.
Thats the problem with sql and the challenge to writing it well. Many styles and techniques work fine on small amounts of data in dev. But when the users keep clicking on stuff as they do and your mdf files are thousands of percent larger than the initial dev database was that's when you discover what actually works and what does not.
1
I suspect you are right as well, but in some cases this type of query can't be avoided. There may be a better approach such as precomputing the aggregates in a temp table, but it's situation dependent.
The point of the post was to showcase how APPLY can be used, not to make a judgment on performance.
I do agree with you though.
1
Nice. I really like the comparisons you made between group by and apply: group by works but apply is cleaner and more robust in the example.
1
Great article 👍
1
The information looks solid, I'm glad you touched on using Apply with functions, that seems to fly under the radar sometimes when I talk to people. The site looks clean, professional, and it was informative! I really like the formatting, I'm a little jealous to be honest.
What I'd like to see would be a quick way to navigate your posts as you accumulate more articles. I'd also like a way to copy the code down so I can easily do the tests myself. There is a plugin called Crayon that works fine, I don't know of a better alternative without creating my own. I also personally spring for Snagit to create screenshots. It's a little costly (I think I paid ~60 and then I get yearly updates for ~$15) but it has saved me SO MUCH TIME. It was worth spending 2-4 hours of effort learning it better.
1
Thanks for the encouragement!
Regarding the site itself, I am brand new to WordPress. I have a vision for how the site should work, but translating that to WordPress within the confines of my chosen wp theme is really hard. So, expect that to change for the better as time goes on.
The code being screenshots sucks, I agree. I couldn't get syntax highlighting to work. I'll check out Crayon!
2
My site is also WordPress and I think you do a better job at it than I do, I've been failing at it for about 4 years now. I completely understand the difficulties you're experiencing, but it gets better.
When you have time, make sure to set up backups and plug it into Google Analytics. Setting up your own email domain is also rewarding, but I lost some sanity configuring that. Otherwise I look forward to following your site and reading your content. Keep it up!
1
Will do, thanks for the pointers. I really do appreciate it.
2
Also - check out Greenshot for screengrabs!
2
After some googling, it looks pretty good. I'd say it probably has 85% to 95% of the functionality of snagit but is free. Definitely going to keep this in mind when next renewal comes to, thanks!
1
Thank you for this! You gave me some ideas
1
Great article. I love APPLY, but, I'd never thought about using it in place of a long GROUP BY list. Thank you!
1
One further use of cross apply which is not as great as your group by substitute is to use it when you have an expression in the select that uses the result of another expression elsewhere in the select, i.e. to avoid copy-pastes of expressions. I use cross applies that just select the expressions needed without from clauses then refer to the results in the select instead of putting the expressions in it directly.
1
What do you mean "new"? I use APPLY all the damn time. ;)
Another tip: like any other query, the subquery inside APPLY doesn't have to have a FROM clause, meaning you can use it to define calculated columns in one place and reference them from the SELECT, WHERE, GROUP BY, etc. clauses without specifying the same calculation multiple times.
SELECT calc.GrossProfit
FROM Sales
CROSS APPLY (SELECT Sales.Price - Sales.Cost) AS calc
WHERE calc.GrossProfit > 2000
1
Now I've learned something new! So many wasted lines of code wrapping my calculations in an outer query...
1
Granted, that was something of a poor example, since "Price - Cost >" wouldn't be a SARGable filter, but it gets the idea across anyway. You're more likely to do that in SELECT (e.g. multiple calculated fields/subqueries derived from the same underlying calculation), or in WHERE along with other SARGable expressions.
1
Hello there! I've decided to start a SQL blog. For my first post I am tackling APPLY. Enjoy!