Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Indexing where table is in join and where clause Expand / Collapse
Author
Message
Posted Thursday, January 24, 2013 3:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 10, 2013 6:49 AM
Points: 21, 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!
Post #1411030
Posted Thursday, January 24, 2013 3:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:12 AM
Points: 2,837, Visits: 3,955
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
Post #1411038
Posted Thursday, January 24, 2013 4:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 9:14 PM
Points: 138, Visits: 924
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.
Post #1411046
Posted Thursday, January 24, 2013 4:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 42,470, Visits: 35,541
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 2008, MVP
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

Post #1411062
Posted Thursday, January 24, 2013 4:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:11 PM
Points: 42,470, Visits: 35,541
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 2008, MVP
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

Post #1411065
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse