Google Answers Logo
View Question
 
Q: Choosing Table Index in SQL Server ( No Answer,   2 Comments )
Question  
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.
Answer  
There is no answer at this time.

Comments  
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.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

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 Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy