Почему инструкция SELECT должна составлять 45% стоимости плана выполнения в SQL Server 2008?

ИДЕЯ ДЛЯ Ускорения

Я отредактировал ваш запрос (я не тестировал, поэтому могут быть опечатки), чтобы избавиться от подзапросов. Здесь вы сначала получаете все максимальные элементы (1 x количество empl), а затем запускаете основной выбор (1 x количество empl). Это изменяет ваш запрос с O (N ^ 3) на O (N), поэтому он должен быть быстрее.

Я сделал только два из них, третий должен быть понятен из этого примера:

WITH mVisa AS
(
 SELECT MAX(VISA_CURRENT.EFFDT) as max, VISA_CURRENT.EMPID as EMPLID
 FROM VISA_CURRENT
 WHERE VISA_CURRENT.EFFDT <= GETDATE()
 GROUP BY VISA_CURRENT.EMPLID
), mPers AS
(
 SELECT MAX(PERS_CURRENT.EFFDT) as max, PERS_CURRENT.EMPLID
 FROM PERS_CURRENT
 AND PERS_CURRENT.EFFDT <= GETDATE())
 GROUP BY PERS_CURRENT.EMPLID
)
SELECT
/* Names */
NM.EMPLID, NM.NAME_PREFIX, NM.LAST_NAME, NM.FIRST_NAME, NM.MIDDLE_NAME, NM.NAME_SUFFIX,
/* Directory Info */
DIR_PERSON.BIRTH_DT,
/* PERSDATA */
PERS.SEX, PERS.HIGHEST_EDUC_LVL,
/* DIVERS.ETHNIC */
ETHNIC.ETHNIC_GRP_CD,
/* TENURE */
TENURE.EMPLID, TENURE.TENURE_STATUS, TENURE.EG_GRANTED_DT, TENURE.EG_TENURE_HOME, 
TENURE.EG_TRACK_HIRE_DT, TENURE.EG_MAND_REVW_DT, TENURE.CODE,
/* VISA */
VISA.VISA_PERMIT_TYPE

FROM NAMES NM

/* ----- Table Joins ----- */
/* Directory Join */
LEFT OUTER JOIN DIR_PERSON ON DIR_PERSON.ID = NM.EMPLID

/* PERS_DATA Join */
LEFT JOIN mPers ON NM.EMPLID = mPers.EMPLID 
LEFT OUTER JOIN PERS ON PERS.EMPLID = NM.EMPLID
AND PERS.EFFDT = mPers.max
/* ETHNIC Join */                                       
LEFT OUTER JOIN  ETHNIC ON ETHNIC.EMPLID = NM.EMPLID
AND ETHNIC.PRIMARY_INDICATOR = 'Y'

/* TENURE Join */
LEFT OUTER JOIN TENURE ON TENURE.EMPLID = NM.EMPLID

/* VISA Join */
LEFT JOIN mVisa ON NM.EMPLID = mVisa.EMPLID
LEFT OUTER JOIN VISA ON VISA.EMPLID = NM.EMPLID
AND VISA.EFFDT = mVisa.max

/* ----- End Table Joins ----- */       

WHERE   NM.NAME_TYPE = 'PRI' 
        AND NM.EFFDT = (SELECT MAX(NM_CURRENT.EFFDT) FROM NM_CURRENT 
                                        WHERE NM.EMPLID = NM_CURRENT.EMPLID 
                                        AND NM.NAME_TYPE = NM_CURRENT.NAME_TYPE 
                                        AND NM_CURRENT.EFFDT <= GETDATE());

Как сказал Кейд, сначала проверьте свои индексы.

Если индексы есть, убедитесь, что ваша статистика актуальна.

Если обе эти проблемы решаются, рассмотрите возможность рефакторинга ваших подзапросов в один или несколько CTE, а затем присоединитесь к ним по соответствующим критериям. Это не серебряная пуля, но, по моему опыту, CTE часто работают лучше, чем подзапросы.

45% чего-то маленького по-прежнему 45%. Трудно сказать, не увидев более подробностей, но я обнаружил, что заключительные этапы очень дороги при вставке в кластеризованную (по столбцам, отличным от IDENTITY) индексную таблицу или таблицу с большим количеством индексов.

При всех этих просмотрах таблиц - разве нет индексов?

Было бы полезно, если бы вы включили код, но если у вас, например, GROUP BY или ORDER BY, то это значительно добавит к запросу.

Если окончательный выбор - это большая таблица, а остальные не только крошечные, но и не очень часто используются в основной таблице, тогда вам нужно достичь 100% в какой-то части запроса, даже если это простая часть.

WHERE   NM.NAME_TYPE = 'PRI' 
        AND NM.EFFDT = (SELECT MAX(NM_CURRENT.EFFDT) FROM NM_CURRENT 
                                        WHERE NM.EMPLID = NM_CURRENT.EMPLID 
                                        AND NM.NAME_TYPE = NM_CURRENT.NAME_TYPE 
                                        AND NM_CURRENT.EFFDT <= GETDATE());

Ваши 45% здесь. Если вы создадите индекс для MN.NAME_TYPE и NM.EFFDT, вы увидите это падение на 45%.

Он может включать или не включать этот подзапрос в стоимость основного выбора - если это так, то именно в этом и заключается ваша проблема. ПОМНИТЕ, что он должен повторно запускать этот запрос для каждой строки.

См. Другой комментарий для рефакторинга соединения.