| Author |
 Topic  |
|
|
MMMY
Starting Member
14 Posts |
 Posted - 06/01/2012 : 10:46:29
|
I created an index on Column A. I want to rewrite this query so that it takes advantage of the index - Using an AND clause in the WHERE ignores the index.
The table is indexed by Column A.
update TableExample
set B = True
where A > 3
and C < 10
Because it is using a where clause that involves both A and C, no index is used. Thanks so much for the help. |
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 06/01/2012 : 11:12:26
|
| If SQL thinks it's more effecient to not use your index, then why would you think your index will be faster? The only option I know of would be to add an index hint. But that is, probably, not a very good idea. |
 |
|
|
MMMY
Starting Member
14 Posts |
Posted - 06/01/2012 : 11:29:37
|
I don't know, maybe I'm just being cocky versus computer intelligence,
but for my case, I would think it'd be more efficient to look at everything that is A > 3 first through the use of an index, then figure out whether C is < 10.
The way it's set up now, it's going through every single record to determine if it meets both conditions. I wonder why SQL thinks it's more efficient that way? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 06/01/2012 : 11:36:36
|
not sure if this is efficient but this is an alternative approach
update TableExample
set B = case when C < 10 then True else B end
where A > 3
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 06/01/2012 : 11:38:59
|
| Your index won't help because the C column is not part of the index. Either change your index or let the optimizer choose. |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 06/01/2012 : 11:51:25
|
quote:
Originally posted by MMMY
I don't know, maybe I'm just being cocky versus computer intelligence,
but for my case, I would think it'd be more efficient to look at everything that is A > 3 first through the use of an index, then figure out whether C is < 10.
The way it's set up now, it's going through every single record to determine if it meets both conditions. I wonder why SQL thinks it's more efficient that way?
You might want to do some reading on how SQL does it's "thang" to get more familar with the optimizer, query optimization, etc..
If SQL uses the index you have created it has to do a Lookup to get the other value(s), in this case C. So, instead of doing a bunch of lookups, it's more effecent to just scan the table (at least according to the optimizer). |
|
|
|
MMMY
Starting Member
14 Posts |
Posted - 06/01/2012 : 12:04:18
|
I just want to say thank you very much for all the help. I know I am a complete newbie, so thank you for your patience.
So the recommendation is I create an index on A and include column C. However, in my queries, I will be updating column C.
update TableExample
set C = 1
where A > 3
and C is null
This is what SQL recommends, is it advisable to go ahead and create the index based on their recommendation? |
Edited by - MMMY on 06/01/2012 12:04:42 |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 06/01/2012 : 12:37:01
|
| Just to be clear INCLUDE has a special meaning with indexes. IMHO you don't want to INCLUDE C, you want it to be part of the index (subtle difference). And if this is a query you would execute fequently, you might want to create a filtered index WHERE C IS NULL. |
|
|
| |
Topic |
|
Please help rewrite this query to take advantage
