Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

To whoever reads this, sorry for the long question and thanks so much for your time.

I'm needing some help on understanding what columns should be key/non key in the following situation, and what potential impacts it might have on performance either way.

We're using SQL Server 2008 but must support 2005 and in our application, we have a query which is hit constantly but the where clause changes depending on how the query is hit.

It ALWAYS includes 3 columns in the where (lets call them a1, a2, a3), and varaibly includes some of the others (lets call them v1,v2,v3), and always selects all of the columns.

so what we're looking at is a query that always looks like;

select id, a1, a2, a3, v1, v2, v3, data1, data2, data3, table_order
from example_table
where a1 = ?
and a2 = ?
and a3 = ?
...
order by table_order

where sometimes (but not too often) the where clause contains one of or combinations of;

and v1 = ?
and v2 = ?
and v3 = ?

Is there a best practice for this sort of situation? From what I understand about indexing, I should be make a non-clustered index that has key columns for stuff in the where clause and everything else selected as a non key column;

CREATE NONCLUSTERED INDEX
ON example_table(a1, a2, a3, table_order) 
INCLUDE (id, v1, v2, v3, data1, data2, data3)

My Question here is should I be including v1, v2, v3 in as key columns given that sometimes they are there? IE;

CREATE NONCLUSTERED INDEX
ON example_table(a1, a2, a3, table_order, v1, v2, v3) 
INCLUDE (id, data1, data2, data3)

Would that help with performance given that this table is queried, inserted to, updated to, etc. all the time? Or should I have different indexes for each possible iteration (IE an index with a1-3, table_order & v1 as key columns for example)? IE;

CREATE NONCLUSTERED INDEX
ON example_table(a1, a2, a3, table_order, v1) 
INCLUDE (id, v2, v3, data1, data2, data3)

CREATE NONCLUSTERED INDEX
ON example_table(a1, a2, a3, table_order, v2) 
INCLUDE (id, v1, v3, data1, data2, data3)

etc.

I should mention here that DB size is of concern so I don't really want to do the multiple indexes if I can help it.

I guess what this really boils down to is that I'm wondering if the query optimizer will use the one index that has all of the key columns listed in all of these different situations?

select id, a1, a2, a3, v1, v2, v3, data1, data2, data3, table_order
from example_table
where a1 = ?
and a2 = ?
and a3 = ?
order by table_order

select id, a1, a2, a3, v1, v2, v3, data1, data2, data3, table_order
from example_table
where a1 = ?
and a2 = ?
and a3 = ?
and v1 = ?
order by table_order

select id, a1, a2, a3, v1, v2, v3, data1, data2, data3, table_order
from example_table
where a1 = ?
and a2 = ?
and a3 = ?
and v1 = ?
and v2 = ?
order by table_order

etc.

Or am I forced into having to do multiple indexes to satisfy each condition?

Given that the where clause 90% of the time only have a1, a2, a3, should I just include the v columns in as non key columns because they're selected? Does the inclusion of the extra key columns hurt performance for the 90% use case?

Could specifying so many key columns in the order I'm looking to specify cause performance concerns on simple joins like;

inner join example_table
on a1 = ?

or would the query optimizer just not use this index whatsoever for that kind of simple join? If so, I'm going to need a separate index like the follow, right?

CREATE NONCLUSTERED INDEX
ON example_table(a1) 
share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

That's a great question.
And there are good answers.

The engine definitely will use the index even if you don't use every key column.
That's especially so if they are in order, as you are talking about.
(can anyone else speak to different orders of key columns?)

You will benefit just fine from selecting just on the first column alone as a key, or multiple columns.
What will make a difference - for any index - is staying inside the INCLUDEd columns.
No matter how many key columns you use in your Where, the performance hit for having to go back to the primary key for additional columns can be huge as it doubles the "operations".

When it comes to dealing with performance vs. size, you have the same problem as with any index.
Since you know you want the same columns returned in all cases, if you are READ focused, you will probably want to the index with all 6, if you INCLUDE everything.
It will certainly save you db size compared to making both indexes.

On WRITE, you obviously have a bigger burden with a larger index. That is a significant additional amount of sorting.
If you do just one row inserted at a time, maybe it won't hardly matter at all.
If you do bulk inserts, you'll definitely want to test the two indexes to see the write performance for your actual inserts.

share|improve this answer
    
We definitely do one at a time. It's a highly concurrent web application, so sometimes the one at a times can seem to happen all at once but they're all separate DB connections with separate transactions. That's actually the reason we are looking to put new indexing into the app, because of some long running transactions that we're coming up. We're talking like 10-15 minute transactions. These are locking tables that we need to run updates/inserts/reads on so we've set read_committed_snapshot on and are making indexes to prevent the table scan that waits for that long transaction. –  Adam Reynolds Jan 16 at 1:30
    
Cool. Try the single, larger index. It seems like you're on the right track. –  Mike M Jan 16 at 1:32
    
Now if we could just find the time to start caching queries in the application so we could just optimize the DB for just writes, I'd be a happy camper! –  Adam Reynolds Jan 16 at 1:36
    
Hey Mike, see the end of the post.. if we have a number of joins on the table using a1, will this index also optimize those? Or do you think it'd be more efficient to add another index specifically for that purpose? assuming joins are structured; inner join example_table on a1 = ? and what about impacts to subselects like; where id_fk in (select id from example_table where a1 = ?) ? Again man, thanks so much for your help! –  Adam Reynolds Jan 16 at 1:41
    
Yeah, good indexing can make a huge difference, including for Writes, as you're talking about. If you have inadequate indexing, reads & writes have to escalate locks up towards the table level because it is (or looks to be) all over the place to the engine. –  Mike M Jan 16 at 1:41
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.