查询花费太多时间执行：优化选择查询 [英] Query taking too much time to execute : optimize the select query

问题描述

我在商店里以相当老式的方式写了一个查询。



Store Proc使用2个表，FOLLOWUP_DETAILS有231352个记录，TELE_ENQUIRIES有91727个记录。





搜索过滤器是刚刚根据1个月的日期范围完成



例如

Hi ,

I have a query written in quite old fashioned manner in a Store Proc

The Store Proc uses 2 tables , FOLLOWUP_DETAILS which has 231352 records and TELE_ENQUIRIES which has 91727 number of records.


The search filter is just done based on date range of 1 month

For example

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个奇数记录，但执行需要花费很多时间。



请找一个优化结果的方法。

如果我需要在w上放置索引我需要把索引放在哪个栏目上？

which usually returns 2000 odd records , but it takes hell lot of time to execute.

Please find suggest me a way to optimize the result.
If I need to put indexing , on which column i need to put indexes on ?

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 

作为



我尝试过：



我在FOLLOWUP_DETAILS表的SOURSE_ID上创建了一个索引，但没有帮助..

As

What I have tried:

I created an index on SOURSE_ID of FOLLOWUP_DETAILS table , but no help ..

推荐答案

我们无法告诉你如何加快它的速度因为我们无法访问您的系统。但是，有些事情需要注意：



1.确保您加入的字段中有索引。

2。 where子句中字段的索引是个好主意;但是，因为所有参数都是可选的，并且您的字段基本上在where子句的公式中，所以查询优化器可能不会使用索引。

3.使用数据库引擎优化顾问查看如果它建议任何索引或其他项目可以提供帮助，请教程：数据库引擎优化顾问 [ ^ ]。

4.在SSMS中的查询菜单项下，选择显示估计的执行计划。只需从存储过程中取出sql部分并自行运行它，而不是通过SP调用它。这将显示查询的每个部分在执行时间方面花费了多少。

There is no way we can tell you how to speed it up because we cannot access your system. However, some things to look for are:

1. Make sure there are indexes on the fields you are joining on.
2. Indexes on fields in your where clause would be a good idea; however, because all of your parameters are optional and your fields are essentially in a formula in the where clause, the query optimizer probably will not use indexes anyway.
3. Use the Database Engine Tuning Advisor to see if it recommends any indexes or other items to help, Tutorial: Database Engine Tuning Advisor[^].
4. Under Query menu item in SSMS choose to display the estimated execution plan. Do this by taking just the sql portion out of the Stored procedure and running it on its own, not calling it through the SP. This will show you how much each portion of the query is taking in terms of execution time.

存储过程和动态查询混合不好。

First在您使用该程序的时候，它将针对您在此时使用的参数进行编译和优化。

如果您下次使用不同的参数，查询计划将不会被优化。



我建议在这种情况下你将 OPTION（RECOMPILE）添加到每个查询的末尾，这将重新创建一个优化的每次使用SP时查询计划。



我还建议您阅读这个 [来自Erland Sommarskog的 ^ ]文章他必须阅读！

Stored procedures and dynamic queries don't mix well.
First time you're using the procedure it will be compiled and optimized for the parameters you used on that occasion.
If you the next time use different parameters, the query plan will not be optimized.

I recommend in this case that you add OPTION (RECOMPILE) to the end of each query, which will recreate an optimized query plan every time you use the SP.

I also recommend that you read this[^] article by Erland Sommarskog, anything by him is a must read!

这篇关于查询花费太多时间执行：优化选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！