October 25, 2007 at 6:17 am
Hi Guys,
I've been reading up about indexing as I do not know a lot about it. Even though lots of articles exist on the topic, none of them really have any "real life" scenario examples as every article indicates that every "scenario" is different. Is there any one willing to take some time and guide us through the process of indexing a database using an example. This might even make a good (even great) article. I have attached two scripts. One to create a 5 table schema and one to populate some data.
Table Descriptions:
- Customer: Stores customer related information. CustomerCode always unique and be used for searches.
- Product: Stores product related information. ProducCode always unique and used for searches.
- ActivityStatus: Activity Status Lookup. Searches will be based on the "ActivityStatusName" column.
- Allocation: Stores customer-product information.
- AllocationActivity: Stores status change activity for the particular customer-product allocation. Searches will be based on the Allocation link, AllocationStatus link and AllocationActivityDateTime.
As you can see it's a pretty basic "everyday" scenario. Good information to might be why a clustered index is used for the particular column rather than non-clustered, why is a "multi column" index used, maybe even why not to index certain columns etc...
Your article would be greatly appreciated 😀
October 25, 2007 at 6:25 am
Or if you know of a link with some examples please post them here too 😀
October 25, 2007 at 7:53 am
There's just not one right answer. The question you have to ask, in addition to the structure, how will the data be accessed?
For example, CustomerCode on your Customer table sounds like a likely primary key and a likely clustered index. But then I have to ask what data type is it? If it's varchar(500), then you may want to create an artificial key as an int or something for performance reasons.
Then you have to look at tables Like the Allocation table. Customer to Product many-to-many joins I assume. At first blush, this would want a compound primary key, clustered on the PK from Customer and the PK from Product. But, maybe the access pattern is to always include a begin and end date as part of the standard query. Then you might want to consider including those columns in the cluster.
It just goes on and on from there. But understanding how the tables will be queried can drive the indexing better than most anything else.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 26, 2007 at 4:27 am
The problem with writing an article on indexing, is that the 'best' indexes for a table depend on a large number of factors, and th table structure is probably the least important.
How is the data going to be accessed
- single rows or aggregates?
- Searching by name, id, both, other fields?
Amount of data in the table
Distribution of data. What values are common, in what columns
Ratio or reads to writes for the table
Frequency of inserts
Frequency of updates of each column
Indexing, even when you've been doing it a while, is as much trial and error as anything else (or at least it is for me).
Experience also has a lot to do with it. After a while, from the tables's structure, the queries and the rows affected and total, you can often come up with a good suggestion
You can find recomendations and best practices in a number of places. I will recommend SqlSkills as a good place to start
That's why the most common answer youll get when you ask what the best index is for a table is 'It depends'. Because it does
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply