July 20, 2007 at 10:36 am
I'm facing the following situation:
I got a SQL2K5 Express database (small personal consulting company) with 57 tables.
There is one (main-) table for the candidates and 9 detailtables (e.g. studies, employment, languages ... ).
I'm asking myself what would be the best strategy for indexing these tables.
I have clustered indexes on the PKs of all tables. Additionaly I got non-clustered indexes on the FK-columns of all the detail tables (~the PK of the candidate-table) grouped with colums which are often looked up (e.g. table for education has a non-clustered index including ID_candidate and majorCode).
1st question: In this particular situation, would it be better to build different indexes for these columns? When do I put the columns in one index, when do I create them separately?
In the mainform of the FE there are different tabs for each detailtable thus all records of these tables are accessed at the same time, ordered by the ID of the main table.
2nd question: wouldn't it be better to have a clustered index on the candidate-id in all detail tables so that the records are already in the order they are accessed?
I tried this first but had problems when somebody entered new data (wrong records where displayed). Could this have to do with to less fill factor?
For testing purposes I made a query over the main and the 9 detail tables and checked the execution plan.
With a inner join (which is out of question because there aren't detail records for every candidate in everey detail table) only some of the tables get index-seeked while most of them get scanned. Doing a LEFT-JOIN all the detail tables get clustered-index-scanned, which is very unsatisfying as you can imagine.
What can be done here to increase the performance? Why aren't the indexes seeked instead of scanning the clustered indexes?
Questions upon questions ... thanks for reading!
July 23, 2007 at 1:06 am
The best strategy depends greatly on the queries that you use. Some points
Put seperate indexes on seperate columns when queries use different columns. an index on 2 columns ColA, ColB cannot be used for seeks by a query filtering only on ColB
From what you say, a clustered index could be the most suitable, but without seeing the schema and queries, it's difficult to say. An index won't cause incorrect data, recardless of type or fill factor. For that, take a look at your queries.
Why you have scans, not seeks is hard to say withou being able to look at the system. How selective are the indexes? How fragmented are they? Are the stats up to date? How many records are you retrieving from the master table at a time? How many from the details?
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
July 23, 2007 at 3:51 am
I greatly appreciate your answer, thanks a lot.
to specify my concern:
Put seperate indexes on seperate columns when queries use different columns. an index on 2 columns ColA, ColB cannot be used for seeks by a query filtering only on ColB
There are also queries for certain columns (in combination) as joins by and queries for particular colums.
From what you say, a clustered index could be the most suitable, but without seeing the schema and queries, it's difficult to say.
a clustered index on the FK-columns of the detail tables or their respective PK?
An index won't cause incorrect data, recardless of type or fill factor. For that, take a look at your queries.
To be more precisely (as you probably mentioned I'm no native english speaker):
The index did not really cause wrong data, thats not the problem. It appeard to be wrong data, which means, a unexpected record was displayed after inserting data und updating it. Remember: this was only in the FE. And also the record was displayed correctly when refreshing the recordsource. But ok, that maybe a ADO problem, since the FE is connected to the Database through ADO and this is, of course, not a forum for ADO ... I thought it could have to do with the index because the data in the table is physically arranged in order of the FK of the main table.
Why you have scans, not seeks is hard to say withou being able to look at the system. How selective are the indexes? How fragmented are they? Are the stats up to date? How many records are you retrieving from the master table at a time? How many from the details?
Well, what you said about separate indexes in the beginning makes this thing more clear. I probably misunderstood an article about indexing here, which (I thought) said that an index on one column is obsolete if you got this column already covered by an "combined" index ...
edit: oops, as it seems BB Code doesn't work ... anyways, I leave it this way. Think its clearer this way
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply