Sql-server – Why does the first query scan and the second seek

execution-planindex-tuningsql serversql-server-2008-r2

Please could somebody explain why the first query below scans on every join to the REFERENCE_VALUES table but the second query seeks on the same joins?

The first query does have other joins, but I don't see why that should cause this to happen. The join column RFVAL_REFNO is a unique clustered index and PK.

Query 1:

DECLARE @PATNT_REFNO NUMERIC(10,0) = 515938

SELECT  
'Outpatient' AS TIMELINE_TYPE,
SCHEDULES.SCHDL_REFNO,
ISNULL(SCHEDULES.START_DTTM, SCHEDULES.ARRIVED_DTTM) AS START_DTTM,
REF_VISIT.DESCRIPTION VISIT_TYPE,
SERVICE_POINTS.CODE AS CLINIC_CODE,
SERVICE_POINTS.DESCRIPTION AS CLINIC_DESC,    
SCHEDULES.COMMENTS AS COMMENTS,
SERVICE_POINT_SESSIONS.CODE AS SESSION_CODE,
ISNULL(PROF_CARERS.FORENAME,'') + ' ' + ISNULL(PROF_CARERS.SURNAME,'') AS CLINICIAN,
REF_ATTND.DESCRIPTION AS ATTEND_TYPE,   
CASE REF_ATTND.DESCRIPTION
      WHEN 'Cancelled' THEN 'Cancelled'
      ELSE REF_SCOCM.DESCRIPTION
END AS SCHEDULED_OUTCOME ,  
CASE REF_ATTND.DESCRIPTION
      WHEN 'Cancelled' THEN REF_CANCB.DESCRIPTION 
      ELSE ''
END AS CANCB_DESC,    
[SPECIALTIES].MAIN_IDENT AS SPEC_CODE,
[SPECIALTIES].DESCRIPTION AS SPEC_DESC,
SPECIALTIES.SPECT_REFNO                               
FROM      
[IE_PAS].[dbo].[SCHEDULES]

LEFT JOIN [IE_PAS].[dbo].[PROF_CARERS] ON SCHEDULES.PROCA_REFNO = PROF_CARERS.PROCA_REFNO
LEFT JOIN [IE_PAS].[dbo].[SPECIALTIES] ON SCHEDULES.SPECT_REFNO = SPECIALTIES.SPECT_REFNO


LEFT JOIN [IE_PAS].[dbo].[SERVICE_POINTS] ON SCHEDULES.SPONT_REFNO = SERVICE_POINTS.SPONT_REFNO
LEFT JOIN [IE_PAS].[dbo].[SERVICE_POINT_SESSIONS] ON SCHEDULES.SPSSN_REFNO = SERVICE_POINT_SESSIONS.SPSSN_REFNO
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_VISIT ON SCHEDULES.VISIT_REFNO = REF_VISIT.RFVAL_REFNO               
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_ATTND ON SCHEDULES.ATTND_REFNO = REF_ATTND.RFVAL_REFNO   
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_SCOCM ON SCHEDULES.SCOCM_REFNO = REF_SCOCM.RFVAL_REFNO
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_CANCB ON SCHEDULES.CANCB_REFNO = REF_CANCB.RFVAL_REFNO   


--LEFT JOIN #AuthorisedDNF LETTERS WITH (INDEX(#AuthorisedDNF_IX)) ON SCHEDULES.SCHDL_REFNO = LETTERS.LINK
--LEFT JOIN #CLINICAL_NOTES CLINICAL_NOTES_OP WITH (INDEX(#CLINICAL_NOTES_IX)) ON CAST(SCHEDULES.SCHDL_REFNO AS VARCHAR(10)) = CLINICAL_NOTES_OP.ACTIVITY_REFERENCE   
--                                                                          AND CLINICAL_NOTES_OP.ACTIVITY_TYPE = 'SCHDL_REFNO'
--                                                                          AND CLINICAL_NOTES_OP.AD_HOC_FLAG = 0
WHERE 
SCHEDULES.PATNT_REFNO = @PATNT_REFNO AND
ISNULL(SCHEDULES.ARCHV_FLAG, 'N') <> 'Y' AND
ISNULL(SERVICE_POINTS.ARCHV_FLAG, 'N') <> 'Y' AND
ISNULL(SERVICE_POINT_SESSIONS.ARCHV_FLAG, 'N') <> 'Y' AND
ISNULL(REF_VISIT.ARCHV_FLAG, 'N') <> 'Y' AND          
ISNULL(REF_ATTND.ARCHV_FLAG, 'N') <> 'Y' AND    
ISNULL(REF_SCOCM.ARCHV_FLAG, 'N') <> 'Y' AND
ISNULL(PROF_CARERS.ARCHV_FLAG, 'N') <> 'Y'

Query 2:

SELECT  
'Outpatient' AS TIMELINE_TYPE,
SCHEDULES.SCHDL_REFNO,
ISNULL(SCHEDULES.START_DTTM, SCHEDULES.ARRIVED_DTTM) AS START_DTTM,
REF_VISIT.DESCRIPTION VISIT_TYPE, 
SCHEDULES.COMMENTS AS COMMENTS,
REF_ATTND.DESCRIPTION AS ATTEND_TYPE,   
CASE REF_ATTND.DESCRIPTION
      WHEN 'Cancelled' THEN 'Cancelled'
      ELSE REF_SCOCM.DESCRIPTION
