Index question

Question

Index question

Sean Grebey

Ten Centuries

Points: 1159
More actions
December 26, 2012 at 12:58 pm

#267946

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?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

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...

Jared
CE - Microsoft

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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, 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

Sean Grebey Ten Centuries Points: 1159 More actions · Answer 3

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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, 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

Sean Grebey Ten Centuries Points: 1159 More actions · Answer 5

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.

Jared SSC Guru Points: 61793 More actions · Answer 6

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.

Jared
CE - Microsoft

Sean Grebey Ten Centuries Points: 1159 More actions · Answer 7

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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, 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