具有子查询的查询执行时间太长

本文介绍了具有子查询的查询执行时间太长的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个查询，其中有很多内部查询嵌入其中，虽然它给了我一个正确的报告但是执行执行需要太长时间（几分钟）。是否有任何方法来优化相同的。 以下是作为存储过程写的查询。

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║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 ║ 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.