index-question

Question

index-question

Viewing 7 posts - 16 through 21 (of 21 total)

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

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

So both ID and Col1 are already indexed. If this is going to be a once off query of Col2 whrn don't bother indexing it.

What's the data type of Col2? What kind of query are you doing on it (exact match, like,...?)

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

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 2

ID is int, COL1 is char(14) and COL2 is char(16)

my query is: select ID from TBL where COL1= @a or COL2= @a

, where @a is a char(14) parameter.

Paul Mu SSCertifiable Points: 5547 More actions · Answer 3

And just a note of caution here, it would be wise if you did not use langauge that might offend others (your post #33), especially when they are also trying to help you out!

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

In that case I will stick with my earlier advice. If this is a once-off query and in the future you will only search by ID then don't put an index on Col2. The overhead of creating an index will nullify any gains you get from the query.

If this query will be run in the future, then consider a NC index on Col2 and you should be fine (Since the other 2 columns are already indexed)

There is no need to include ID in any of the NC indexes since, as the clustering key, it is already included in the indexes as the pointer to the data

HTH

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

Airolg Icsadjag SSC Eights! Points: 961 More actions · Answer 5

Airolg Icsadjag

SSC Eights!

Points: 961

January 24, 2006 at 12:08 am

#616577

uhum.

thats good! i take your advice.

thanks a lot G

Giles McArdell SSC Eights! Points: 875 More actions · Answer 6

A UNION might help get rid of the OR IE:

select ID from TBL where COL1= @a UNION select ID from TBL where COL2= @a

This may or may not speed it up, but will produce duplicates where @a is in both columns.

Or possibly:

select ID from TBL where NOT (COL1 <> @a AND COL2 <> @a)

I think this one should work

"Don`t try to engage my enthusiasm, I don`t have one."

(Marvin)

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

select ID from TBL where COL1= @a UNION select ID from TBL where COL2= @a

That will result in one index seek (first query) and one table scan (second) plus a distinct sort (to remove duplicates. union removes duplicates, union all doesn't)

It's highly unlikely that either of the above will be faster than a simple OR. Especially since the second is logically identical to an OR

You can get an estimated execution plan to be sure.

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