|
|
Subject:
Choosing Table Index in SQL Server
Category: Computers > Programming Asked by: mandeeparora-ga List Price: $50.00 |
Posted:
05 Mar 2006 15:21 PST
Expires: 04 Apr 2006 16:21 PDT Question ID: 703971 |
Choosing the right Indexing scheme (Sql Server 2000) I have a large table (2 Million+ Rows) with 5 Columns and I am trying to come up with the correct indexing scheme for the table. As the table is large and also frequently used the indexing on this table is crucial to overall performance. Size of index must be weighed against speed of lookups as well. Here is the layout of the table which contains Sales history Columns -------------------------- ID (Autonumber) StoreID (Store at which product was sold) ShelfID (Shelf from which product was sold) UPC (Item which was sold) Sales (Number of items sold) UnitPrice (Price at which item was sold) SaleDate (Date & Time of sale) There is an index on the ID field which SQL Server creates automatically as ID is the primary key. Lookups are performed most often on StoreID or on a combination of StoreID & ShelfID. For example Select SUM(Sales) Where StoreID = 25 and ShelfID = 1 AND SaleDate > '1/1/06' or Select SUM(Sales) Where StoreID = 25 AND SaleDate > '1/1/06' Once in a while queries are executed in other ways (based on UPC) Select UPC, SUM(Sales) Where SaleDate > '1/1/06' GROUP BY UPC Queries like these are used much less frequently. It would seem to me that the ideal index is a clustered index on StoreID or a clustered index on the combination of ShelfID and StoreID, or perhaps both. I am looking for an analysis of what the cost/benefits are of these approaches and if there are other approaches that should be considered. |
|
There is no answer at this time. |
|
Subject:
Re: Choosing Table Index in SQL Server
From: nikindia-ga on 07 Mar 2006 01:48 PST |
In my opinion, it is better that you should have separate indexes for each of these fields, StoreID ShelfID and SalesDate. As whatever combination you will try in the where clause, the index will maximize performance. |
Subject:
Re: Choosing Table Index in SQL Server
From: nicklp-ga on 12 Mar 2006 07:13 PST |
From the informaton that you have given I wouls suggest that you have the Clustered index on 2 fields StoreID,ShelfID as this is the fastest lookup and cal be used when StoreID is looked up or a combination of StoreID and ShelfID. The index on the primary key can be left as it is. Adding more indexes will speed up the other queries, but adding more than 4ish will start to slowdown the updates and inserts, so these should be put on the most commonly used fields in queries. If shelfID is used in queries without StoredID, put one on it, Then maybe one of UPC or SaleDate. If you can send the database I can tell you the the best index combination, but as I said it depends on how often they arew used in the queries. There are tools in Enterprise manager to recomend index which work quite well. You run the profiler which saves all the SQL done while your system is running then the index tuner to recomend then create the indexes. This works very well, but is not perfect. |
If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you. |
Search Google Answers for |
Google Home - Answers FAQ - Terms of Service - Privacy Policy |