Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

summarize vs addcolumn

Hello,
The following two queries give the same result.
Which is the best to use and why?
I feel like summarize is simpler for me to use.

Thank you

 

ADDCOLUMNS (
GROUPBY (
'TableX'
,'TableO'[field1]
,'tableY'[field3]
)
,
"duration", 'TableX'[field5]
)

////////////////////////////////////

SUMMARIZE(
'TableX'
,'TableO'[field1]
,'tableY'[field3]
, "duration", 'TableX'[field5]

  • marcorusso's avatar
    marcorusso
    6 years ago

    In reality this would the the best one:

    SUMMARIZECOLUMNS (
    TableO[field1],
    tableY'[field3],
    "duration", [field5]
)

    And if you cannot use SUMMARIZECOLUMNS then:

    ADDCOLUMNS (
    SUMMARIZE (
        TableX,
        TableO[field1],
        tableY[field3]
    ),
    "duration", [field5]
)

2 Replies

  • marcorusso's avatar
    marcorusso
    Most Valuable Professional
    6 years ago

    In reality this would the the best one:

    SUMMARIZECOLUMNS (
    TableO[field1],
    tableY'[field3],
    "duration", [field5]
)

    And if you cannot use SUMMARIZECOLUMNS then:

    ADDCOLUMNS (
    SUMMARIZE (
        TableX,
        TableO[field1],
        tableY[field3]
    ),
    "duration", [field5]
)
  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    Here is a good blog by marcorusso  who is the guy to ask!

     

    His conclusion is below.  There are a ton of books and blogs to answser other questions like this.

     

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

     

    The conclusion is that extended columns in a SUMMARIZE expression should not be moved out to an ADDCOLUMNS if the table used in SUMMARIZE has particular filters and the extended column expression uses columns that are not part of the output. Even though you can create an equivalent ADDCOLUMNS query, the result is much more complex and there are no performance benefits in this refactoring. The more complex query has the exact same (not so good) performance as the SUMMARIZE query – both queries in this section require almost 20 seconds to run on Adventure Works 2012 Tabular.