Post reply

do Distinct, Group By, Order By & Union (without All)

  • May 8, 2013 at 7:05 pm

    #277362

    all take the same hit on Sort?

    Is there any way to mitigate that or conditions that make one cheaper than another forcing uniqueness?

    thanks very much

    drew

  • May 8, 2013 at 9:36 pm

    #1613595

    Hi,

    This resource, Logical Query Processing , from Itzik Ben-Gan's book will help you understand what is going on behind the scenes.

    But if you want to know for sure then run your particular scenario with the execution plan on and compare results.

    Regards,

    Bevan Keighley

  • May 9, 2013 at 4:43 am

    #1613701

    drew.georgopulos (5/8/2013)

    all take the same hit on Sort?

    Is there any way to mitigate that or conditions that make one cheaper than another forcing uniqueness?

    thanks very much

    drew

    I don't think there is any alternative(other than mentioned) way to get unique data.

  • May 9, 2013 at 6:05 am

    #1613746

    thanks very much...the execution plan is what informed me about where the query was so costly, but i was surprised to find that all the alternatives boiled down to 96% spent on Sort, (divided sometimes in different branches depending on where i put which operator(s)) but Sort was always the elephant in the room!

    that was a great diagram, i learned that ages ago but never saw it so cleanly diagrammed, so thanks a lot for pointing it out.

    drew

  • May 9, 2013 at 6:52 am

    #1613768

    suneet.mlvy (5/9/2013)

    drew.georgopulos (5/8/2013)

    all take the same hit on Sort?

    Is there any way to mitigate that or conditions that make one cheaper than another forcing uniqueness?

    thanks very much

    drew

    I don't think there is any alternative(other than mentioned) way to get unique data.

    It depends upon the shape of the data. If there are relatively few distinct values in the set, then Paul White's super-fast distinct, which uses seeks instead of sorts, can execute many times faster than native DISTINCT.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • May 9, 2013 at 2:50 pm

    #1613978

    Holy Good Night!

    I never would have thought of that, and it is breathtaking!

    Thanks so much for telling me about it, I really appreciate it.

    drew

Viewing 6 posts - 1 through 5 (of 5 total)

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