如何优化用于在仪表板中显示数据的存储过程？ [英] How to optimise the stored procedure used for displaying data in the dashboard?

查看：79
本文介绍了如何优化用于在仪表板中显示数据的存储过程？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想优化用于在仪表板中显示数据的存储功能。

我使用多个表来获取要在仪表板中显示的数据。我正在使用UNION



我尝试过：



我使用多个表来获取要在仪表板中显示的数据。我正在使用UNION。

我的SQL查询：

I want to Optimise the Stored Porcedure used for displaying data in the Dashboard.
I am using multiple tables to fetch data to display in the dashboard. I am using UNION for the same

What I have tried:

I am using multiple tables to fetch data to display in the dashboard. I am using UNION for the same.
My SQL Query:

SET @p_TO_DATE= convert(datetime,GETDATE(),103) 
select @RoleId= RoleId,@DeptId=DeptId from ICCS_USERS where UserId=@ip_userId 

--Input Pending Start       
		Select 'Input Pending(' + CONVERT(varchar(10),(OCEPending+ACEPending+PDCPending)) + ')' as Type,        
		'OCE(' + CONVERT(varchar(10),OCEPending) + ')' AS OCE,
		'PDC(' + CONVERT(varchar(10),PDCPending) + ')' AS PDC,            
		'ACE(' + CONVERT(varchar(10),ACEPending) + ')' AS ACE,
		'RCE(' + CONVERT(varchar(10),ICEACKPending) + ')' AS ICE        
		       
		from             
		(Select 'Input Pending' AS TYPE,           


		--OCE Input Pending Start  
		SUM(CASE WHEN   
		CurrentStatus in(1)  
		AND  
		convert(datetime,ScannedDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		) as 'OCEPending',      
        
        -- PDC  Cheque Input Pending Start  
		SUM(CASE WHEN   
		CurrentStatus in(57)  
		AND  
		convert(datetime,ScannedDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		) as 'PDCPending',

		-- ACE Cheque Input Pending Start  
		SUM(CASE WHEN   
		CurrentStatus in(36)  
		AND  
		convert(datetime,ScannedDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		) as 'ACEPending'  
		
		,'' as 'ICEACKPending'    
		
		from ICCS_CHEQUE_DETAILS     
		WHERE
		NextRoleId =@RoleId  and DeptId=@DeptID 
		
		UNION
		
		SELECT 
		'',
		'',
		'',
		'',
		SUM(CASE WHEN   
		CurrentStatus in(20.50,20.80,20.85)  
		and ECEItemType IN('N','P')
		AND  
		convert(datetime,ICE_CreatedDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		) 
		as 'ICEACKPending'        
		FROM 
        ICCS_INWARD_CHEQUE_DETAILS
        WHERE
		NextRoleId =@RoleId  and DeptId=@DeptID 
		)
        
		as Input  
--Input Pending End



--Repair Start
		Select 'Repair Pending(' + 
		CONVERT(varchar(10),(OCEPending+ACEPending+PDCPending)) + ')' as Type,        
		'OCE(' + CONVERT(varchar(10),OCEPending) + ')' AS OCE,        
		'PDC(' + CONVERT(varchar(10),PDCPending) + ')' AS PDC,
        'ACE(' + CONVERT(varchar(10),ACEPending) + ')' AS ACE   
		from             

		-- Outward Start
		(Select 'Repair Pending' AS TYPE,           

		--OCE Repair Pending Start  
		SUM(CASE WHEN   
		CurrentStatus in(4)  
		AND  
		convert(datetime,ScannedDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		) as 'OCEPending',      
         
        -- PDC  Repair Pending Start  
		SUM(CASE WHEN     
		CurrentStatus in(60)    
		AND convert(datetime,ScannedDate,103) <=@p_TO_DATE    
		-- AND    
		--NextRoleId=@RoleId  and DeptId=@DeptID      
		then 1 else 0 end     
		) as 'PDCPending' ,


		-- ACE Repair Pending Start  
		SUM(CASE WHEN   
		CurrentStatus in(39)

		AND  
		convert(datetime,ScannedDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		) as 'ACEPending'    


		

		from ICCS_CHEQUE_DETAILS  
		WHERE
		NextRoleId =@RoleId  and DeptId=@DeptID    
		
		) 
		as Repair 
--Repair End

--Verification/Ack Verification Start        
		Select 'Verification Pending(' + 
		CONVERT(varchar(10),(OCEPending+ACEPending+PDCPending
		+ICEPending+IREPending+OCEACKPending+ACEACKPending
		+PDCACKPending+RCEACKPending)) + ')' as Type,        
		'OCE(' + CONVERT(varchar(10),OCEPending) + ')' AS OCE, 
		'PDC(' + CONVERT(varchar(10),PDCPending) + ')' AS PDC,       
		'ACE(' + CONVERT(varchar(10),ACEPending) + ')' AS ACE,        
		'ICE(' + CONVERT(varchar(10),ICEPending) + ')' AS ICE,
		'IRE(' + CONVERT(varchar(10),IREPending) + ')' AS IRE,
		'OCE ACK(' + CONVERT(varchar(10),OCEACKPending) + ')' AS OCEACK,
		'ACE ACK(' + CONVERT(varchar(10),ACEACKPending) + ')' AS ACEACK,
		'PDC ACK(' + CONVERT(varchar(10),PDCACKPending) + ')' AS PDCACK,
		'ICE ACK(' + CONVERT(varchar(10),RCEACKPending) + ')' AS RCEACK         
		from             

		-- Outward Start
		(
		
		Select 'Verification Pending' AS TYPE,           

		--OCE Verification Pending Start  
		SUM(CASE WHEN   
		CurrentStatus in(2,5)  
		AND  
		convert(datetime,ScannedDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		) as 'OCEPending',      

        -- PDC  Verification Pending Start  
		SUM(CASE WHEN   
		CurrentStatus in(58,61)  
		AND  
		convert(datetime,ScannedDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		) as 'PDCPending',
		
		 
		-- ACE Verification Pending Start  
		SUM(CASE WHEN   
		CurrentStatus in(37,40)

		AND  
		convert(datetime,ScannedDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		) as 'ACEPending',     

		
		'' as ICEPending,
		
		-- IRE  Verification Pending Start  
		SUM(CASE WHEN   
		CurrentStatus in(30,30.10,30.20,31,33)  
		AND  
		ECEItemType IN ('N','P')
		AND
		convert(datetime,SettlementDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		) as 'IREPending',

		-- OCE ACK  Verification Pending Start  
		SUM(CASE WHEN   
		CurrentStatus in(17,18)
		AND
		ECEItemType='N'  
		AND  
		convert(datetime,CBResponseDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		) as 'OCEACKPending',
		
			-- PDC ACK  Verification Pending Start  
		SUM(CASE WHEN   
		CurrentStatus in(17,18)
		AND
		ECEItemType='P'  
		AND  
		convert(datetime,CBResponseDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		) as 'PDCACKPending',

		-- ACE ACK  Verification Pending Start  
		SUM(CASE WHEN   
		CurrentStatus in(48,49)
		AND  
		convert(datetime,CBResponseDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		) as 'ACEACKPending',

		
		--- RCE ACK  Verification Pending Start  
	    '' as 'RCEACKPending'
		
		
        --Verification/Ack Verification End  

		from ICCS_CHEQUE_DETAILS  
		WHERE
		NextRoleId =@RoleId  and DeptId=@DeptID 
		
		
-- Outward End 
		
		UNION
-- Inward Start		
		SELECT 
		'',
		'',
		'',
		'',
		--ICE Pending
		SUM(CASE WHEN   
		CurrentStatus in(20.50,20.85)  
		and ECEItemType IN('N','P')
		AND  
		convert(datetime,ICE_CreatedDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		
		) as 'ICEPending',
		'',
		'',
		'',
		'',
		--RCE ACK Pending
		SUM(CASE WHEN   
		CurrentStatus in(28,28.50)  
		and ECEItemType IN('N','P')
		AND  
		convert(datetime,ICE_CreatedDate,103)  <=@p_TO_DATE  
		-- AND  
		--NextRoleId=@RoleId  and DeptId=@DeptID   
		then 1 else 0 end   
		
		) as 'RCEACKPending'
		
 
        FROM 
        ICCS_INWARD_CHEQUE_DETAILS
        WHERE
		NextRoleId =@RoleId  and DeptId=@DeptID 
        )
        AS A 
		
-- Inward End

推荐答案

作为@PIEBALD咨询已经暗示，你的查询中有太多的字符串处理。 SQL Server不是为了让事情变得漂亮 - 在表示层中这样做，或者至少在你最终收集了所有必要的数据时。



一些具体的例子：更改 SET @ p_TO_DATE = convert（datetime，GETDATE（），103）至

As @PIEBALDconsult has already intimated, you have far too much string handling in your query. SQL Server is not designed to make things pretty - do that in the presentation layer, or at least when you have finally collected all of the necessary data.

Some specific examples: Change SET @p_TO_DATE= convert(datetime,GETDATE(),103) to
SET @p_TO_DATE= GETDATE()

以及（例如） convert（datetime，ScannedDate，103）< = @的相应条件p_TO_DATE to 

ScannedDate<=@p_TO_DATE

您需要更改 SELECT''，''，'等内容'，''，... 到

SELECT null, null, null, null, ...

或者甚至更好

SELECT 0,0,0,0,...



As @PIEBALDconsult提到你应该确保列类型与数据类型准确匹配，日期应该是Date或DateTime列类型，数字数据必须是Decimal或Int列类型。



请看这段：


As mentioned by @PIEBALDconsult you should ensure that the column types accurately match the data types, dates should be in Date or DateTime column type, numeric data must be in Decimal or Int column types.

Look at this segment:

'Input Pending(' + CONVERT(varchar(10),(OCEPending+ACEPending+PDCPending)) + ')' as Type,        
		'OCE(' + CONVERT(varchar(10),OCEPending) + ')' AS OCE,
		'PDC(' + CONVERT(varchar(10),PDCPending) + ')' AS PDC,            
		'ACE(' + CONVERT(varchar(10),ACEPending) + ')' AS ACE,
		'RCE(' + CONVERT(varchar(10),ICEACKPending) + ')' AS ICE

摆脱所有 CONVERT 来电！这将运行得更快，你可以添加所有单词作为表示层的一部分。

Get rid of all those CONVERT calls! This will run far quicker, and you can add all of the words as part of the presentation layer.

'Input Pending' as [Type],
OCEPending+ACEPending+PDCPending as [total],        
OCEPending AS OCE, PDCPending AS PDC,            
ACEPending AS ACE, ICEACKPending AS ICE



您似乎是多次访问相同的表 - 你可以通过正确使用GROUP BY来合理化。



考虑使用临时表来捕获/过滤你的数据感兴趣 - 例如你在整个地方使用< = @ p_TO_DATE - 也许这可以用来过滤你正在查看的整体数据 的。通过将 AND NextRoleId = @ RoleId和DeptId = @DeptID 移动到最终的WHERE子句，您似乎已经采取了这一步骤。这可能会帮助你摆脱尽可能多的子查询。



说到子查询 - 不包括那些你不喜欢的列在外部查询中使用。例如。


You appear to be visiting the same tables multiple times - you could probably rationalise that by proper use of GROUP BY.

Consider using temporary table(s) to capture/filter the data you are interested in - for example you are using <=@p_TO_DATE all over the place - perhaps this could be used to filter the overall data that you are looking at first. You already appear to have taken this step by moving AND NextRoleId=@RoleId and DeptId=@DeptID to the final WHERE clauses. That might help you to get rid of as many of those sub-queries as possible too.

Speaking of sub-queries - don't include columns that you then don't use in the outer query. E.g. 

From             
(Select 'Input Pending' AS TYPE,

你实际上在外部查询中硬编码输入待定文本，从不使用列TYPE（甚至不是设置在UNION的第二部分）。



我认为第一部分可以归结为...

You actually hard-code the text 'Input Pending' in the outer query and never use the "column" TYPE (Which isn't even set in the second part of the UNION).

I think the first part could come down to this ...

Select 'Input Pending(' As [Type],
OCEPending+ACEPending+PDCPending as [total],
OCEPending AS OCE,
PDCPending AS PDC,
ACEPending AS ACE,
ICEACKPending AS ICE

from
(
    Select
    SUM(CASE WHEN CurrentStatus in(1)  then 1 else 0 end) as 'OCEPending',
    SUM(CASE WHEN CurrentStatus in(57) then 1 else 0 end) as 'PDCPending',
    SUM(CASE WHEN CurrentStatus in(36) then 1 else 0 end) as 'ACEPending',
    0 as 'ICEACKPending'
    from ICCS_CHEQUE_DETAILS
    WHERE NextRoleId = @RoleId  and DeptId=@DeptID AND ScannedDate <=@p_TO_DATE

    UNION

    SELECT NULL,NULL,NULL,NULL,
    SUM(CASE WHEN CurrentStatus in(20.50,20.80,20.85) then 1 else 0 end) as 'ICEACKPending'
    FROM ICCS_INWARD_CHEQUE_DETAILS
    WHERE NextRoleId =@RoleId  and DeptId=@DeptID AND ICE_CreatedDate<=@p_TO_DATE and ECEItemType IN('N','P')
) as Input

虽然我怀疑你只期望一行，在这种情况下外部查询应该是

Although I suspect you were expecting only a single row in which case the outer query should be

SUM(OCEPending+ACEPending+PDCPending) as [total],        
		SUM(OCEPending) AS OCE,
		SUM(PDCPending) AS PDC,            
		SUM(ACEPending) AS ACE,
		SUM(ICEACKPending) AS ICE

否则没有真正意义上的UNION

Otherwise there is no real point to having the UNION


这篇关于如何优化用于在仪表板中显示数据的存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