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.