具有子查询的查询执行时间太长 [英] Query having subquery takes too long to execute
问题描述
我有一个查询，其中有很多内部查询嵌入其中，虽然它给了我一个正确的报告但是执行执行需要太长时间（几分钟）。是否有任何方法来优化相同的。
以下是作为存储过程写的查询。
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
推荐答案
您好，
您的查询只能由您优化。似乎很多左外部显然会减慢输出。如果您可以避免这么多左侧外壳或制作小块大小的案例来获取数据然后获取最终输出。
干杯。
Hi,
Your query can be optimized by you only. It seems that so many left outer will obviously slow the output. If you can either avoid so many left outers or make small small chunks of cases to fetch out data and then fetch the Final output.
Cheers.
这篇关于具有子查询的查询执行时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
