SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexing where table is in join and where clause


Indexing where table is in join and where clause

Author
Message
james marriot
james marriot
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 26
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!
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13390 Visits: 4077
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;-)
Dung Dinh
Dung Dinh
SSChasing Mays
SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)SSChasing Mays (657 reputation)

Group: General Forum Members
Points: 657 Visits: 1233
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226552 Visits: 46329
Bhuvnesh (1/24/2013)
Order table will have index on InvoiceID and InvoiceNumber
OrderLines 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226552 Visits: 46329
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search