Оптимизация запросов SQl
* может кто-нибудь помочь мне оптимизировать запрос. Я использую этот запрос в ETL, называемом streamsets, и он дает 70 записей в течение 6 минут, когда я запускаю очень медленный конвейер потоковых наборов. Мы берем этот запрос из пакета служб SSIS и соединяем каждую из таблиц, используя левые внешние соединения. Мне нужно оптимизировать его так, чтобы он выбирал как минимум 1000 записей в минуту в наборе потоков *
SELECT [FR].[ORDER_ID]
, [FR].[FULFILLMENT_REQUEST_ID]
, [SR].[SCREENING_RESULT_ID]
, [AT].[AG_TASK_ID]
, [RCC].[RESULT_CRIM_CASE_ID]
, [RCC].[RESULT_CRIM_CHARGE_ID]
, [FR].[JURISDICTION_ID]
, [COUNTY] = CASE WHEN [FR].[PRODUCT_ID] = 2 THEN [JC].[COUNTY_NAME] ELSE '' END
, [STATE] = CASE WHEN [FR].[PRODUCT_ID] = 2 THEN [JC].[STATE_A2_CD] ELSE [JS].[STATE_A2_CD] END
, [JT].[JURISDICTION_TYPE_NAME]
, [FR].[PRODUCT_ID]
, [P].[PRODUCT_NAME]
, [FR].[ORGANIZATION_ID]
, [O].[ORGANIZATION_NAME]
, [SA].[SALARY_RANGE]
, [AGE] = YEAR(GETDATE()) - YEAR([FR].[SUBJECT_DOB])
, [FR].[SUBJECT_JOB_COUNTRY]
, [FR].[SUBJECT_JOB_STATE]
, [FR].[ENTRY_DATE]
, [FR].[CREATION_DATE]
, [FR].[CLOSED_DATE]
, [FR].[EXTERNAL_USER_ID]
, [FR].[GENDER_CODE_ID]
, [GC].[GENDER_CODE_VALUE]
, [AT].[DATA_SOURCE]
, [SU].[RESEARCHER_CLASS]
, [TT].[TASK_TYPE]
, [TT].[TASK_DESCRIPTION]
, [RCC].[CHARGE_DESCRIPTION]
, [RCC].[DISPOSITION_DESCRIPTION]
, [P].[GENERAL_LEDGER_NBR]
, [O].[SCHEME_AGENCY_ID]
, [RT].[RESULT_TYPE_ID]
, [RT].[RESULT_CATEGORY]
, [RT].[RESULT_TYPE]
, [SU].[USER_ID]
,FR.AG_STATUS_ID
, [SCREENING_REFERENCE_ID]=CAST(LEFT([FR].[SCREENING_REFERENCE_ID],250) AS VARCHAR(8000))
,[P1].POD_NAME
, ATH.AG_TASK_HISTORY_ID
,ATH.TASK_STATUS TASK_HISTORY_STATUS
, ATH.AG_TASK_DATE TASK_HISTORY_DATE
,[TT].[TASK_TYPE_ID]
,ATH.MODIFIED_BY_SYSTEM_USER_ID
,ATH.CF_SYSTEM_USER_ID
,'' AS [STATUS NOTES]
, FR.SCOPE_OF_SEARCH
, SR.COMMONNAMFLAG
,CASE WHEN SU.RESEARCHER_CLASS = 'EXTERNAL' THEN SU.[USER_ID] END AS VENDOR
, CASE [TT].TASK_TYPE_ID WHEN 14 THEN 'SD IN' WHEN 15 THEN 'MR' WHEN 1 THEN 'ER' END AS [REQUIRED_ACTION]
, CASE
WHEN [TT].TASK_TYPE_ID = 1 THEN
CASE ATH.TASK_STATUS
WHEN 'DOCUMENTUPLOADED' THEN 'FMT RECORD ENTRY'
WHEN 'ACKNOWLEDGED' THEN 'SEARCHES'
WHEN 'AWAITINGHITENTRY' THEN 'AWAITING HIT'
WHEN 'DISPUTE' THEN 'AWAITING HIT'
WHEN 'DOUBLEENTERRESULTS' THEN 'AWAITING HIT'
WHEN 'NEW' THEN 'SEARCHES'
WHEN 'SOURCEUNAVAILABLE' THEN 'SEARCHES'
WHEN 'UPLOADED' THEN 'SEARCHES'
END
WHEN [TT].TASK_TYPE_ID = 15 THEN
CASE ATH.TASK_STATUS
WHEN 'DISPUTE' THEN 'QC'
WHEN 'DOCUMENTUPLOADED' THEN 'QC'
WHEN 'DOUBLESMARTDATAREVIEW' THEN 'QC'
WHEN 'MANUALREVIEW' THEN 'QC'
END
WHEN [TT].TASK_TYPE_ID = 14 THEN
CASE ATH.TASK_STATUS
WHEN 'DISPUTE' THEN 'QC'
WHEN 'INFONEEDED' THEN 'QC'
WHEN 'INFOPROVIDED' THEN 'QC'
WHEN 'INFONEEDEDACK' THEN 'QC'
WHEN 'NEW' THEN 'QC'
WHEN 'SDINFONEEDED' THEN 'CD COMPLIANCE'
END
END AS [AOM STATUS]
, CASE
WHEN [TT].TASK_TYPE_ID = 1 AND
ATH.TASK_STATUS = 'DOCUMENTUPLOADED' AND
ATH.MODIFIED_BY_SYSTEM_USER_ID IS NOT NULL AND
ATH.CF_SYSTEM_USER_ID IS NOT NULL THEN 'ER DOCUMENT UPLOADED COMPLETE'
WHEN [TT].TASK_TYPE_ID = 14 AND
ATH.TASK_STATUS = 'INFONEEDED' THEN 'SD INFO NEEDED INFO NEEDED DATA'
WHEN [TT].TASK_TYPE_ID = 1 AND SU.RESEARCHER_CLASS = 'INTERNAL' AND
FR.AG_STATUS_ID NOT IN (152) THEN 'ER INTERNAL'
WHEN [TT].TASK_TYPE_ID = 1 AND
ATH.TASK_STATUS = 'AWAITINGHITENTRY' AND
ATH.MODIFIED_BY_SYSTEM_USER_ID IS NOT NULL AND
ATH.CF_SYSTEM_USER_ID IS NOT NULL AND
ATH.MODIFIED_BY_SYSTEM_USER_ID = ATH.CF_SYSTEM_USER_ID THEN 'ER AWAITING HIT ENTRY DATA'
WHEN [TT].TASK_TYPE_ID = 14 THEN 'SD INFO NEEDED DATA'
WHEN [TT].TASK_TYPE_ID = 15 AND
ATH.TASK_STATUS = 'INFONEEDED' THEN 'MR INFO NEEDED'
END AS AG_OPS_STATUS
, FR.EXTERNAL_ORDER_ID
, AOT.OTD
, drv_pod_name=[P1].POD_NAME
, TAT_IN_MIN= Case when TAT_IN_MIN < 0 then 0 else TAT_IN_MIN end
, SU2.USER_ID CF_SYS_USER
, SU3.USER_ID MODIFIED_SYS_USER
, GETDATE() GET_DATE
FROM AG_TASK_HISTORY [ATH] LEFT OUTER JOIN
(
(
(
(
(
(
(
(
(
SELECT [ORDER_ID] ,[FULFILLMENT_REQUEST_ID],[JURISDICTION_ID],[PRODUCT_ID],[ORGANIZATION_ID],[SUBJECT_DOB],[SUBJECT_JOB_COUNTRY],[SUBJECT_JOB_STATE],[ENTRY_DATE],[CREATION_DATE],[CLOSED_DATE],[EXTERNAL_USER_ID],[GENDER_CODE_ID],AG_STATUS_ID,[SCREENING_REFERENCE_ID],SCOPE_OF_SEARCH,EXTERNAL_ORDER_ID,POD_ID,[SALARY_RANGE_ID],[JURISDICTION_TYPE]
FROM [FULFILLMENT_REQUEST]
) FR
LEFT JOIN
(
(SELECT [SCREENING_RESULT_ID],[RESULT_TYPE_ID],[FULFILLMENT_REQUEST_ID],COMMONNAMFLAG FROM [SCREENING_RESULT] )[SR]
INNER JOIN [DBO].[RESULT_TYPE] [RT]
ON [RT].[RESULT_TYPE_ID] = [SR].[RESULT_TYPE_ID]
)
ON [SR].[FULFILLMENT_REQUEST_ID] = [FR].[FULFILLMENT_REQUEST_ID]
)
INNER JOIN [PRODUCT] [P]
ON [P].[PRODUCT_ID] = [FR].[PRODUCT_ID]
INNER JOIN [ORGANIZATION] [O]
ON [O].[ORGANIZATION_ID] = [FR].[ORGANIZATION_ID]
LEFT JOIN(
(SELECT [AG_TASK_ID],[DATA_SOURCE],[TASK_TYPE_ID],[FULFILLMENT_REQUEST_ID],[SYSTEM_USER_ID] FROM [AG_TASK] ) [AT]
INNER JOIN [TASK_TYPE] [TT]
ON [TT].[TASK_TYPE_ID] = [AT].[TASK_TYPE_ID]
)
ON [AT].[FULFILLMENT_REQUEST_ID] = [FR].[FULFILLMENT_REQUEST_ID])
LEFT OUTER JOIN (
(SELECT [RESULT_CRIM_CASE_ID],[SCREENING_RESULT_ID] FROM [RESULT_CRIM_CASE])[RC]
INNER JOIN (SELECT [RESULT_CRIM_CASE_ID],[RESULT_CRIM_CHARGE_ID],[CHARGE_DESCRIPTION],[DISPOSITION_DESCRIPTION] FROM [RESULT_CRIM_CHARGE]) [RCC]
ON [RCC].[RESULT_CRIM_CASE_ID] = [RC].[RESULT_CRIM_CASE_ID]
)
ON [SR].[SCREENING_RESULT_ID] = [RC].[SCREENING_RESULT_ID]
)
)
LEFT JOIN [SYSUSER] [SU]
ON [SU].[SYSTEM_USER_ID] = [AT].[SYSTEM_USER_ID]
LEFT OUTER JOIN [SALARY_RANGE] [SA]
ON [SA].[SALARY_RANGE_ID] = [FR].[SALARY_RANGE_ID]
)
LEFT OUTER JOIN [JURISDICTION_TYPE] [JT]
ON [JT].[JURISDICTION_TYPE_ID] = [FR].[JURISDICTION_TYPE]
)
LEFT OUTER JOIN [GENDER_CODE] [GC]
ON [GC].[GENDER_CODE] = [FR].[GENDER_CODE_ID]
)
LEFT OUTER JOIN [JURISDICTION_COUNTY] [JC]
ON [JC].[JURISDICTION_ID] = [FR].[JURISDICTION_ID]
LEFT OUTER JOIN [JURISDICTION_STATE] [JS]
ON [JS].[JURISDICTION_ID] = [FR].[JURISDICTION_ID]
)
ON [ATH].AG_TASK_ID=[AT].AG_TASK_ID
LEFT OUTER JOIN [POD] [P1] ON [FR].POD_ID = [P1].POD_ID
LEFT OUTER JOIN(
SELECT *, (DATEDIFF(MINUTE, [IN_DATE], [IP_DATE]))-(DATEDIFF(WK, [IN_DATE], [IP_DATE]) * (2*24*60))-
(CASE WHEN DATENAME(DW, [IN_DATE]) = 'SUNDAY'
THEN (24*60) ELSE 0 END)
-(CASE WHEN DATENAME(DW, [IP_DATE]) = 'SATURDAY'
THEN (24*60) ELSE 0 END) TAT_IN_MIN
FROM (
SELECT FULFILLMENT_REQUEST_ID , MAX([IN_DATE]) [IN_DATE], MAX([IP_DATE]) [IP_DATE]
FROM(
SELECT AG_TASK_HISTORY.FULFILLMENT_REQUEST_ID,
CASE WHEN (AG_TASK_HISTORY.TASK_STATUS='INFONEEDED' OR AG_TASK_HISTORY.TASK_STATUS='NEW') AND (SYSUSER.RESEARCHER_CLASS='EXTERNAL' OR SU1.RESEARCHER_CLASS ='EXTERNAL')
THEN AG_TASK_HISTORY.AG_TASK_DATE END [IN_DATE],
CASE WHEN (AG_TASK_HISTORY.TASK_STATUS='INFOPROVIDED' OR AG_TASK_HISTORY.TASK_STATUS='COMPLETE' OR AG_TASK_HISTORY.TASK_STATUS='DOCUMENTUPLOADED') AND (SYSUSER.RESEARCHER_CLASS='EXTERNAL' OR SU1.RESEARCHER_CLASS ='EXTERNAL')
THEN AG_TASK_HISTORY.AG_TASK_DATE END AS [IP_DATE]
FROM AG_TASK_HISTORY
LEFT JOIN SYSUSER(NOLOCK) ON AG_TASK_HISTORY.CF_SYSTEM_USER_ID = SYSUSER.SYSTEM_USER_ID
LEFT JOIN SYSUSER(NOLOCK) SU1 ON AG_TASK_HISTORY.MODIFIED_BY_SYSTEM_USER_ID = SU1.SYSTEM_USER_ID
WHERE AG_TASK_DATE >=CAST('01-JAN-'+CAST(YEAR(GETDATE())-3 AS CHAR(4))AS DATETIME)
) A GROUP BY FULFILLMENT_REQUEST_ID
)B
)AVT
ON FR.FULFILLMENT_REQUEST_ID = AVT.FULFILLMENT_REQUEST_ID
LEFT OUTER JOIN(
SELECT *, (DATEDIFF(DAY, [IN_DATE], [IP_DATE]))-
(DATEDIFF(WK, [IN_DATE], [IP_DATE]) * (2))-
(CASE WHEN DATENAME(DW, [IN_DATE]) = 'SUNDAY' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(DW, [IP_DATE]) = 'SATURDAY' THEN 1 ELSE 0 END) OTD
FROM
(
SELECT FULFILLMENT_REQUEST_ID , MIN(CREATION_DATE) [IN_DATE], GETDATE() [IP_DATE]
FROM FULFILLMENT_REQUEST WHERE CLOSED_DATE IS NULL AND AG_STATUS_ID NOT IN (29,134,142,152)
GROUP BY FULFILLMENT_REQUEST_ID
)B
) AOT
ON FR.FULFILLMENT_REQUEST_ID = AOT.FULFILLMENT_REQUEST_ID
LEFT OUTER JOIN SYSUSER SU2
ON ATH.CF_SYSTEM_USER_ID=SU2.SYSTEM_USER_ID
LEFT OUTER JOIN SYSUSER SU3
ON ATH.MODIFIED_BY_SYSTEM_USER_ID=SU3.SYSTEM_USER_ID
WHERE SU.RESEARCHER_CLASS='EXTERNAL' AND (AVT.TAT_IN_MIN >0 OR AOT.[IP_DATE] IS NULL)
AND ATH.AG_Task_Date >=' 2017-07-07 09:02:23.050'
2 ответа
Если вы объединяете таблицы и выполняете выборку, вы действительно должны знать свои индексы. Используйте объяснение, чтобы проверить, есть ли проблема с использованием индексов. Возможно, что запрос не может быть оптимизирован и должен быть полностью переработан.
Очень сложно дать вам определенный ответ на ваш вопрос, потому что ваш запрос огромен, и его анализ займет много времени.
Вот несколько советов для вас:
- Получите план выполнения для него (скопируйте его в SMSS и нажмите CTRL+L), который может дать вам подсказки, какие индексы отсутствуют
- Удалите все столбцы из списка выбора и оставьте просто SELECT COUNT(*) FROM AG_TASK_HISTORY .... Проверьте, все еще ли у вас плохая производительность.
- Затем идите снизу и исключите соединение соединением. После каждого устранения посмотрите, если производительность все еще плохая.
- После некоторых устранений вы, вероятно, должны хорошо провести время, а это значит, что вы нашли как минимум 1 узкое место.
- Затем создайте для этого подходящий индекс и повторите попытку, начиная с шага 2.
Другие соображения заключаются в том, чтобы использовать хранимые процедуры (если это возможно) и разбивать этот большой запрос на более мелкие части, но я вполне уверен, что при правильном наборе индексов вы можете получить гораздо лучшую производительность, чем сейчас.