Post reply

Check that a specific Group By condition does not exist

  • September 11, 2012 at 1:06 pm

    #1536441

    Lynn Pettis (9/11/2012)

    Just a quick test using 6 million rows:

    Lynn's method:

    Elapsed Time (ms) 8276

    ScottPletcher's first method:

    Warning: Null value is eliminated by an aggregate or other SET operation.

    Elapsed Time (ms) 12244

    ScottPletcher's second method:

    Elapsed Time (ms) 7464

    I ran this several times with very similar results. The third method on 6 million rows is about 1 second faster.

    Interesting.

    Given that I would never anticipate anything close to that number of TemplateIDs in a real world situation, I still prefer my first method, since the entire concept of what is being queried is expressed in the HAVING clause alone, so that query is self-documenting in that sense.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing post 31 (of 30 total)

You must be logged in to reply to this topic. Login to reply