CODEWITHSUNDEEP
sql-serverindexing
navigator
navigator

Reputation: 1708

What goes wrong when I add the Where clause?

I have a simple query:

 Select Distinct BOLTYPENAME, BOLTYPE.BOLTYPE From BOLTYPE
 Inner Join WORKORDER on WORKORDER.BOLTYPE=BOLTYPE.BOLTYPE
 Inner Join BOLMAIN On BOLMAIN.BOLID=WORKORDER.BOLID 
 Where BOLMAIN.CORID=156

When I run this query without the "Where" clause, it takes 0.1 secs. But adding the where clause causes it to take 1 minute to return. All tables have relevant indexes and they have been de-fragmented. The number of rows in the three tables is:

BOLTYPE: 11 rows

BOLMAIN: 71,455 rows

WORKORDER: 197,500 rows

Here are the execution plans:

Without the Where Clause (0.1 sec):

Without where clause

With the Where Clause (60 sec):

With Where Clause

Any idea as to what could be the issue?

Update: Here are the relevant Index definitions:

CREATE NONCLUSTERED INDEX [BOLIDX] ON [dbo].[WORKORDER]
([BOLID] ASC)
GO

CREATE NONCLUSTERED INDEX [CORIDX] ON [dbo].[BOLMAIN]
([CORID] ASC)
INCLUDE ([BOLID])
GO

CREATE NONCLUSTERED INDEX [BOLTYPEIDX] ON [dbo].[WORKORDER]
([BOLTYPE] ASC)
GO

Upvotes: 4

Views: 121

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

Recreate the CORIDX index so it covers BOLID. You're joining on BOLID, so you want it to be part of the index, not just one of the included columns.

In other words:

CREATE NONCLUSTERED INDEX [CORIDX] ON [dbo].[BOLMAIN]
([CORID] ASC, [BOLID] ASC)

Upvotes: 1

Related Questions