We help IT Professionals succeed at work.

Why is the query plan not using a clustered index seek and at a lower cost?

I have a table with a high row count.

It has a unique clustered index on the primary key.

The query plan is showing that it is using a clustered index scan at 82% cost.

Why is the query plan not using a clustered index seek and at a lower cost?
Comment
Watch Question

Awarded 2008
Awarded 2008

Commented:
Why is the query plan not using a clustered index seek and at a lower cost?

By lower cost, you mean that it is finding an alternative to performing a scan?  Is it seeking on any index?  Can you post your query?
Could you also post the Actual Execution Plan?

Author

Commented:
Can't post query a the moment, sorry.

My question is... Since there is a clustered index on a large table I thought that a seek would be used. Instead a scan has been chosen. What could be causing this?
Awarded 2008
Awarded 2008

Commented:
Just because a clustered index is in place doesn't mean that a seek will be done.  It totally depends on how the query is written.  For example, if you have a table named Table1 with a CI on the Fld1 field, then this would seek

select fld1 from table1 where fld1 = 3

however, this would not, this would scan

select fld1 from table1 where cast(fld1 as varchar(5)) = 3

So, it really depends on how you write your queries.  

Author

Commented:
Like i said i am not able to post the query code at the moment....

Why did your cast example use a Scan not a seek?

select fld1 from table1 where cast(fld1 as varchar(5)) = 3

Author

Commented:
I a CONVERT in the WHERE clause of the query. Could this cause a scan not a seek.

Where AttendanceDate >= CONVERT(DATETIME, '2008-04-01 00:00:00', 102))
Awarded 2008
Awarded 2008

Commented:
right, any type (almost) of function on an indexed column invalidates the index and causes the optimizer to have to scan the records instead of seek them.  This is known as SARG-ability.  

SARGable = Search-Argument-able.  :)
Awarded 2008
Awarded 2008
Commented:
No, that woudln't cause it because you're not performing the function on the field in the table.

Are you using any LEFT JOINS, NOT IN()s, NOT EXIST(), a wildcard '%text%', or anything like that?  Are you joining on the field?

Author

Commented:
Here is my query
SELECT 
                      A_H_5_R.dbo.OPA_General.OPAJoinKey, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 1 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS Primary_diag, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 2 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS Sub_diag, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 3 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag1, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 4 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag2, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 5 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag3, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 6 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag4, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 7 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag5, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 8 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag6, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 9 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag7, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 10 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag8, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 11 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag9, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 12 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag10, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 13 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag11, 
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 14 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag12, 
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 1 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod1, 
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 1 THEN OPA_Procedures.ProcedureDate ELSE NULL END) AS prod1_date_1, 
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 2 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod2, 
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 3 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod3, 
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 4 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod4, 
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 5 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod5, 
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 6 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod6, 
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 7 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod7, 
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 8 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod8, 
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 9 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod9, 
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 10 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod10, 
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 11 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod11, 
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 12 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod12
FROM         A_H_5_R.dbo.OPA_Procedures RIGHT OUTER JOIN
                      A_H_5_R.dbo.OPA_General ON 
                      A_H_5_R.dbo.OPA_Procedures.OPAJoinKey = A_H_5_R.dbo.OPA_General.OPAJoinKey LEFT OUTER JOIN
                      A_H_5_R.dbo.OPA_D ON 
                      A_H_5_R.dbo.OPA_General.OPAJoinKey = A_H_5_R.dbo.OPA_D.OPAJoinKey LEFT OUTER JOIN
                      HRG4_2010.VW_O_C_2010 ON A_H_5_R.dbo.OPA_General.OPAJoinKey = HRG4_2010.VW_O_C_2010.OPAJoinkey
WHERE     (A_H_5_R.dbo.OPA_General.ContractSerialNumber <> '5X' OR
                      A_H_5_R.dbo.OPA_General.ContractSerialNumber = '') AND 
                      (A_H_5_R.dbo.OPA_General.AttendedOrDNA = '5' OR
                      A_H_5_R.dbo.OPA_General.AttendedOrDNA = '6') AND 
                      (A_H_5_R.dbo.OPA_General.CommissionerCode LIKE '5NT%' OR
                      A_H_5_R.dbo.OPA_General.CommissionerCode LIKE '5CR%' OR
                      A_H_5_R.dbo.OPA_General.CommissionerCode LIKE '5AA%' OR
                      A_H_5_R.dbo.OPA_General.CommissionerCode LIKE '5CL%') AND 
                      (A_H_5_R.dbo.OPA_General.FirstAttendance = '1' OR
                      A_H_5_R.dbo.OPA_General.FirstAttendance = '2' OR
                      A_H_5_R.dbo.OPA_General.FirstAttendance = '3' OR
                      A_H_5_R.dbo.OPA_General.FirstAttendance = '4') AND (HRG4_2010.VW_O_C_2010.BatchID IS NULL) AND 
                      (A_H_5_R.dbo.OPA_General.AttendanceDate >= CONVERT(DATETIME, '2008-04-01 00:00:00', 102))
