always use incorrect index to run the query by sql server

Question

always use incorrect index to run the query by sql server

ColinChen

SSC-Addicted

Points: 414
More actions
March 5, 2010 at 11:23 pm

#96234

One table has many fields. And in the table,it has a clustered index on a fieldA, and a nonclustered index on a fieldB. I have a query like :select * from table where fieldB in (select fieldBfrom othertable). But the sql server always use the Clustered index on fieldA, and it is very slow.
C.G

Viewing 15 posts - 1 through 15 (of 15 total)

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

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 1

Colin-232036 (3/5/2010)
One table has many fields. And in the table,it has a clustered index on a fieldA, and a nonclustered index on a fieldB. I have a query like :select * from table where fieldB in (select fieldBfrom othertable). But the sql server always use the Clustered index on fieldA, and it is very slow.

The use of SELECT * pretty much dictates that you'll not use indexes effectively. It would probably be beneficial to use a WHERE EXISTS in this case, as well, but the SELECT * will continue to be a problem in most cases.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arun.sas SSChampion Points: 11831 More actions · Answer 2

Jeff Moden (3/5/2010)
Colin-232036 (3/5/2010)
One table has many fields. And in the table,it has a clustered index on a fieldA, and a nonclustered index on a fieldB. I have a query like :select * from table where fieldB in (select fieldBfrom othertable). But the sql server always use the Clustered index on fieldA, and it is very slow.
The use of SELECT * pretty much dictates that you'll not use indexes effectively. It would probably be beneficial to use a WHERE EXISTS in this case, as well, but the SELECT * will continue to be a problem in most cases.

Hi Jeff,

In this,

It’s better to use the Joins instead of the sub queries/Exists.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

arun.sas (3/5/2010)
Hi Jeff,
In this,
It’s better to use the Joins instead of the sub queries/Exists.

The results might differ in both the cases.

Check the example given below..

declare @a table ( col char(1) )

declare @b-2 table ( col char(1) )

insert@a

select 'a' union all

select 'b' union all

select 'c' union all

select 'd'

insert@b-2

select 'a' union all

select 'b' union all

select 'c'

select * from @a a inner join @b-2 b on a.col = b.col

select * from @a a where exists( select * from @b-2 b where a.col = b.col )

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Coincidentally I just ran into an issue very similar to this today.

My query doesn't use a Select * and the query is SARGable. However, I noticed that it is trying to use the CI and a Key lookup on SQL 2005. The same query with same index structure on a second server does not require the Key Lookup, and uses the appropriate Index. However, if I remove the desired index, I get an RID lookup (note that it is not a key lookup since this terminology changed).

The difference is that the second server is patched and the first server is running 2005 RTM.

Thus, my recommendation is to check your patch level. Being properly patched in this scenario corrected the index usage problem I was seeing.

This recommendation is based on if you are not really using a select * but used that terminology simply to portray the issue.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

arun.sas (3/5/2010)
It’s better to use the Joins instead of the sub queries/Exists.

Sure about that?

http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/

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

arun.sas SSChampion Points: 11831 More actions · Answer 6

GilaMonster (3/6/2010)
arun.sas (3/5/2010)
It’s better to use the Joins instead of the sub queries/Exists.
Sure about that?
http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/

Hi,

I would thing yet it’s by join, but in the wild compares good result, but have the doubt says more than a condition to compare, what’s happened Gail?

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

Sorry, don't understand what you're asking there. Clarify please.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

arun.sas (3/5/2010)
Jeff Moden (3/5/2010)
Colin-232036 (3/5/2010)
One table has many fields. And in the table,it has a clustered index on a fieldA, and a nonclustered index on a fieldB. I have a query like :select * from table where fieldB in (select fieldBfrom othertable). But the sql server always use the Clustered index on fieldA, and it is very slow.
The use of SELECT * pretty much dictates that you'll not use indexes effectively. It would probably be beneficial to use a WHERE EXISTS in this case, as well, but the SELECT * will continue to be a problem in most cases.
Hi Jeff,
In this,
It’s better to use the Joins instead of the sub queries/Exists.

This is a wide-spread myth, encouraged by an optimizer weakness in SQL Server 2000. Please stop spreading it around.

Joins are not "better" than sub-queries. They are not even "in general" better than subqueries. They are "in general" about the same thing, but it always depends.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

