January 24, 2013 at 3:43 am
I have an example where the table im indexing appears in the join and where clause:
select OL1.a, OL1.b, OL1.c, OL1.d, OL1.e, OL1.f, OL1.g
from Orders O
inner join OrderLines OL1
inner join OL1 on O.InvoiceID = OL1.InvoiceID
inner join Product P on P.ProductID = OL1.ProductID
WHERE (OL1.h is not null) and (O.InvoiceNumber = 1001)
and (OL1.status = 1)
Ive been trying a few indexes but im not sure entirely which is best. i really would like to ask:
1. when a table is in the join and where clause, do i create the index with the join col first, or the where col first ?
e.g create ix_test on orderLines(status, InvoiceId, ProductID) Include(.....)
or
e.g create ix_test on orderLines(InvoiceId, ProductID,status) Include(.....)
2. where there are two columns joined upon, which should generally come first in the index definition? is it based on cardinality? if so is it high or low that decides what col comes first?
Thanks!
January 24, 2013 at 3:59 am
Since there is no complete table definition will be not accurate suggestion from us.plus no idea either you have clustered index on tables or not ?
anyways
Order table will have index on InvoiceID and InvoiceNumber
OrderLines will have ix_test on orderLines(InvoiceId, ProductID,status) Include(.....)
and cardinality high for left most column will work better,like sequence InvoiceId, ProductID,status
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 24, 2013 at 4:12 am
james marriot (1/24/2013)
I have an example where the table im indexing appears in the join and where clause:
select OL1.a, OL1.b, OL1.c, OL1.d, OL1.e, OL1.f, OL1.g
from Orders O
inner join OrderLines OL1
inner join OL1 on O.InvoiceID = OL1.InvoiceID
inner join Product P on P.ProductID = OL1.ProductID
WHERE (OL1.h is not null) and (O.InvoiceNumber = 1001)
and (OL1.status = 1)
Ive been trying a few indexes but im not sure entirely which is best. i really would like to ask:
1. when a table is in the join and where clause, do i create the index with the join col first, or the where col first ?
e.g create ix_test on orderLines(status, InvoiceId, ProductID) Include(.....)
or
e.g create ix_test on orderLines(InvoiceId, ProductID,status) Include(.....)
2. where there are two columns joined upon, which should generally come first in the index definition? is it based on cardinality? if so is it high or low that decides what col comes first?
Thanks!
Columns on JOIN and WHERE clause are signals to help you determine which will be indexed. However, there are factors which impact to decision when you create a index:
1- Selective data on the columns
2- Are there many queries are using the columns to compare?
....
About theory index, you should re-search it http://www.sqlservercentral.com/stairway/72399/
Actually, you want to know an index is good/bad or it's used/unused until you have data in real environment.
January 24, 2013 at 4:54 am
Bhuvnesh (1/24/2013)
Order table will have index on InvoiceID and InvoiceNumberOrderLines will have ix_test on orderLines(InvoiceId, ProductID,status) Include(.....)
Not the most efficient order for the columns within the indexes, will probably result in sub-optimal performance, probably index scans rather than index seeks
and cardinality high for left most column will work better,like sequence InvoiceId, ProductID,status
Selectivity is only a portion of the story, unfortunately it's the only portion that most people remember.
The point of a index is to reduce the rows in consideration for the query as early as possible. As such, you want leading columns of indexes to be columns that you filter on, not the most selective column in the table.
An index that leads with InvoiceID is not going to be very useful for a query that filters on Status and another column.
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
January 24, 2013 at 4:58 am
james marriot (1/24/2013)
1. when a table is in the join and where clause, do i create the index with the join col first, or the where col first ?e.g create ix_test on orderLines(status, InvoiceId, ProductID) Include(.....)
or
e.g create ix_test on orderLines(InvoiceId, ProductID,status) Include(.....)
Typically where clause. The point of an index is to reduce the rows in consideration for the query as fast as possible. With InvoiceID first in both indexes, you've got no way to do an index seek on one of the filters (the where clause) and then a join, if SQL wants to filter before the join it has to do an index scan.
2. where there are two columns joined upon, which should generally come first in the index definition? is it based on cardinality? if so is it high or low that decides what col comes first?
That one mostly comes down to test it and see. There's a huge number of factors involved, the join types (physical operator), the number of rows in the outer resultset, the number of rows that will match in a join, etc.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply