Indexing for Group By Clause

• Knowledge Hunter

  SSCrazy

  Points: 2287

  More actions

  July 12, 2011 at 10:43 am

  #243886

  Hi,

  I have a Query having Group By clause on multiple fields.

  SELECT

  Col1, Col2, Col3, Col4, …..

  From Table1 A

  Join Table2 B

  ON A.ColA = B.ColB

  WHERE Col2 = <>

  GROUP BY

  Col5,

  Col6,

  Col7

  ORDER BY Col 8

  Both the tables have no index. On checking the Execution plan I found that 72% of the resource is consumed by Sort task and 22% is used for Table2 scan.

  Please suggest what will be appropriate clustered and nonclustered index for the tables.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 12, 2011 at 10:52 am

  #1351775

  post the actual execution plan you looked at and let us peer review it with you;

  trying to guess at performance from pseudo code won't get much other than guesses.

  what are the actual indexes on the table now? adding included columns might be an option.

  note that the example you changed to pseudocode wouldn't be right, since the columns selected, unless they are min/maxed or otherwise aggregated, must exist in the group by.

  so cols 1 thru 4 better be in the group by , along with those others 5 thru 8....

  jjust one more additional reason to see a real execution paln.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  July 12, 2011 at 2:12 pm

  #1351874

  It were only a single table, you could try an index over the three columns you are doing your GROUP BY on. In your pseudocode example, that would be Col5, Col6, and Col7. Keep the same sequence as your GROUP BY. You would also INCLUDE all the columns that will be used in the SELECT or WHERE clauses.

  You don't really give us any information about what columns are used to join the two tables so the above suggestion may be totally worthless. You might consider an indexed view over the two tables if you are gong to run the query often and response time is critical. Otherwise, live with the fact that a sort following the join is necessary.

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills