Rotating Header Image

Great EXCEPTations

(OK I fully understand how lame the title of this post is but hey it’s the best I could do so live with it)

I am guilty of favoring particular syntax over other options. This is mainly due to habit so every now and again I like to take some time and look at other ways to skin the cat. (PLEASE NOTE: No cats were actually skinned in the making of this post… that being said I hate cats and have pushed many cats off my lap at friends houses cause well it’s my lap and if I wanted your hair all over me I’d roll around on that gross pillow that you favor so dearly… I digress)

Let’s take two examples using our AdventureWorks Database and pull back all the ProductIDs that have never been sold using two different methods… the tried, tested, and true LEFT OUTER JOIN and an operator that came on the scene in SQL SERVER 2005…EXCEPT.

SELECT ProductID
FROM   Production.Product
EXCEPT
SELECT
ProductID
FROM   Sales.SalesOrderDetail

vs.

SELECT A.ProductID
FROM   Production.Product A LEFT OUTER JOIN
  
Sales.SalesOrderDetail B ON A.ProductID = B.ProductID
WHERE  B.ProductID IS NULL

EXCEPT Profiler Results:
 CPU – 0
 READS – 1132
 WRITES – 0
 Duration – 12

LEFT OUTER JOIN Profiler results:
 CPU – 110
 READS – 243
 WRITES – 0
 Duration – 111

So if we were just looking at this from a “who gets to the finish line first” point of view then the EXCEPT version of the query wins.  One thing to note though is that the reads are 4 times as much in the EXCEPT as they are in the LEFT OUTER JOIN example.  This is one of those situations where more reads actually equals better performance. 

Now this is NOT an arguement or endorsement in favor of EXCEPT over LEFT OUTER JOIN (or NOT IN, NOT EXISTS, etc) as different situations will call for you use different operators to get the best bang for your buck.  This is just another example on how two different methods to achieve the same goal can produce significantly different results (and execution plans as the EXCEPT did an INDEX SEEK where the LEFT OUTER JOIN did an INDEX SCAN)

CAUTION:  One “Gotcha” or at least something to take note of is that EXCEPT returns any distinct values from the left query that are not also found on the right query… take note of the word DISTINCT.

Anyways… if you haven’t looked into EXCEPT (and INTERSECT) check out the Books Online information on the arguments.

Enjoy!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

3 Comments

  1. Steve says:

    I have thought and used (sparingly) EXCEPT in some Dimension load ETLs.. “give me just the record from my source that aren’t in my destination so I can take the difference, and either update or insert into my destination” .. works well for smaller tables, for sure..

  2. There’s a couple of disadvantages to a properly researched grey import phone, although depending on your perspective they might not be particularly problematic.

  3. Google’s decision to partner with Taiwanese handset-maker HTC was a great move and HTC has the expertise to create a phone which not only creates value for consumers but also keeps high margins for the company.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.