Please help rewrite this query to take advantage

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help rewrite this query to take advantage

Author

Topic

MMMY
Starting Member

14 Posts

Posted - 2012-06-01 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-01 : 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 - 2012-06-01 : 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

52326 Posts

Posted - 2012-06-01 : 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

15732 Posts

Posted - 2012-06-01 : 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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-01 : 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 - 2012-06-01 : 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?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-01 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources