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

Index question Expand / Collapse
Author
Message
Posted Wednesday, December 26, 2012 12:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:42 AM
Points: 41, Visits: 70
I am trying to speed up a Query joining two tables. For simplicity sake, we'll just put it as I am doing:

An Inner Join On Table1.FieldA and Table2.FieldA
A Where Clause on Table2.FieldB

Right now:
Table1 Has an Index on FieldA
Table2 has a Index on FieldA/FieldB
Table2 has a primary key ID field

Would putting an Index on Table2.FieldB by itself help at all since it is in the WHERE clause by itself?
Post #1400345
Posted Wednesday, December 26, 2012 1:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 2,691, Visits: 3,375
I would answer this for you, but it is easy enough to set up and test. Look at the execution plans both before and after the index...

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1400355
Posted Wednesday, December 26, 2012 1:14 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
Maybe take a read through these:

http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/



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 #1400357
Posted Wednesday, December 26, 2012 1:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:42 AM
Points: 41, Visits: 70
Well anybody actually know the answer and could spare the 5 seconds to answer instead of wanting me to spend more time than I have to this holiday away from my family trying to grow as a person trying to figure myself it out myself, I'd much appreciate it.
Post #1400365
Posted Wednesday, December 26, 2012 1:56 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
Sean Grebey (12/26/2012)
Would putting an Index on Table2.FieldB by itself help at all since it is in the WHERE clause by itself?


Maybe, depends what's in the select clause, how selective the filter is, how selective (if at all) the join is, what exactly the filter in the where clause is and a couple other factors.

Hence why I referred you to a set of articles (that I wrote), because there's no enough information in your post to answer the question and writing out all the various considerations and possibilities will take the entire rest of my holiday and probably end up as long as one of those articles.



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 #1400367
Posted Wednesday, December 26, 2012 2:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:42 AM
Points: 41, Visits: 70
GilaMonster (12/26/2012)
Sean Grebey (12/26/2012)
Would putting an Index on Table2.FieldB by itself help at all since it is in the WHERE clause by itself?


Maybe, depends what's in the select clause, how selective the filter is, how selective (if at all) the join is, what exactly the filter in the where clause is and a couple other factors.

Hence why I referred you to a set of articles (that I wrote), because there's no enough information in your post to answer the question and writing out all the various considerations and possibilities will take the entire rest of my holiday and probably end up as long as one of those articles.


Thanks for the links.
Post #1400372
Posted Wednesday, December 26, 2012 2:13 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 2,691, Visits: 3,375
If it is as simple as what you provided... creating an index on Table2.FieldB will "probably" help. It will get rid of the scan and use a key lookup to get the data for the results. Again, "probably" if it is as simple as you have shown.

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1400374
Posted Wednesday, December 26, 2012 2:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:42 AM
Points: 41, Visits: 70
SQLKnowItAll (12/26/2012)
If it is as simple as what you provided... creating an index on Table2.FieldB will "probably" help. It will get rid of the scan and use a key lookup to get the data for the results. Again, "probably" if it is as simple as you have shown.


Thanks.
Post #1400378
Posted Wednesday, December 26, 2012 2:38 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 3:46 PM
Points: 42,462, Visits: 35,525
SQLKnowItAll (12/26/2012)
If it is as simple as what you provided... creating an index on Table2.FieldB will "probably" help.


Depends how selective the filter is. It's definitely not a covering index, so depends on whether the cost of the key lookups is judged low enough (and that's assuming the filter's SARGable in the first place)

My gut feel is that it probably won't help. There's an index that appears covering, though the columns are in the wrong order to support the filter)



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 #1400380
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse