Geek Answers Handbook

Two-column indexes for a group

I have a table with a high pace (~ 160 million rows) #itemsTemp

 itemId | style | styleWeight -------------------------------- int | smallint | float(53)

and the following query:

 select itemId, style, SUM(styleWeight) itemCount from #itemsTemp group by itemId,style

#itemsTemp currently has no indexes. I am a bit confused about what would be best here:

  • A composite index on itemId and style (and probably include styleWeight)
  • Separate indexes on itemId and style

Which direction should I go? What for? Any other options?

+4
source share
3 answers

A component index on itemId and style with styleWeight enabled styleWeight be a better option.

This will allow Stream Aggregate without sorting and / or clustering search / RID searches.

+4
source

SQL Server 2008 actually offers missing indexes if you include an actual execution plan . Database tuning tool offers you indexes.

However, the optimal indexes depend on other queries executed with this table :

  • The Evert index element that you add to the table has both a storage penalty and a write performance penalty, so if you write to this table, you want the number of indexes to be low enough to ensure write performance is acceptable.
  • If many other queries use the same 2 columns, you can use a composite index if these queries can use this index (remember that the order of the composite index matters).
  • Conversely, if other queries cannot use a composite index, it may be better to use two separate indexes - performance may be lower for this query, however it may cost overall if reusing the index reduces the number of indexes in this table.

In fact, the index suggestion function tends to work very well - I usually do what it offers (after a quick check / health check), and then just run a few simple tests to make sure the query is really executed with the new index ( s).

+3
source

In addition to evaluating performance in both directions (manually), you can use query optimization tips - for example: http://msdn.microsoft.com/en-us/library/ms181714.aspx .

Also, if your temporary table is so large, I wonder if there is a better way to solve the problem than using a temporary table.

Also - how often do you write or read? How long does the session take? Do you make this available to other procedures?

+1
source

More articles:


All Articles