RBarryYoung (3/6/2010)
Joins are not "better" than sub-queries. They are not even "in general" better than subqueries. They are "in general" about the same thing, but it always depends.

This being the reason that I'm writing the blog series (one of the posts linked above) on comparing in, exists and joins, and their negations. So far, in simple tests, IN and EXISTS identical, JOIN a little slower, though that may be a fluke (it was just because of a different join operator used) and I'm not willing to make a general conclusion based on that test.

Joins are most definitely not faster than IN.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

GilaMonster (3/6/2010)
RBarryYoung (3/6/2010)
Joins are not "better" than sub-queries. They are not even "in general" better than subqueries. They are "in general" about the same thing, but it always depends.
This being the reason that I'm writing the blog series (one of the posts linked above) on comparing in, exists and joins, and their negations. So far, in simple tests, IN and EXISTS identical, JOIN a little slower, though that may be a fluke (it was just because of a different join operator used) and I'm not willing to make a general conclusion based on that test.
Joins are most definitely not faster than IN.

Agreed, Gail, and I love the series too.

My own point is that anyone who gets significantly different performance results for JOINs vs Subqueries on SQL Server 2005 or later, does so because either:

_1. They were not written to return the same result sets in all cases (ie., NOT IN), or

_2. There is some bug (or quirk, or idiosyncrasy..) in the SQL Server Optimizer, that should be noted in a bug report to Microsoft at the Connect site, or

_3. Either the queries or the measurements have something wrong them.

Sorry to go on Gail, I know that you know all of this, I just really want other folks to stop spreading this "performance tip" that's been obsolete for five years now. 🙂 If if we could say one or the other is faster for one very specific instance or another, it sure isn't even close to general enough to be a valid truism or tip. IMHO, the only valid recommendation for them these days is "regard them as effectively the same until you test it".

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

RBarryYoung (3/6/2010)
GilaMonster (3/6/2010)
RBarryYoung (3/6/2010)
Joins are not "better" than sub-queries. They are not even "in general" better than subqueries. They are "in general" about the same thing, but it always depends.
This being the reason that I'm writing the blog series (one of the posts linked above) on comparing in, exists and joins, and their negations. So far, in simple tests, IN and EXISTS identical, JOIN a little slower, though that may be a fluke (it was just because of a different join operator used) and I'm not willing to make a general conclusion based on that test.
Joins are most definitely not faster than IN.
Agreed, Gail, and I love the series too.
My own point is that anyone who gets significantly different performance results for JOINs vs Subqueries on SQL Server 2005 or later, does so because either:
_1. They were not written to return the same result sets in all cases (ie., NOT IN), or
_2. There is some bug (or quirk, or idiosyncrasy..) in the SQL Server Optimizer, that should be noted in a bug report to Microsoft at the Connect site, or
_3. Either the queries or the measurements have something wrong them.
Sorry to go on Gail, I know that you know all of this, I just really want other folks to stop spreading this "performance tip" that's been obsolete for five years now. 🙂 If if we could say one or the other is faster for one very specific instance or another, it sure isn't even close to general enough to be a valid truism or tip. IMHO, the only valid recommendation for them these days is "regard them as effectively the same until you test it".

Nice points Barry.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

RBarryYoung (3/6/2010)
My own point is that anyone who gets significantly different performance results for JOINs vs Subqueries on SQL Server 2005 or later, does so because either:
_1. They were not written to return the same result sets in all cases (ie., NOT IN), or
_2. There is some bug (or quirk, or idiosyncrasy..) in the SQL Server Optimizer, that should be noted in a bug report to Microsoft at the Connect site, or
_3. Either the queries or the measurements have something wrong them.

Bearing in mind of course - and again speaking to the crowd - that IN and INNER JOIN are actually not equivalent statements. IN does a semi-join, just looking for matches. JOIN does a complete join, retrieving matching rows. Very important when there are duplicate values in the 2nd table. IN doesn't care, Join will duplicate rows

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

ColinChen SSC-Addicted Points: 414 More actions · Answer 13

Thanks for everyone.

Now I found the issue:

Because the fieldB is varchar , and the fieldC is nvarchar.

select * from tableA where fieldB in(select fieldc from tablec)

If I alter the fieldC to varchar, the index on fieldB is used.

C.G

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Thanks for posting back with your solution.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events