Create a Temp table or have different join for each aggregation

• kjain

  Old Hand

  Points: 362

  More actions

  April 9, 2010 at 8:55 am

  #386672

  I have stored procedure that returns aggregated values. I need to different aggregation but there are about 5 table joins for the first aggregation, and one additional table joins for the second aggregation.

  What would be a better approach

  To create a Temp table and get all the data needed so the joins is happening only once and then use that temp table to do different aggregation

  Or

  Create aggregation and use joins in both the aggregation statements.

  Thanks

  Kjain

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  April 9, 2010 at 1:43 pm

  #1149344

  Not enough information to go on.

  In general, the less rows you have to touch the better. So if the last table is large and the result of your aggregation means you have to hit far fewer rows, that might be the better way to go. Emphasis on Might.

  You could provide a ton of information to us and we could probably make a very good guess, but the easiest thing would simply be to try it both ways and see.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  April 10, 2010 at 9:31 am

  #1149572

  kjain (4/9/2010)

  ---

  I have stored procedure that returns aggregated values. I need to different aggregation but there are about 5 table joins for the first aggregation, and one additional table joins for the second aggregation.

  What would be a better approach

  To create a Temp table and get all the data needed so the joins is happening only once and then use that temp table to do different aggregation

  Or

  Create aggregation and use joins in both the aggregation statements.

  Thanks

  Kjain

  In general... One of the major keys to performance is to "touch" each row as few times as possible and "pre-aggregation" in a Temp Table is certainly one of the ways to accomplish that. I've changed many a 45 minute report to run in only seconds using that method.

  However, I agree with Seth... there's not enough information to make anything other than a general recommendation here.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)