June 30, 2009 at 2:41 am
Erm, I'm busy packaging the db change scripts and then I realised something.
Guys, under what situations would it be better to create IX_Tmp_bob_pkColA and IX_Tmp_bob_pkColB as opposed to just PK_Tmp_bob? Approximately 75% of the SELECTs coming from the application that uses the database only use pkColB, 20% use pkColA and pkColB, and the other 5% use pkColA, otherCol (FLOAT for those of you who don't like going back to the first post) and pkColB.
Now the resident knowledge specialist/domain specialist/guywhosbeenherelongerthananyoneandknowsthesystem says that otherCol isn't used anymore, at all, in any of our clients' systems, but because of that 5% in the code, should I create a non-unique index that covers (if that is the correct use of the term) pkColA, otherCol and pkColB? That 5% is just at the screen that edits the data for this particular master table.
This table has been fairly stable at around 500 entries for about three years, and in the past year and a half has grown steadily to around 5600 rows. I know that at this size the network might prove more of a bottleneck than the db engine, but I would still like to do things in a correct manner and learn while I'm at it.
June 30, 2009 at 4:26 am
If most of the usage is on ColB and quite a bit on the combination, I'd change that PK so that the columns are in the other order (ColB, ColA) and not put any other indexes on. With the pk as it is now (ColA, ColB) it cannot be used for a seek by a query using only ColB.
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
June 30, 2009 at 6:13 am
I'm surprised that the column order of an index makes a difference! Thanks Gila.
June 30, 2009 at 6:18 am
GDI Lord (6/30/2009)
I'm surprised that the column order of an index makes a difference!
SQL can only seek on a left-based subset of the index key
Think of the local phone directory. It's essentially an index on the columns surname, first name, location. How would you find me everyone named Matthew who lives in Boksburg? Note that you don't know the surname.
Now, how about I ask you to find Mr Matthew Brown?
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
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
June 30, 2009 at 8:42 am
Thanks again Gila. That was an informative read. Busy reading your post about statistics in between working now...
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply