Query having subquery takes too long to execute

See more:

optimization

SQL-Server-2008R2

I have got a query , which has lot of inner query nested into it, Although it gives me a correct report but however it takes too long(several minutes) to execute to execute.Is there any way to optimize the same .

Following is the query written as a stored Procedure.

CREATE PROCEDURE [dbo].[USP_GET_ENQUIRY_ANALYSIS_SUMMARY_REPORT]  
(  
@CENTRE_ID INT=-1,  
@FROM_DATE DATETIME=NULL,  
@TO_DATE DATETIME=NULL,  
@HEARD_ABOUTUS NVARCHAR(50)='',  
@SOURCE_NAME NVARCHAR(50)='',  
@IS_ENROLLED INT=-1,  
@COURSE_INTERESTED NVARCHAR(50)='',  
@CENTRE_WISE BIT=0  
)  
AS  
BEGIN  
   
 SET NOCOUNT ON;  
  
  
  IF @CENTRE_WISE=0  
  
  BEGIN---<<<<  
  

    
   SELECT   
   'S' AS SOURCE, '1' A, '1' B, '1' C, '1' E, '1' W   
    
    
  --End   
  END-->>>  
    
  ELSE  
    
    BEGIN---<<<<  
  
  SELECT [CENTRE_MASTER].[CENTRE_NAME] AS SOURCE, ISNULL(TAB1.A,0) A, ISNULL(TAB1.B,0) B, ISNULL(TAB1.C,0) C, ISNULL(TAB1.E,0) E, ISNULL(TAB1.W,0) W FROM  
  (  
   SELECT   
   [TELE_ENQUIRIES].[SUGGESTED_CENTRE_ID],  
   COUNT([TELE_ENQUIRIES].[ADMISSION_ID]) AS A,  
   SUM([ADMISSION_MASTER].[TOTALFEES]) AS B,  
   --SUM(DBO.[UFX_FEES_PAID]([ADMISSION_MASTER].[ADMISSION_ID])) AS [C],  
   SUM(ADMISSION_RECEIPTS.AMOUNT_PAID_FEES) AS [C],  
   COUNT(*)  AS E,  
   SUM(CONVERT(INT,ISNULL([TELE_ENQUIRIES].[WALKIN],0))) AS W  
        
        
   FROM  
        
   [TELE_ENQUIRIES] LEFT OUTER JOIN [ADMISSION_MASTER]  
   ON [TELE_ENQUIRIES].[ADMISSION_ID]=[ADMISSION_MASTER].[ADMISSION_ID]  
      LEFT OUTER JOIN ADMISSION_RECEIPTS ON   
      ADMISSION_MASTER.ADMISSION_ID=ADMISSION_RECEIPTS.ADMISSION_ID  
        
   WHERE   
     
   (([TELE_ENQUIRIES].[ENQUIRY_DATE] BETWEEN @FROM_DATE AND @TO_DATE) OR @FROM_DATE IS NULL)  
   AND  
   (([TELE_ENQUIRIES].[CENTRE_ID] = @CENTRE_ID) OR @CENTRE_ID=-1)  
   AND  
   ([TELE_ENQUIRIES].[COURSE_INTERESTED] LIKE @COURSE_INTERESTED+'%')  
      
   --AND  
   --(ISNULL([ENQUIRY_MASTER].[COURSE_INTERESTED],'') LIKE @COURSE_INTERESTED + '%')  
   --AND  
   --([ENQUIRY_MASTER].[ISENROLLED]=@IS_ENROLLED OR @IS_ENROLLED=-1)  
      AND [SOURCE]!='Outbound'  
      AND [SOURCE]!='Center Leads'  
   GROUP BY [TELE_ENQUIRIES].[SUGGESTED_CENTRE_ID]  
        
  ) AS TAB1 INNER JOIN [CENTRE_MASTER]  
  ON   
  TAB1.[SUGGESTED_CENTRE_ID]=[CENTRE_MASTER].[CENTRE_ID]  
  
  WHERE   
    
  dbo.CENTRE_MASTER.ISACTIVE=1  
    
     
   ORDER BY E DESC  
  END-->>>  
  
  SELECT [CONTROL_FILE].[CONTROLFILE_VALUE] AS SOURCE, ISNULL(TABA.A,0) A, ISNULL(TABB.B,0) B, ISNULL(TABC.C,0) C, ISNULL(TABE.E,0) E,ISNULL(TABW.W,0) W FROM [CONTROL_FILE]   
 LEFT OUTER JOIN  
 (  
  --SELECT ENQUIRY_MASTER.HEARD_ABOUTUS,COUNT(ENQUIRY_MASTER.ISENROLLED)AS A  
  --FROM ENQUIRY_MASTER  
  --WHERE ((ENQUIRY_MASTER.ENQUIRY_DATE  BETWEEN @FROM_DATE AND @TO_DATE) OR @FROM_DATE IS NULL)  
  --AND ENQUIRY_MASTER.ISENROLLED=1  
  --GROUP BY ENQUIRY_MASTER.HEARD_ABOUTUS   
   SELECT ENQUIRY_MASTER.HEARD_ABOUTUS,COUNT(*) AS A  
 FROM ADMISSION_MASTER  
 INNER JOIN ENQUIRY_MASTER ON ADMISSION_MASTER.ENQUIRY_ID=ENQUIRY_MASTER.ENQUIRY_ID  
 WHERE ((ADMISSION_MASTER.ADMISSION_DATE BETWEEN @FROM_DATE AND @TO_DATE) OR @FROM_DATE IS NULL)  
 AND ADMISSION_MASTER.ISACTIVE=1  
  GROUP BY ENQUIRY_MASTER.HEARD_ABOUTUS  
 )  
 AS TABA   
 ON   
 TABA.HEARD_ABOUTUS=[CONTROL_FILE].[CONTROLFILE_VALUE]  
  
 LEFT OUTER JOIN  
 (  
  SELECT ENQUIRY_MASTER.HEARD_ABOUTUS,SUM([ADMISSION_MASTER].[TOTALFEES]) AS B  
  FROM ENQUIRY_MASTER INNER JOIN ADMISSION_MASTER ON ENQUIRY_MASTER.ENQUIRY_ID=ADMISSION_MASTER.ENQUIRY_ID  
  WHERE ((ENQUIRY_MASTER.ENQUIRY_DATE  BETWEEN @FROM_DATE AND @TO_DATE) OR @FROM_DATE IS NULL)  
  GROUP BY ENQUIRY_MASTER.HEARD_ABOUTUS   
 )  
 AS TABB  
 ON   
 TABB.HEARD_ABOUTUS=[CONTROL_FILE].[CONTROLFILE_VALUE]  
  
 LEFT OUTER JOIN  
 (  
  SELECT ENQUIRY_MASTER.HEARD_ABOUTUS,SUM(DBO.[UFX_FEES_PAID]([ADMISSION_MASTER].[ADMISSION_ID])) AS [C]  
  FROM ENQUIRY_MASTER INNER JOIN ADMISSION_MASTER ON ENQUIRY_MASTER.ENQUIRY_ID=ADMISSION_MASTER.ENQUIRY_ID  
  WHERE ((ENQUIRY_MASTER.ENQUIRY_DATE  BETWEEN @FROM_DATE AND @TO_DATE) OR @FROM_DATE IS NULL)  
  GROUP BY ENQUIRY_MASTER.HEARD_ABOUTUS   
 )  
 AS TABC  
 ON   
 TABC.HEARD_ABOUTUS=[CONTROL_FILE].[CONTROLFILE_VALUE]  
  
 LEFT OUTER JOIN  
 (  
  SELECT [TELE_ENQUIRIES].[SOURCE],COUNT(*)  AS E FROM TELE_ENQUIRIES WHERE   
    (([TELE_ENQUIRIES].[ENQUIRY_DATE]  BETWEEN @FROM_DATE AND @TO_DATE) OR @FROM_DATE IS NULL)  
    AND [SOURCE]!='Outbound'  
    AND [SOURCE]!='Center Leads'  
    GROUP BY [TELE_ENQUIRIES].[SOURCE]  
 )AS TABE  
 ON   
 TABE.SOURCE=[CONTROL_FILE].[CONTROLFILE_VALUE]  
  
 LEFT OUTER JOIN  
 (  
  SELECT ENQUIRY_MASTER.HEARD_ABOUTUS,COUNT(*)  AS W   
  FROM ENQUIRY_MASTER  
  WHERE ((ENQUIRY_MASTER.ENQUIRY_DATE  BETWEEN @FROM_DATE AND @TO_DATE) OR @FROM_DATE IS NULL)  
  GROUP BY ENQUIRY_MASTER.HEARD_ABOUTUS   
 )  
 AS TABW   
 ON   
 TABW.HEARD_ABOUTUS=[CONTROL_FILE].[CONTROLFILE_VALUE]  
  
 WHERE [CONTROL_FILE].[CONTROLFILE_KEY]='HEARD_ABOUT_SACL'   
 ORDER BY [CONTROL_FILE].[CONTROLFILE_VALUE]  
  
