Correct Index for Seek? - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Correct Index for Seek?

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2011-09-14 : 15:17:22

When I write the data access portion like this


	  FROM myMessage    m2
INNER JOIN Message_Code M1
		ON m2.Message_Cd		  = M1.Message_Cd
	   AND m2.Message_Language_Cd = M1.Message_Language_Cd
 LEFT JOIN User_Message UM
		ON m2.Group_Trans_Id      = UM.Group_Trans_Id
    WHERE m2.[Business_Key_Id]	  = @myMessage_Id

it performs a scan on the Primary

if I rewrite to do a derived look up on a col with a unique index it does the seek?

I'm confused


	  FROM (SELECT Group_Trans_Id FROM myMessage WHERE [Business_Key_Id] = @myMessage_Id) AS XXX
INNER JOIN myMessage    m2
		ON m2.Group_Trans_Id = XXX.Group_Trans_Id
INNER JOIN Message_Code M1
		ON m2.Message_Cd		  = M1.Message_Cd
	   AND m2.Message_Language_Cd = M1.Message_Language_Cd
 LEFT JOIN User_Message UM
		ON m2.Group_Trans_Id      = UM.Group_Trans_Id
-- For whatever reason, this causes a scan and uses the PK Index, instead of IX_myMessage_Business_Key_Id
--		for a seek operation. Did the derived table which uses the correct index and does a seek
--	 WHERE m2.[Business_Key_Id]	  = @myMessage_Id

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-14 : 16:04:57

[code]ON m2.Group_Trans_Id = UM.Group_Trans_Id[/code]
Both are indexed?

What is the selectivity after the result of the outer join?

Does it do a seek if you use inner join (as a test)?

Does SSMS report any missing indexes?

X002548
Not Just a Number

15586 Posts

Posted - 2011-09-15 : 10:07:53

Nope I've got all the appropriate indexes. That's why the second one use the index for business key id. It's like the join overrides the index...


ALTER TABLE myMessage
	ADD CONSTRAINT PK_myMessage PRIMARY KEY  CLUSTERED (Group_Trans_Id ASC)
go

CREATE NONCLUSTERED INDEX IX_myMessage_Business_Key_Id ON myMessage
( 
	Business_Key_Id       ASC,
	Group_Trans_Id        ASC
)
go

CREATE NONCLUSTERED INDEX IX_myMessage_Bussiness_Group_Cd_Suite_Cd ON myMessage
( 
	Business_Group_Cd     ASC,
	Suite_Cd              ASC
)
go

CREATE NONCLUSTERED INDEX IX_myMessage_Category_Cd ON myMessage
( 
	Category_Cd           ASC
)
go

CREATE NONCLUSTERED INDEX IX_myMessage_Expiration_DT ON myMessage
( 
	Expiration_DT         ASC,
	Term_DT               ASC,
	Group_Trans_Id        ASC
)
go

CREATE NONCLUSTERED INDEX IX_myMessage_Priority_Cd ON myMessage
( 
	Priority_Cd           ASC
)
go

CREATE NONCLUSTERED INDEX IX_myMessage_Term_DT ON myMessage
( 
	Term_DT               ASC,
	Expiration_DT         ASC,
	Group_Trans_Id        ASC
)
go

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-15 : 12:01:33

My guess is that it needs to scan to perform the left join. But in your 2nd query, you've already done the filtering for it.

I wonder if

LEFT JOIN User_Message UM
	On	m2.[Business_Key_Id] = @myMessage_Id
	And	m2.Group_Trans_Id      = UM.Group_Trans_Id

would cause it to do a seek.

Statistics are up to date?

Oh yeah, if you set statistics io on, what's the reads for each query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources