SQL Index Performance

Question

We have a table called table1 ...

(c1 int indentity,c2 datetime not null,c3 varchar(50) not null,
   c4 varchar(50) not null,c5 int not null,c6 int ,c7 int)

on column c1 is primary key(clusterd Index)
on column c2 is index_2(Nonclusterd) 
on column c3 is index_2(Nonclusterd)
on column c4 is index_2(Nonclusterd)
on column c5 is index_2(Nonclusterd)

It contains 10 million records. We have several procedures pointing to "table1" with different search criteria:

select from table1 where c1=blah..and c2= blah.. and c3=blah..
select from table1 where c2=blah..and c3= blah.. and c4=blah..
select from table1 where c1=blah..and c3= blah.. and c5=blah..
select from table1 where c1=blah..
select from table1 where c2=blah..
select from table1 where c3=blah..
select from table1 where c4=blah..
select from table1 where c5=blah..

What is the best way to create non-clustered index apart from above, or modify existing indexes to get good index performance and reduce the execution time?

Answer 1

And now to actually respond...

The trick here is that you have single-column lookups on any number of columns, as well as composite column lookups. You need to understand with what frequency the queries above are executing - for those that are run very seldom, you should probably exclude them from your indexing considerations.

You may be better off creating single NCIX's on each of the columns being queried. This would likely be the case if the number of rows being returned is very small, as the NCIX's would be able to handle the "single lookup" queries as well as the composite lookups. Alternatively, you could create single-column NCIX's in addition to covering composite indexes - again, the deciding factor being the frequency of execution and the number of results being returned.

Answer 2

This is somewhat tough to answer with just the information you have provided. There are other factors you need to weigh out.

For example: How often is the table updated and what columns are updated frequently? You'll be paying a cost on these updates due to index maintenance.

What is the cardinality of your different columns? What queries are you executing most often and what columns appear in the where clause of those queries?

You need to first figure out what your parameters for acceptable performance are for each of your queries and work from their taking into account the things I have mentioned above.

With 10 million rows, partitioning your table could make a lot of sense here.

Answer 3

Have you thought about using the Full Text Search component of MSSQL. It might offer the performance that you are looking for?