Запрос, требующий слишком много времени для выполнения : оптимизируйте запрос select
Привет ,
У меня есть запрос написанный в довольно старомодной манере в магазине Proc
Магазин Proc использует 2 таблицы, FOLLOWUP_DETAILS, который имеет 231352 записи и TELE_ENQUIRIES, который имеет 91727 записей.
Фильтр поиска просто делается на основе диапазона дат 1 месяц
Например
EXECUTE [dbo].[USP_SEARCH_TELE_ENQUIRIES] @FROM_DATE ='2016-05-27 00:00:00.000' ,@TO_DATE ='2016-06-27 00:00:00.000'
который обычно возвращает 2000 нечетных записей, но для его выполнения требуется чертовски много времени.
Пожалуйста, найдите и предложите мне способ оптимизировать результат.
Если мне нужно поставить индексацию, то на какой столбец мне нужно поставить индексы ?
Hi I have below query written in procedure :
CREATE PROCEDURE [dbo].[USP_SEARCH_TELE_ENQUIRIES]
(
@FROM_DATE CHAR(10)=''
,@TO_DATE CHAR(10)=''
,@FIRSTNAME VARCHAR(50)=''
,@LASTNAME VARCHAR(50)=''
,@COURSE_INTERESTED VARCHAR(50)=''
,@SOURCE VARCHAR(50)=''
,@CENTRE_ID INT=-1
,@SUGGESTED_CENTRE_ID INT=-1
,@STREAM VARCHAR(50)=''
,@ISENROLLED INT=-1
,@WALKIN INT=-1
,@EMP_FNAME VARCHAR(50)=''
,@EMP_LASTNAME VARCHAR(50)=''
,@ISOUTBOUND INT=0
,@ISHO INT=0
,@SOURCE_NAME VARCHAR(50)=''
,@ISCENTERLEAD INT=0
,@LOCATION VARCHAR(50)=''
,@LEAD_TARGET VARCHAR(50)=''
,@QUALIFICATION VARCHAR(50)=''
,@MOBILE NCHAR(30)=''
)
AS BEGIN
IF @LOCATION='seo'
BEGIN
SELECT [TELE_ENQUIRIES].[TELE_ENQUIRY_ID]
,[TELE_ENQUIRIES].[ENQUIRY_DATE]
,[TELE_ENQUIRIES].[FIRSTNAME]+' '+[TELE_ENQUIRIES].[MIDDLENAME]+' '+[TELE_ENQUIRIES].[LASTNAME] AS NAME
,[TELE_ENQUIRIES].[TELEPHONE]
,[TELE_ENQUIRIES].[MOBILE]
,[TELE_ENQUIRIES].[COURSE_INTERESTED]
,[TELE_ENQUIRIES].[STREAM]
,[TELE_ENQUIRIES].[SOURCE]
,[TELE_ENQUIRIES].[SUGGESTED_CENTRE_ID]
,[CENTRE_MASTER_1].[CENTRE_NAME] AS CENTRE_NAME
,[TELE_ENQUIRIES].[REMARKS]
,[TELE_ENQUIRIES].[ISENROLLED]
,[TELE_ENQUIRIES].[ADMISSION_ID]
,[TELE_ENQUIRIES].[CENTRE_ID]
,CENTRE_MASTER.[CENTRE_NAME] AS SUGGESTED_CENTRE_NAME
,[TELE_ENQUIRIES].[WALKIN]
,[TELE_ENQUIRIES].[ENQUIRY_HANDELED_BY]
,NULL AS HANDELED_BY
,NULL AS [FOLLOWUP_DETAILS]
,[CREATED_ON]
,[ENQUIRY_ID]
,NULL AS [FOLLOWUP_DATE]
,NULL AS [NEXT_FOLLOWUP_DATE]
,SOURCE_NAME
,NULL AS FOLLOWUP_BY
,LOCATION
,LEAD_TARGET
,QUALIFICATION
,EMAIL_ID
FROM [MISONLINE_NEW].[dbo].[TELE_ENQUIRIES]
TELE_ENQUIRIES INNER JOIN
CENTRE_MASTER AS CENTRE_MASTER_1 ON CENTRE_MASTER_1.CENTRE_ID = TELE_ENQUIRIES.CENTRE_ID INNER JOIN
CENTRE_MASTER ON TELE_ENQUIRIES.SUGGESTED_CENTRE_ID = CENTRE_MASTER.CENTRE_ID LEFT OUTER JOIN
EMPLOYEE_MASTER ON TELE_ENQUIRIES.ENQUIRY_HANDELED_BY = EMPLOYEE_MASTER.EMPLOYEE_ID
WHERE
((@FROM_DATE='') OR (ENQUIRY_DATE BETWEEN @FROM_DATE AND @TO_DATE))
AND ([FIRSTNAME] like @FIRSTNAME + '%')
AND ([LASTNAME] LIKE @LASTNAME +'%')
AND ([COURSE_INTERESTED] LIKE @COURSE_INTERESTED+'%')
AND ((@SOURCE='') OR ([SOURCE] = @SOURCE))
AND ((@CENTRE_ID=-1) OR ([TELE_ENQUIRIES].CENTRE_ID=@CENTRE_ID))
AND ((@SUGGESTED_CENTRE_ID=-1) OR ([TELE_ENQUIRIES].[SUGGESTED_CENTRE_ID]=@SUGGESTED_CENTRE_ID))
AND ((@STREAM='') OR ([TELE_ENQUIRIES].[STREAM]=@STREAM))
AND ((@ISENROLLED=-1) OR ([TELE_ENQUIRIES].[ISENROLLED]=@ISENROLLED))
AND ((@WALKIN=-1) OR ([TELE_ENQUIRIES].[WALKIN]=@WALKIN))
AND (EMPLOYEE_MASTER.EMP_FNAME LIKE @EMP_FNAME+'%')
AND (EMPLOYEE_MASTER.EMP_LASTNAME LIKE @EMP_LASTNAME+'%')
AND ((@ISOUTBOUND=0 AND SOURCE<>'OUTBOUND') OR @ISOUTBOUND=1)
AND ((@ISHO=0 AND [SUGGESTED_CENTRE_ID]<>155) OR @ISHO=1)
--AND (SOURCE_NAME LIKE @SOURCE_NAME+'%') --this line has been commented because it is the one trick used while optimization to increase the performance of the report by pushpak
--AND ((@ISCENTERLEAD=0 AND SOURCE<>'Center Leads') OR @ISCENTERLEAD=1) --this line has been commented because it is the one trick used while optimization to increase the performance of the report by pushpak
AND ((@LEAD_TARGET='') OR ([LEAD_TARGET] = @LEAD_TARGET))
--AND ([QUALIFICATION] LIKE @QUALIFICATION+'%') --this line has been commented because it is the one trick used while optimization to increase the performance of the report by pushpak
AND EMAIL_ID IS NOT NULL
AND EMAIL_ID!=''
AND (((@MOBILE='') OR (@MOBILE=MOBILE)) OR ((@MOBILE='') OR (@MOBILE=TELEPHONE)))
ORDER BY ENQUIRY_DATE DESC
END
ELSE
BEGIN
SELECT [TELE_ENQUIRIES].[TELE_ENQUIRY_ID]
,[TELE_ENQUIRIES].[ENQUIRY_DATE]
,[TELE_ENQUIRIES].[FIRSTNAME]+' '+[TELE_ENQUIRIES].[MIDDLENAME]+' '+[TELE_ENQUIRIES].[LASTNAME] AS NAME
,[TELE_ENQUIRIES].[TELEPHONE]
,[TELE_ENQUIRIES].[MOBILE]
,[TELE_ENQUIRIES].[COURSE_INTERESTED]
,[TELE_ENQUIRIES].[STREAM]
,[TELE_ENQUIRIES].[SOURCE]
,[TELE_ENQUIRIES].[SUGGESTED_CENTRE_ID]
,[CENTRE_MASTER_1].[CENTRE_NAME] AS CENTRE_NAME
,[TELE_ENQUIRIES].[REMARKS]
,[TELE_ENQUIRIES].[ISENROLLED]
,[TELE_ENQUIRIES].[ADMISSION_ID]
,[TELE_ENQUIRIES].[CENTRE_ID]
,CENTRE_MASTER.[CENTRE_NAME] AS SUGGESTED_CENTRE_NAME
,[TELE_ENQUIRIES].[WALKIN]
,[TELE_ENQUIRIES].[ENQUIRY_HANDELED_BY]
,ISNULL(ISNULL(EMPLOYEE_MASTER.EMP_FNAME,'')+' '+ISNULL(EMPLOYEE_MASTER.EMP_LASTNAME,''),'') AS HANDELED_BY
,[FOLLOWUP_DETAILS]
,[CREATED_ON]
,[ENQUIRY_ID]
,[FOLLOWUP_DATE]
,[NEXT_FOLLOWUP_DATE]
,SOURCE_NAME
,FOLLOWUP_BY
,LOCATION
,LEAD_TARGET
,QUALIFICATION
,EMAIL_ID
FROM [MISONLINE_NEW].[dbo].[TELE_ENQUIRIES]
TELE_ENQUIRIES INNER JOIN
CENTRE_MASTER AS CENTRE_MASTER_1 ON CENTRE_MASTER_1.CENTRE_ID = TELE_ENQUIRIES.CENTRE_ID INNER JOIN
CENTRE_MASTER ON TELE_ENQUIRIES.SUGGESTED_CENTRE_ID = CENTRE_MASTER.CENTRE_ID LEFT OUTER JOIN
EMPLOYEE_MASTER ON TELE_ENQUIRIES.ENQUIRY_HANDELED_BY = EMPLOYEE_MASTER.EMPLOYEE_ID
LEFT OUTER JOIN
(
SELECT [FOLLOWUP_DETAILS],[SOURSE_ID],[FOLLOWUP_DATE],[NEXT_FOLLOWUP_DATE],EMPLOYEE_MASTER.EMP_FNAME+' '+EMPLOYEE_MASTER.EMP_LASTNAME AS FOLLOWUP_BY
FROM [FOLLOWUP_DETAILS] INNER JOIN EMPLOYEE_MASTER ON EMPLOYEE_MASTER.EMPLOYEE_ID=[FOLLOWUP_DETAILS].[FOLLOWUP_BY]
WHERE FOLLOWUP_DETAILS_ID IN
(
SELECT FOLLOWUP_DETAILS_ID FROM
(
SELECT MAX(FOLLOWUP_DETAILS_ID) AS FOLLOWUP_DETAILS_ID,[FOLLOWUP_DETAILS].[SOURSE_ID]
FROM [FOLLOWUP_DETAILS]
INNER JOIN
(
SELECT [SOURSE_ID],MAX([FOLLOWUP_DATE]) AS [FOLLOWUP_DATE]
FROM [MISONLINE_NEW].[dbo].[FOLLOWUP_DETAILS]
WHERE SOURCE='J'
GROUP BY SOURSE_ID
)TAB1 ON TAB1.[SOURSE_ID]=[FOLLOWUP_DETAILS].[SOURSE_ID]
WHERE TAB1.[SOURSE_ID]=[FOLLOWUP_DETAILS].[SOURSE_ID]
AND TAB1.[FOLLOWUP_DATE]=[FOLLOWUP_DETAILS].[FOLLOWUP_DATE]
GROUP BY [FOLLOWUP_DETAILS].[SOURSE_ID]
)TAB
)
)AS TAB1 ON TELE_ENQUIRIES.[TELE_ENQUIRY_ID]=TAB1.[SOURSE_ID]
WHERE
((@FROM_DATE='') OR (ENQUIRY_DATE BETWEEN @FROM_DATE AND @TO_DATE))
AND ([FIRSTNAME] like @FIRSTNAME + '%')
AND ([LASTNAME] LIKE @LASTNAME +'%')
AND ([COURSE_INTERESTED] LIKE @COURSE_INTERESTED+'%')
AND ((@SOURCE='') OR ([SOURCE] = @SOURCE))
AND ((@CENTRE_ID=-1) OR ([TELE_ENQUIRIES].CENTRE_ID=@CENTRE_ID))
AND ((@SUGGESTED_CENTRE_ID=-1) OR ([TELE_ENQUIRIES].[SUGGESTED_CENTRE_ID]=@SUGGESTED_CENTRE_ID))
AND ((@STREAM='') OR ([TELE_ENQUIRIES].[STREAM]=@STREAM))
AND ((@ISENROLLED=-1) OR ([TELE_ENQUIRIES].[ISENROLLED]=@ISENROLLED))
AND ((@WALKIN=-1) OR ([TELE_ENQUIRIES].[WALKIN]=@WALKIN))
AND (EMPLOYEE_MASTER.EMP_FNAME LIKE @EMP_FNAME+'%')
AND (EMPLOYEE_MASTER.EMP_LASTNAME LIKE @EMP_LASTNAME+'%')
AND ((@ISOUTBOUND=0 AND SOURCE<>'OUTBOUND') OR @ISOUTBOUND=1)
AND ((@ISHO=0 AND [SUGGESTED_CENTRE_ID]<>155) OR @ISHO=1)
--AND (SOURCE_NAME LIKE @SOURCE_NAME+'%') --this line has been commented because it is the one trick used while optimization to increase the performance of the report by pushpak
--AND ((@ISCENTERLEAD=0 AND SOURCE<>'Center Leads') OR @ISCENTERLEAD=1) --this line has been commented because it is the one trick used while optimization to increase the performance of the report by pushpak
AND ((@LOCATION='') OR ([LOCATION] = @LOCATION))
AND ((@LEAD_TARGET='') OR ([LEAD_TARGET] = @LEAD_TARGET))
--AND ([QUALIFICATION] LIKE @QUALIFICATION+'%') --this line has been commented because it is the one trick used while optimization to increase the performance of the report by pushpak
AND (((@MOBILE='') OR (@MOBILE=MOBILE)) OR ((@MOBILE='') OR (@MOBILE=TELEPHONE)))
ORDER BY ENQUIRY_DATE DESC
END
END
Как
Что я уже пробовал:
Я создал индекс на SOURCE_ID таблицы FOLLOWUP_DETAILS, но никакой помощи ..