*can anyone help me optimizing the query. I am using this query in an ETL called streamsets and it is yielding 70 records for 6 minutes when i run an streamsets pipleline which is very slow.we are taking this query from an SSIS package and joining each of the tables using left outer joins.I need to optimize it so that it fetches atleast 1000 records per minute in streamsets *
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'
