Alex2 101 Ответов: 1

Как оптимизировать хранимую процедуру, используемую для отображения данных на панели мониторинга?

Я хочу оптимизировать сохраненный Porcedure, используемый для отображения данных на панели мониторинга.
Я использую несколько таблиц для извлечения данных для отображения на панели мониторинга. Я использую UNION для того же самого

Что я уже пробовал:

Я использую несколько таблиц для извлечения данных для отображения на панели мониторинга. Я использую UNION для того же самого.
Мой SQL-запрос:
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

PIEBALDconsult

Независимо от того, хранится ли он в процедуре или нет, это не проблема.
Я боюсь, что ваш код затрудняется большим количеством манипуляций со строками (в частности, преобразованием) и суб-выборками.
И я очень надеюсь, что вы не храните даты в виде строк.
На мой взгляд, вам нужно создать резервную копию и переработать задействованные таблицы.

1 Ответов

Рейтинг:
1

CHill60

Как уже намекал @PIEBALDconsult, в вашем запросе слишком много обработки строк. SQL Server не предназначен для того, чтобы делать вещи красивыми - делайте это на уровне презентации или, по крайней мере, когда Вы наконец собрали все необходимые данные.

Некоторые конкретные примеры: изменение SET @p_TO_DATE= convert(datetime,GETDATE(),103) к

SET @p_TO_DATE= GETDATE()
и соответствующие условия из (напр.) convert(datetime,ScannedDate,103) <=@p_TO_DATE к 
ScannedDate<=@p_TO_DATE
Вам нужно будет изменить такие вещи, как SELECT '','','','',... к 
SELECT null, null, null, null, ...
или, возможно, даже лучше 
SELECT 0,0,0,0,...

Как уже упоминалось @PIEBALDconsult, вы должны убедиться, что типы столбцов точно соответствуют типам данных, даты должны быть в типе столбца Date или DateTime, числовые данные должны быть в типах столбцов Decimal или Int.

Посмотрите на этот сегмент:
'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 звонит! Это будет работать гораздо быстрее, и вы можете добавить все слова как часть слоя презентации.
'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 and DeptId=@DeptID к заключительным пунктам где. Это также может помочь вам избавиться от как можно большего количества этих подзапросов.

Говоря о подзапросах - не включайте столбцы, которые вы затем не используете во внешнем запросе. 
From             
(Select 'Input Pending' AS TYPE,
На самом деле вы жестко кодируете текст "Input Pending" во внешнем запросе и никогда не используете тип "column" (который даже не задан во второй части объединения).

Я думаю, что первая часть может сводиться к этому ...
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
Хотя я подозреваю, что вы ожидали только одну строку, и в этом случае внешний запрос должен быть
SUM(OCEPending+ACEPending+PDCPending) as [total],        
		SUM(OCEPending) AS OCE,
		SUM(PDCPending) AS PDC,            
		SUM(ACEPending) AS ACE,
		SUM(ICEACKPending) AS ICE
В противном случае нет никакого реального смысла иметь союз

Karthik_Mahalingam

5

CHill60

Спасибо