| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2009-07-16 : 18:47:52
|
| Hi,I just ran the index wizard on one of our databases and I'm a little surprised by one of the recommendations but I could also see it making a lot of sense... I just don't know.We have a fairly large table that has about 20 columns (with a few million records). 10 of those columns make up a compound primary key. The recommendation is to create a non-clustered index with 9 of the primary key values. The order of the index varies than the primary key order.So is the recommendation due to:1) The altered key order is more efficient so varying the column order in the index will be more efficient2) Having an index of just the key data (and no other data), makes the retrieval process more efficient (since you don't have the other non key data, it will be faster).3) Creating an index by excluding one of the primary keys (which rarely varies between records) is more efficient than the normal clustered index4) All of the above The problem is with solution one, the is a small set of profiler data. In the end all columns are pretty unique so over a large amount of queries, the order will vary with all of them. Making an index with a varying order only helps in a subset of those queries. Taken to an extreme, we'd need indexes for all the various orders...So if the answer is primarily due to #1, then I probably won't add the index. But if #2 or #3 plays a role, then I can see value in the index. But from my understanding that is what a clustered index is. Why would it be good to add a non clustered index which is essentially the same thing as the clustered index?I know the answer really just depends but is my reasoning correct? If the value of the index is primarily associated with the order, then in reality it's probably not that helpful (given this scenario), but this is a commonly used table so I just don't want to ignore the suggestion.Thanks,NicNic |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-17 : 00:27:07
|
| any time i see a table with that many fields in the pk, i see a system that wasn't properly normalized to begin with. anyway...are you experiencing performance problems? |
 |
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2009-07-17 : 13:53:14
|
| Actually this is necessary. This table stores vehicle information (we write software for the insurance industry). The first 10 digits of a vehicles VIN (each car has a 17 digit vin) is the unique identifier for that car (year/make/model). Unfortunately many of the digits also contain wildcard values so we need each VIN value to be a unique column for proper searching of this table. I agree normally a 10 digit compound key would be odd but for our purposes it is required (based on the VIN data structure).Any suggestions on my original question?Nic |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-17 : 14:23:40
|
| well it boils down to how the data is searched. i aksed if you're having performance problems, because if you aren't then i wouldn't change things except in terms of optimization for best practices.by the way, this design may be a mistake if you search on one specific column as in some cases it won't be selective enough to utilize a seek. and if you pass in the entire vin, then it's definitely a mistake. i also work in the automotive industry and we store vin in a single field. when searching by vin, we pass in the whole vin. very fast. when searching by year/make/model we pass those in -- we have an application lookup table, indexed over make/model/yearanyway...- if you are searching a single column, index it.- you're PK will be much smaller by adding an identity column as a surrogate key, though this may be offset by enforcing uniqueness over the natural key.- if any indexes are not used, drop 'emfinally, i find it unlikely, but not impossible, that a non-clustered index over 9 of the 10 fields in the clustered index will be of much use...however, how efficient is your PK? perhaps you need to change the order in that? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-17 : 14:26:10
|
| by the way, the answer to your question is most likely #1. the varied key order better corresponds with the queries against that table |
|
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2009-07-17 : 14:42:45
|
| This is a question for Russell just incase he rereads the post:Since you are in the automotive industry too, and store the entire vin as the search param, how do you handle the vin wildcard values?For exampleif a user types in 1N6ED29X731212121That actually refers to a 2003 NISSAN FRONTIER with a ISO VIN of 1N6ED29X-3. Notice how the 9th character is actually a wildcard. The user could have typed in anything. It is would still return the same vehicle. This wildcard value varies by year/make/model (almost always the 9th but also somethings the 4th, 7th etc). If you passed in the complete vin (without wildcards) how do you properly find the vehicle when the base record contains wildcards (so can't just say VIN = @VIN)? By splitting up the vin into different columns we can more easily handle wildcards etcJust curious?Thanks,NicNic |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-17 : 21:30:05
|
replace it with an underscore  vin like @vin1N6ED29X731212121becomes1N6ED29X_31212121isn't it standard practice to ignore the check digit anyway? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-17 : 21:33:34
|
| by the way, how do you handle old cars -- pre 1981? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-17 : 21:43:27
|
| One more thing...when we store the vin of a KNOWN vehicle, the lookup is against the vin.When we are doing APPLICATION lookups, it is almost always by make model year engine, which is in a sperate application table.This can be derived from most vins (post 1981), but not all. Of course this puts a little strain on every year when new models come out, but I'm sure you know all about that lol.Do you subscribe to ACES and AAIA? |
|
|
|
|
index recommendation clarification