GROUP BY A_H_5_R.dbo.OPA_General.OPAJoinKey

Open in new window

Awarded 2008
Awarded 2008

Commented:
Its a combination of your <> clause and your OR statements.

Author

Commented:
chapmandew:

I am using any LEFT JOINS, wildcard and I am joining on the clustered index column.

Is there anything I can do to optimize?

Author

Commented:
chapmandew:
>>Its a combination of your <> clause and your OR statements.

So is there anything i can do?
Awarded 2008
Awarded 2008

Commented:
There are.  What fields in your A_H_5_R.dbo.OPA_General are indexed?

Author

Commented:
AttendanceDate and OPAJoinKey are indexed. OPAJoinKey has a unique clustered index.

The following do not have an index and are used in the WHERE clause

ContractSerialNumber
AttendedOrDNA
CommissionerCode
FirstAttendance
Awarded 2008
Awarded 2008

Commented:
Ok, cool.  Next, how many records will this query bring back?

Author

Commented:
Not sure.. maybe a 1million +

Author

Commented:
or could be 500k
Awarded 2008
Awarded 2008

Commented:
ewww...then, we might be able to make this scan faster, but a scan is still going to happen.  The reason is that it is a lot faster for SQL to scan the tables and bring back the records you need, since there are so many to return.  

Author

Commented:
should i be concerned that the cost is 82%
Awarded 2008
Awarded 2008
Commented:
See if this helps:

if OBJECT_ID('tempdb..#results') is not null
drop table #results

select *
into #results
from A_H_5_R.dbo.OPA_General
where
(                        A_H_5_R.dbo.OPA_General.ContractSerialNumber <> '5X' OR
                      A_H_5_R.dbo.OPA_General.ContractSerialNumber = ''
           ) AND
           (
                        A_H_5_R.dbo.OPA_General.AttendedOrDNA = '5' OR
                A_H_5_R.dbo.OPA_General.AttendedOrDNA = '6'
           ) AND
           (
                              A_H_5_R.dbo.OPA_General.CommissionerCode LIKE '5NT%' OR
                              A_H_5_R.dbo.OPA_General.CommissionerCode LIKE '5CR%' OR
                              A_H_5_R.dbo.OPA_General.CommissionerCode LIKE '5AA%' OR
                              A_H_5_R.dbo.OPA_General.CommissionerCode LIKE '5CL%'
            ) AND
            (
                  A_H_5_R.dbo.OPA_General.FirstAttendance = '1' OR
          A_H_5_R.dbo.OPA_General.FirstAttendance = '2' OR
          A_H_5_R.dbo.OPA_General.FirstAttendance = '3' OR
          A_H_5_R.dbo.OPA_General.FirstAttendance = '4'
         )
          (A_H_5_R.dbo.OPA_General.AttendanceDate >= CONVERT(DATETIME, '2008-04-01 00:00:00', 102))

SELECT
                      OPA_General.OPAJoinKey,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 1 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS Primary_diag,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 2 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS Sub_diag,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 3 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag1,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 4 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag2,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 5 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag3,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 6 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag4,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 7 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag5,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 8 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag6,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 9 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag7,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 10 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag8,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 11 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag9,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 12 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag10,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 13 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag11,
                      MAX(CASE WHEN OPA_D.DiagnosisOrder = 14 THEN OPA_D.ID10DiagnosisCode ELSE NULL END) AS diag12,
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 1 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod1,
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 1 THEN OPA_Procedures.ProcedureDate ELSE NULL END) AS prod1_date_1,
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 2 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod2,
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 3 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod3,
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 4 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod4,
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 5 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod5,
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 6 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod6,
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 7 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod7,
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 8 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod8,
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 9 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod9,
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 10 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod10,
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 11 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod11,
                      MAX(CASE WHEN OPA_Procedures.ProcedureOrder = 12 THEN OPA_Procedures.OPCS4ProcedureCode ELSE NULL END) AS prod12
FROM         A_H_5_R.dbo.OPA_Procedures RIGHT OUTER JOIN
                      A_H_5_R.dbo.#results OPA_General ON
                      A_H_5_R.dbo.OPA_Procedures.OPAJoinKey = OPA_General.OPAJoinKey LEFT OUTER JOIN
                      A_H_5_R.dbo.OPA_D ON
                      A_H_5_R.dbo.OPA_General.OPAJoinKey = A_H_5_R.dbo.OPA_D.OPAJoinKey LEFT OUTER JOIN
                      HRG4_2010.VW_O_C_2010 ON OPA_General.OPAJoinKey = HRG4_2010.VW_O_C_2010.OPAJoinkey
                     
WHERE     (HRG4_2010.VW_O_C_2010.BatchID IS NULL)
GROUP BY OPA_General.OPAJoinKey
Awarded 2008
Awarded 2008

Commented:
the cost of what?  cost is a relative figure

Author

Commented:
thanks... I learnt a lot from the discussion.

I'll do some research on the impact of LEFT JOINS, NOT IN()s, NOT EXIST(), wildcards and rowcounts on the queryplans.