END AS SCHEDULED_OUTCOME ,  
CASE REF_ATTND.DESCRIPTION
      WHEN 'Cancelled' THEN REF_CANCB.DESCRIPTION 
      ELSE ''
END AS CANCB_DESC                          
FROM      
[IE_PAS].[dbo].[SCHEDULES]
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_VISIT ON SCHEDULES.VISIT_REFNO = REF_VISIT.RFVAL_REFNO               
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_ATTND ON SCHEDULES.ATTND_REFNO = REF_ATTND.RFVAL_REFNO   
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_SCOCM ON SCHEDULES.SCOCM_REFNO = REF_SCOCM.RFVAL_REFNO
LEFT JOIN [IE_PAS].[dbo].[REFERENCE_VALUES] REF_CANCB ON SCHEDULES.CANCB_REFNO = REF_CANCB.RFVAL_REFNO      
WHERE 
SCHEDULES.PATNT_REFNO = @PATNT_REFNO AND
ISNULL(SCHEDULES.ARCHV_FLAG, 'N') <> 'Y' AND
ISNULL(REF_VISIT.ARCHV_FLAG, 'N') <> 'Y' AND          
ISNULL(REF_ATTND.ARCHV_FLAG, 'N') <> 'Y' AND    
ISNULL(REF_SCOCM.ARCHV_FLAG, 'N') <> 'Y'

I rebuilt the index on SERVICE_POINT_SESSIONS and now the 2 queries seek on the REFERENCE_VALUES table. I picked that one to rebuild as the execution plan was estimating lots of rows would be returned when not many were actually being returned. A bit of insight would still be appreciated.

Best Answer

You said you rebuilt the index and now both queries are performing index seeks, as desired.

This is most likely due to the index rebuild operation recreating the statistics for the affected columns. Now that that query optimizer has updated statistics, it knows it would be more efficient to seek the index.

You may want to ensure you have an index rebuild job that runs on a schedule - I'd recommend looking at Ola Hallengren's solution for that.

Related Solutions

Sql-server – How (and why) does TOP impact an execution plan

I would have guessed that when a query includes TOP n the database engine would run the query ignoring the the TOP clause, and then at the end just shrink that result set down to the n number of rows that was requested. The graphical execution plan seems to indicate this is the case -- TOP is the "last" step. But it appears there is more going on.

The way the above is phrased makes me think you may have an incorrect mental picture of how a query executes. An operator in a query plan is not a step (where the full result set of a previous step is evaluated by the next one.

SQL Server uses a pipelined execution model, where each operator exposes methods like Init(), GetRow(), and Close(). As the GetRow() name suggests, an operator produces one row at a time on demand (as required by its parent operator). This is documented in the Books Online Logical and Physical Operators reference, with more detail in my blog post Why Query Plans Run Backwards. This row-at-a-time model is essential in forming a sound intuition for query execution.

My question is, how (and why) does a TOP n clause impact the execution plan of a query?

Some logical operations like TOP, semi joins and the FAST n query hint affect the way the query optimizer costs execution plan alternatives. The basic idea is that one possible plan shape might return the first n rows more quickly than a different plan that was optimized to return all rows.

For example, indexed nested loops join is often the fastest way to return a small number of rows, though hash or merge join with scans might be more efficient on larger sets. The way the query optimizer reasons about these choices is by setting a Row Goal at a particular point in the logical tree of operations.

A row goal modifies the way query plan alternatives are costed. The essence of it is that the optimizer starts by costing each operator as if the full result set were required, sets a row goal at the appropriate point, and then works back down the plan tree estimating the number of rows it expects to need to examine to meet the row goal.

For example, a logical TOP(10) sets a row goal of 10 at a particular point in the logical query tree. The costs of operators leading up to the row goal are modified to estimate how many rows they need to produce to meet the row goal. This calculation can become complex, so it is easier to understand all this with a fully worked example and annotated execution plans. Row goals can affect more than the choice of join type or whether seeks and lookups are preferred to scans. More details on that here.

As always, an execution plan selected on the basis of a row goal is subject to the optimizer's reasoning abilities and the quality of information provided to it. Not every plan with a row goal will produce the required number of rows faster in practice, but according to the costing model it will.

Where a row goal plan proves not to be faster, there are usually ways to modify the query or provide better information to the optimizer such that the naturally selected plan is best. Which option is appropriate in your case depends on the details of course. The row goal feature is generally very effective (though there is a bug to watch out for when used in parallel execution plans).

Your particular query and plan may not be suitable for detailed analysis here (by all means provide an actual execution plan if you wish) but hopefully the ideas outlined here will allow you to make forward progress.

Postgresql – Change table scan in query plan to seek

I'm not a Postgres guy, but with other DBs this can be a problem with the table statistics that the query planner relies on. Resetting those via an

ANALYZE TABLE

can resolve the issue. Your queries & indexes look correct.

http://www.postgresql.org/docs/9.0/static/sql-analyze.html

Best Answer

Related Solutions

Sql-server – How (and why) does TOP impact an execution plan

Postgresql – Change table scan in query plan to seek

Related Question