Query taking too much time to execute : optimize the select query

Question

0.00/5 (No votes)

See more:

, +

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

SQL

EXECUTE [dbo].[USP_SEARCH_TELE_ENQUIRIES] @FROM_DATE ='2016-05-27 00:00:00.000'  ,@TO_DATE ='2016-06-27 00:00:00.000'

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 ?

SQL

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

As

What I have tried:

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

Posted 26-Jun-16 0:47am

Kunal Ved

Updated 28-Jun-16 10:08am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Answer 1 · 2016-06-26T02:02:00

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.

Jörgen Andersson · Answer 2 · 2016-06-28T10:08:00

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!

Query taking too much time to execute : optimize the select query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0