问题描述
我有一个查询，其中有很多内部查询嵌入其中，虽然它给了我一个正确的报告但是执行执行需要太长时间（几分钟）。是否有任何方法来优化相同的。
以下是作为存储过程写的查询。
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'
*/
$ b $b╔════════════════════════════════════════════════════════════════════════════════════════════════ ═══════════>>b $b║来源║A║B║C║E║║
$ b $b╠═ ═══════════════════════╬═════╬═════════╬═════════╬ ═══════════>
$ b $b║ABPMajha║9║30900║19005║0║2║
║AC║1║0║0 ║0║0║
$ b $b║下午║1║131000║26700║0 ║1║
$ b $b║ALP║0║0║0║0║0║
$ b $b║Aparna - MBA║0║0║0║0║0║
$ b $b║ASKME║2║5259║2670║2║2║
║asklaila.com║1║13000║1780║146║5║
║阿塔瓦学院 - 2014年0 0 0 0 0 0 0 0
$ b $b║横幅║117║7314570║1788652║36║122║
$ b $b║BEST║2║ 14240║4005║║║║/
══════════════════════════════════════════════════════════════════════════════════════════>>
PS：在这里粘贴一个表输出真是太痛苦了......每次我尝试粘贴表格时格式都会被破坏ut
╔════════════════════════╦═════╦═════════╦═════════╦═════╦═════╗
║ 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
推荐答案
这篇关于具有子查询的查询执行时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！