Think Set-Based

Paul Randal explains why you should think in a set-based manner when writing T-SQL queries:

This explanation involves a similar problem but not involving SQL Server. Imagine you need to acquire twelve 4′ x 8′ plywood sheets from your local home improvement store.

You could drive to and from the store twelve times, and each time you need to go into the store, purchase the sheet, and wait for a staff member to become available to load the sheet into your pickup truck, then drive home and unload the sheet.

Or you could drive to the store once and purchase all twelve sheets in one go, with maybe four staff members making three trips each out to your pickup, carrying one sheet each time. Or even just one staff member making twelve trips out to your pickup.

Which method is more efficient? Multiple trips to the store or one trip to the store, no matter how many staff members are available to carry the sheets out?

On the SQL Server side, the APPLY operator is a method for bridging set-based operations with procedural thought patterns.  It’s not the perfect answer for everything, but there are some fantastic use cases (like simplifying calculations via chained APPLY operators).

Related Posts

Units Of Measure In The ShowPlan Schema

Grant Fritchey shows off the ShowPlan Schema: Because the showplan schema contains notes throughout stating what the units of measure are, what each of the values means. For instance, I can explain why SerialDesiredMemory, DesiredMemory, RequestedMemory are identical: …Provide memory grant estimate as well as actual runtime memory grant information. Serial required/desired memory attributes are […]

Read More

When AT TIME ZONE Is Too Slow

Robert Davis troubleshoots a performance problem relating to time zones: Time Zones were definitely being a drag today. I got an email from one of the developers at work asking about the performance difference between 2 queries. The only difference between the 2 queries is that one of them uses the AT TIME ZONE clause that was […]

Read More

Categories

May 2016
MTWTFSS
« Apr Jun »
 1
2345678
9101112131415
16171819202122
23242526272829
3031