WITH(INDEX(ind1)) syntax, how do i join indexes?

Question

WITH(INDEX(ind1)) syntax, how do i join indexes?

winston Smith

SSCoach

Points: 19500
More actions
January 14, 2009 at 7:21 am

#125583

Im using the WITH(INDEX(indexName)) syntax to help me reproduce execution plans purposfully, so as to use them as a base line:
SELECT m.LastName, m.FirstName, m.Region_No
FROM dbo.Member AS m with( INDEX memberfirstName) )
WHERE m.FirstName = 'Jim'
OR m.LastName = 'Smith'
My problem is that the optimizers query plan is dooing a merge join on two indexes, but i cant replicate it. ive tried this
SELECT m.LastName, m.FirstName, m.Region_No
FROM dbo.Member AS m with( INDEX memberfirstName) INDEX(memberLastName )
WHERE m.FirstName = 'Jim'
OR m.LastName = 'Smith'
and
SELECT m.LastName, m.FirstName, m.Region_No
FROM dbo.Member AS m with( INDEX memberfirstName, memberLastName ))
WHERE m.FirstName = 'Jim'
OR m.LastName = 'Smith'
Both work, but do hash joins instead of a merge join, which is what the optimizer is comming up with. how do i merge join indexes using the WITH(INDEX.... syntax?

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

For testing purposes only!

Hints should not be used in production code, unless you really, really know what you're doing and are 120% certain you know better than the optimiser.

Right, now that the disclaimer's out of the way...

Try OPTION (MERGE JOIN) at the end of the query. I don't know if it will do what you want. It should, that's the hint that forces a join type for the entire query.

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

winston Smith SSCoach Points: 19500 More actions · Answer 2

no, absolutely for test purposes only. OPTION (MERGE JOIN) doesnt make any difference.

here are the queries:

SELECT m.LastName, m.FirstName, m.Region_No

FROM dbo.Member AS m

WHERE m.FirstName = 'Jim'

OR m.LastName = 'Smith'

go

SELECT m.LastName, m.FirstName, m.Region_No

FROM dbo.Member AS m with( INDEX (memberfirstName,memberlastname))

WHERE m.FirstName = 'Jim'

OR m.LastName = 'Smith'OPTION (MERGE JOIN)

here are the query plans. the top one is the optimizers plan, the bottom one is the plan using hints

I need to get these to be identical, so that i have a baseline to compare changes against.

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

Since you have the execution plan that you want to force, try using the OPTIOn (USE PLAN ...) hint. That guarantees that the baseline query will never change plan.

The disclaimer wasn't for you. Was for anyone else who reads this thread.

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

winston Smith SSCoach Points: 19500 More actions · Answer 4

great, that works! code is a little buliker but does the job nicely! thanks