END -------------->>>>>>>>>>>>>>   
   
   
  
/*  
EXEC DBO.USP_GET_ENQUIRY_ANALYSIS_SUMMARY_REPORT @CENTRE_ID=143, @FROM_DATE='06/1/2014',@TO_DATE='06/30/2014'  
*/

╔════════════════════════╦═════╦═════════╦═════════╦═════╦═════╗
║ SOURCE ║ A ║ B ║ C ║ E ║ W ║
╠════════════════════════╬═════╬═════════╬═════════╬═════╬═════╣
║ ABP Majha ║ 9 ║ 30900 ║ 19005 ║ 0 ║ 2 ║
║ AC ║ 1 ║ 0 ║ 0 ║ 0 ║ 0 ║
║ Afternoon ║ 1 ║ 131000 ║ 26700 ║ 0 ║ 1 ║
║ ALP ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║
║ Aparna - MBA ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║
║ ASK ME ║ 2 ║ 5259 ║ 2670 ║ 2 ║ 2 ║
║ asklaila.com ║ 1 ║ 13000 ║ 1780 ║ 146 ║ 5 ║
║ Atharva College - 2014 ║ 0 ║ 0 ║ 0 ║ 0 ║ 0 ║
║ Banners ║ 117 ║ 7314570 ║ 1788652 ║ 36 ║ 122 ║
║ BEST ║ 2 ║ 14240 ║ 4005 ║ 0 ║ 2 ║
╚════════════════════════╩═════╩═════════╩═════════╩═════╩═════╝


PS: Its such a pain to paste a table output in here .. the format gets broken each time i attempt to paste a table output