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
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(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 [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(*) 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
╔════════════════════════╦═════╦═════════╦═════════╦═════╦═════╗
║ 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