Using CROSS APPLY instead of a calculated variable

3

May 22, 2013 by Kenneth Fisher

Earlier this week I posted The many uses of CROSS APPLY and I’m quite glad I did. I’ve been working on a series of audit queries for the last couple of weeks and got thrown a bit of a curve today. The original specs included several comparisons against a date. Basically the following.

WHERE (date1 >= '5/31/2008' AND date2 >= '5/31/2008')
	OR (date3 >= '5/31/2008' AND date4 >= '5/31/2008')

I was a little shocked when I was told several weeks into the project “Next month it will change to June 30 2008.” So in reality rather than a constant (used no less than 7 times throughout each of 13 views) I now needed a calculation.

Not really a huge issue. It’s a simple enough calculation and it would be simple enough to just copy and paste it everywhere it’s needed. I just really don’t want the repetitive code. Not to mention adding that much more complexity to the views. And I shudder to think of having to change it in over 90 different places if the business specs change. So the next thought I had was using a variable, unfortunately these have to be views, although I guess I could have used TVFs (table valued functions) but still not the best solution.

Now recently I watched Kendra Little’s 5 T-SQL Features You’re Missing Out On and learned that you could use CROSS APPLY for reusable calculations. Well that’s what this is right? A reusable calculation.

So now instead of looking like this: (The calculation is from one of Pinal Dave’s blogs, and the calculation is for September 2003. If you are reading this past May 2013 that will have changed.)

SELECT *
FROM Sales.SalesOrderHeader
WHERE (DueDate >= DATEADD(mm, DATEDIFF(mm,0,GETDATE())-116,0) 
			AND DueDate < DATEADD(mm, DATEDIFF(mm,0,GETDATE())-115,0))
   OR (ShipDate >= DATEADD(mm, DATEDIFF(mm,0,GETDATE())-116,0) 
			AND ShipDate < DATEADD(mm, DATEDIFF(mm,0,GETDATE())-115,0))

It now instead looks like this.

SELECT *
FROM Sales.SalesOrderHeader
CROSS APPLY (SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE())-116,0) AS StartDate, 
			DATEADD(mm, DATEDIFF(mm,0,GETDATE())-115,0) AS EndDate) AS Vars
WHERE (DueDate >= Vars.StartDate AND DueDate < Vars.EndDate)
   OR (ShipDate >= Vars.StartDate AND ShipDate < Vars.EndDate)

Personally I find this much easier to read. Of course if the performance isn’t there then it doesn’t really matter. Fortunately or unfortunately depending on how you look at it, all of my tests so far have been inconclusive. The query plans are very slightly different on some queries and the same on others. The run times are only slightly different and neither one runs faster every time. Until I see something conclusive one way or the other I’ll consider performance close enough with a big edge on readability and maintainability going to the CROSS APPLY.

3 thoughts on “Using CROSS APPLY instead of a calculated variable

  1. Mark Freeman says:

    A CTE might give you the same benefit and be even more clear to read and understand.

  2. It actually comes out to exactly the same execution plan (at least when I tried it). The only thing you have to remember when using the CTE is that you also have to do a CROSS JOIN to actually bring the results into the query. I’m not sure either one is better or worse, I think it just comes down to personal preference. Like a lot of other things in SQL Server.

  3. […] of my favorite features of CROSS APPLY is the ability to use it instead of a calculated variable. Well recently I was working on performance tuning a rather annoying query (which I will blog about […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,134 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: