Question about indexes

Question

Post reply

Question about indexes

Barry Ferrell

SSC Journeyman

Points: 80
More actions
September 6, 2006 at 12:09 pm

#91251

if you have and index built on field 1 and field 2 in that order but when you join to the table that has the index, you join by referencing field 2 and then field 1, are you still using the index?

thanks,

Barry

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

September 6, 2006 at 12:13 pm

#658840

Assuming that the join is sargable then yes (not converting column data in the join). The optimiser is smart enough to figure the best path.

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 2

Interesting question actually. I never considered this.

When you say converting the data you are speaking of something like a cast, or convert; correct?

I have had cases, many times when I join tables, or perhaps it is just my WHERE clause and I get an index SCAN rather than SEEK. In some cases I have corrected this either by changing the JOIN (is: perhaps adding WITH (FIRTROWFAST) ) or some other means. Even in those cases I am unclear (most of the time) as to why or how I corrected the issue. I just know that I can try a select set of commands / hints and get a SEEK out of a scan.

I must say Indexes (while I feel that I understand them) are my weak point. That said; I guess do not understand them to the degree that I THINK that I do.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3

jut to add ...

when you build your query in Query Analyser, you can check out the execution plan. (exstimated /executed)

(Ctrl+L or Query/display exstimated execution plan)

(Ctrl+K or Query/Show execution plan)

This option is very educational.

With SQL2005 this is even better

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jo Pattyn SSC-Dedicated Points: 31672 More actions · Answer 4

Jo Pattyn

SSC-Dedicated

Points: 31672

September 7, 2006 at 5:25 am

#659015

functions on columns that participate in the join can make the optimizer decide not to use an index (not sargeable)

like convert(varchar(10),mydate1,102)=convert(varchar(10),mydate1,102)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

September 7, 2006 at 6:54 am

#659033

Or even something as subtile as having a varchar column joining to an nvarchar column can cause implicit_converts which can cause scans. This can be a big headache to find once the app is deployed .

Scott Bernardi SSChasing Mays Points: 600 More actions · Answer 6

Scott Bernardi

SSChasing Mays

Points: 600

September 7, 2006 at 7:02 am

#659040

I'm curious about the WITH (FIRTROWFAST). I searched help in SQL Server 2005 and google didn't get any hits. Is there a typo? WHat is this?

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 7

implicit_converts will indeed messup things.

rule 1 for any RDBMS : tell the system what you know !

SQL2005 can generate nice xml-plans which show you implict conversions !

<

ScalarOperator ScalarString="[DMaterialManagement].[dbo].[T_Inspection].[SequenceNr]=CONVERT_IMPLICIT(numeric(18,0),[@xyzParam2],0) AND [DMaterialManagement].[dbo].[T_Inspection].[DaughterNr]=CONVERT_IMPLICIT(numeric(18,0),[@xyzParam3],0) AND CONVERT_IMPLICIT(nchar(8),[DMaterialManagement].[dbo].[T_Inspection].[CoilNumberMainframe],0)=[@xyzParam1]">

Whilst implict conversions are to be avoided, the conversion of a column to match a predicate will at least generate an indexscan (if the column is indexed) !