Post reply

Aggregate Performance Question

  • May 30, 2018 at 9:41 am

    #368609

    Which approach should I generally take? In this example, I'm keeping it simple, but imagine 20 calculated columns in which I keep summing the same values repeatedly, just in different combinations.  Or what if I have 10 when lines in my case, where I keep using the same sum values?

    Method One

    select a, b = sum(b), c = case when sum(z) < sum(b) then 'Yes' from Table group by a

    Method Two

    select a, b, c = case when z < b then 'Yes'
    from (
    select a, b = sum(b), z = sum(z) from Table group by a
    ) x

  • May 30, 2018 at 11:19 am

    #1992356

    Use method two.  You could also use a view or a cte to define the SUMs to use in other calcs.

    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".

  • May 30, 2018 at 11:32 am

    #1992358

    Thanks.

  • May 30, 2018 at 11:50 am

    #1992364

    There shouldn't be any performance difference, but the second method might reduce the length of the code by not having to repeat it.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 4 posts - 1 through 3 (of 3 total)

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