存储过程需要花费很多时间来执行...光标是原因,,,, [英] stored procedure taking much time to execute...Is cursor the reason,,,,
本文介绍了存储过程需要花费很多时间来执行...光标是原因,,,,的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
存储过程需要2分钟才能执行。
Sp有光标。我正在尝试为所有部门生成月度报告，
我不能跳过光标我想，有没有其他方式如
- >我的sp中哪个查询运行缓慢？
- >是有没有办法避免光标？
- >如何解决这个问题。
< br $>
The Stored Procedure is taking 2 min to execute.
The Sp has cursor in it. I am trying to generate the monthly report for all the departments,
I cant skip cursor i think, Is there any Other way like
-->which query in my sp is running slow?
-->Is there any way to avoid cursor ?
-->How to sort out this.
Alter PROCEDURE [dbo].[Monthly_Report]--'15/05/2013 00:00','15/05/2013 23:59'
(
@fromdate datetime,
@todate datetime
)
AS
BEGIN
SET NOCOUNT ON
create table #tempmonthly
(
Duration varchar(50),
hub_id int,
Hub_Name varchar(100),
[Total Request Routed from Route Pro] float,
[Total Request/Cut Paste] float,
[occupancy4orless] float,
[occupancy5ormore] float,
[Adhoc request] int,
[Average request] int,
[Cut Paste Percentage] float,
[Total Unique users] float
)
DECLARE @pickcount float,
@dropcount float,
@pickcount4 float,
@dropcount4 float,
@totalemp float,
@totalemp4 float,
@routeidcount float,
@routeidcount4 float,
@occupancy float,
@occupancy4 float,
@adhocrequestcount float,
@routedfromroutepro float,
@cutpastecount float,
@cutpaste float,
@totaldays float,
@T_Drop_Emp float,
@T_Pick_Emp float,
@T_Emp float,
@Duration varchar(50),
@totalunique_users float,
@R_Name varchar(100)
BEGIN TRY
BEGIN TRANSACTION
IF(DATEDIFF(mm, @fromdate, @todate) >= 0)
BEGIN
print @fromdate
print @todate
select facility_id,Facility_Name into #facility1 from tbl_facility where hub=1 order by facility_id desc
Declare @R_id int
DECLARE CURSOR_G CURSOR FOR
Select facility_id,Facility_Name from #facility1
OPEN CURSOR_G
FETCH CURSOR_G INTO @R_id,@R_Name
WHILE @@Fetch_Status = 0
BEGIN
--------------------------------------------------occupancy--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
print 'HUBID-->' + cast(@R_id as varchar)
print '@routeidcountBEFOREset-->' + cast(@routeidcount as varchar)
--set @routeidcount=0
print '@routeidcountBEFORE-->' + cast(@routeidcount as varchar)
select @routeidcount=count(1) from tbl_Route_Header WITH(NOLOCK)
where Inactive = 0 AND Company_ID = 1
AND DATEDIFF(dd,@fromdate,Appointment_DateTime) >= 0
AND DATEDIFF(dd,Appointment_DateTime,@todate) >= 0
AND Route_Status_ID IN(1,2,3,4,5,6,8,9,10,11) and Hub_ID= @R_id
and current_occupancy between 0 and 4
print '@routeidcountAFTER-->' + cast(@routeidcount as varchar)
--Pick employess
set @pickcount=0
select @pickcount=isnull(count(userID),0) from tbl_route_detail RD with(nolock) inner join tbl_route_header RH with(nolock) on rh.route_iD=rd.route_id
where
RH.Company_ID = 1 AND
RH.route_type_ID=1 and rd.inactive=0 and
rh.route_status_ID in (1,2,3,4,5,6,8,9,10,11) and rh.hub_ID= @R_id and rh.inactive=0 and RD.action='P'
AND DATEDIFF(dd,@fromdate,rh.Appointment_DateTime) >= 0 AND DATEDIFF(dd,rh.Appointment_DateTime,@todate) >= 0
and RH.current_occupancy between 0 and 4
print 'PICK COUNT AFTER-->' + cast(@pickcount as varchar)
--Drop employess
set @dropcount=0
select @dropcount=isnull(count(userID),0) from tbl_route_detail RD with(nolock) inner join tbl_route_header RH with(nolock) on rh.route_iD=rd.route_id
where
RH.Company_ID = 1 AND
RH.route_type_ID=0 and rd.inactive=0 and
rh.route_status_ID in (1,2,3,4,5,6,8,9,10,11) and rh.hub_ID=@R_id and rh.inactive=0 and RD.action='P'
AND DATEDIFF(dd,@fromdate,rh.Appointment_DateTime) >= 0 AND DATEDIFF(dd,rh.Appointment_DateTime,@todate) >= 0
and RH.current_occupancy between 0 and 4
print 'DROPCOUNT AFTER-->' + cast(@dropcount as varchar)
set @totalemp=0
set @totalemp=@dropcount+@pickcount
print 'TOTAL COUNT-->' + cast(@totalemp as varchar)
if(@totalemp >0)
begin
set @occupancy=@totalemp/@routeidcount
end
else set @occupancy=0.00
print 'occupancy--> ' + cast(@occupancy as varchar)
if(@occupancy=0)
goto GoEND1
----------------------------------------------------occupancy < 4 end-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------occupancy > 4 start--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select @pickcount4=isnull(count(userID),0) from tbl_route_detail RD with(nolock) inner join tbl_route_header RH with(nolock) on rh.route_iD=rd.route_id
where
RH.Company_ID = 1 AND
RH.route_type_ID=1 and rd.inactive=0 and
rh.route_status_ID in (1,2,3,4,5,6,8,9,10,11) and rh.hub_ID= @R_id and rh.inactive=0 and RD.action='P'
AND DATEDIFF(dd,@fromdate,rh.Appointment_DateTime) >= 0 AND DATEDIFF(dd,rh.Appointment_DateTime,@todate) >= 0
and RH.current_occupancy between 5 and 12
print 'PICK COUNT MORE THAN 4--> ' + cast(@pickcount4 as varchar)
--Drop employess
select @dropcount4=isnull(count(userID),0) from tbl_route_detail RD with(nolock) inner join tbl_route_header RH with(nolock) on rh.route_iD=rd.route_id
where
RH.Company_ID = 1 AND
RH.route_type_ID=0 and rd.inactive=0 and
rh.route_status_ID in (1,2,3,4,5,6,8,9,10,11) and rh.hub_ID=@R_id and rh.inactive=0 and RD.action='P'
AND DATEDIFF(dd,@fromdate,rh.Appointment_DateTime) >= 0 AND DATEDIFF(dd,rh.Appointment_DateTime,@todate) >= 0
and RH.current_occupancy between 5 and 12
print 'PICK COUNT MORE THAN 4--> ' + cast(@dropcount4 as varchar)
select @routeidcount4=count(1) from tbl_Route_Header WITH(NOLOCK)
where Inactive = 0 AND Company_ID = 1
AND DATEDIFF(dd,@fromdate,Appointment_DateTime) >= 0
AND DATEDIFF(dd,Appointment_DateTime,@todate) >= 0
AND Route_Status_ID IN(1,2,3,4,5,6,8,9,10,11) and Hub_ID=@R_id
and current_occupancy between 5 and 12
print 'Route Count for occupancy greater than 4--> ' + cast(@routeidcount4 as varchar)
set @totalemp4=@dropcount4+@pickcount4
print 'total employee count-> ' + cast(@totalemp4 as varchar)
if(@totalemp4 >0)
begin
set @occupancy4=@totalemp4/@routeidcount4
end
else set @occupancy4=0.00
print 'occupancy greater than 4-> ' + cast(@occupancy4 as varchar)
---------------------------------------------------------occupancy > 4 end----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------cutpaste start--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-->adhoc request
select @adhocrequestcount =count(*)
from tbl_request with (NOLOCk)
where created_by not like '%admin%'
and request_type_id = 0
and (from_facility_id in(select facility_id from tbl_facility where hub_id = @R_id)
or to_facility_id in(select facility_id from tbl_facility where hub_id = @R_id))
and Appointment_DateTime between @fromdate and @todate
print 'Adhoc Request-> ' + cast(@adhocrequestcount as varchar)
--> Request routed from route pro
select @cutpastecount=(count(*)/2) from tbl_route_edit_log with (nolock) where action in
('Cut','Paste') and hub_id = @R_id and Appointment_DateTime between @fromdate and @todate
and USERID in (
select UserID from tbl_request with(nolock) where Appointment_DateTime between
@fromdate and @todate
and (from_facility_id
in(select facility_id from tbl_facility where hub_id = @R_id)
or to_facility_id in(select facility_id from tbl_facility where hub_id = @R_id))
and IsRoutedFromRoutePro=1 )
print 'Total routes routed pro on which cut paste done-> ' + cast(@cutpastecount as varchar)
-->Total Number of request routed from route pro
select @routedfromroutepro=count(*)
from tbl_request with (NOLOCk)
where IsRoutedFromRoutePro=1
and request_status_id not in(0,1,8,12)
and (from_facility_id in(select facility_id from tbl_facility where hub_id = @R_id)
or to_facility_id in(select facility_id from tbl_facility where hub_id = @R_id))
and Appointment_DateTime between @fromdate and @todate
print 'occupancy greater than 4-> ' + cast(@occupancy4 as varchar)
set @cutpaste=0.00
if(@cutpastecount>0)
begin
set @cutpaste=isnull((@cutpastecount/@routedfromroutepro)*100,0.00)
print 'Cut Paste Percentage-> ' + cast(@cutpaste as varchar)
end
--------------------------------------------------cut paste end--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------average request per day-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select @T_Drop_Emp=isnull(count(userID),0) from tbl_route_detail RD with(nolock) inner join tbl_route_header RH with(nolock) on rh.route_iD=rd.route_id
where
RH.Company_ID = 1 AND
RH.route_type_ID=0 and rd.inactive=0 and
rh.route_status_ID in (1,2,3,4,5,6,8,9,10,11) and rh.hub_ID=@R_id and rh.inactive=0 and RD.action='P'
AND DATEDIFF(dd,@fromdate,rh.Appointment_DateTime) >= 0 AND DATEDIFF(dd,rh.Appointment_DateTime,@todate) >= 0
select @T_Pick_Emp=isnull(count(userID),0) from tbl_route_detail RD with(nolock) inner join tbl_route_header RH with(nolock) on rh.route_iD=rd.route_id
where
RH.Company_ID = 1 AND
RH.route_type_ID=1 and rd.inactive=0 and
rh.route_status_ID in (1,2,3,4,5,6,8,9,10,11) and rh.hub_ID=@R_id and rh.inactive=0 and RD.action='P'
AND DATEDIFF(dd,@fromdate,rh.Appointment_DateTime) >= 0 AND DATEDIFF(dd,rh.Appointment_DateTime,@todate) >= 0
SET @T_Emp=@T_Pick_Emp+@T_Drop_Emp
SET @TotalDays = (DATEDIFF(dd, @fromdate, @todate) + 1)
set @Duration= CONVERT(VARCHAR(11),@fromdate,106) + ' To ' + CONVERT(VARCHAR(11),@todate,106)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------total unique users start--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select @totalunique_users = count(distinct rd.userid)
from tbl_route_detail RD with(nolock)
inner join tbl_route_header RH with(nolock) on rh.route_iD=rd.route_id
inner join tbl_user_master UM with (nolock) on UM.userid=rd.userid
Left join tbl_user_master UM1 with (nolock) on UM1.userid=UM.manager_id
inner join tbl_facility F with (nolock) on F.facility_id=Rh.hub_id
inner join tbl_geocode_address GA with (nolock) on GA.Address_id=UM.Address_id
left join tbl_id_distances D with (nolock) on D.From_id=@R_id and To_id=UM.Address_id
where
RH.Company_ID = 1 AND
rd.inactive=0 and
rh.route_status_ID in (1,2,3,4,5,6,8,9,10,11)
and rh.hub_ID=@R_id and rh.inactive=0 and RD.action='P'
and rh.appointment_datetime between @fromdate and @todate
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into #tempmonthly
(Duration,hub_id,Hub_Name,
[Total Request Routed from Route Pro],
[Total Request/Cut Paste],
[occupancy4orless],
[occupancy5ormore],
[Adhoc request],
[Average request],
[cut paste percentage],
[Total Unique users])
values (@Duration,
@R_id,
@R_Name,
@routedfromroutepro,
@cutpastecount,
round( @occupancy,2),
round( @occupancy4,2),
@adhocrequestcount,
ISNULL(@T_Emp/@TotalDays,0.00),
round(@cutpaste,2),
isnull(@totalunique_users,0.00))
GoEND1:
FETCH CURSOR_G INTO @R_id,@R_Name
END
CLOSE CURSOR_G
DEALLOCATE CURSOR_G
END
declare @count float
select @count=count(1) from #tempmonthly where [occupancy5ormore] >0
insert into #tempmonthly(Duration,[occupancy4orless],[occupancy5ormore],[Adhoc request],[Average request],[cut paste percentage],[Total Unique users])
SELECT 'FINAL STATISTICS',Round(avg(([occupancy4orless])),2),Round((sum(occupancy5ormore))/@count,2),sum([adhoc request]),
ISNULL(@T_Emp/@TotalDays,0.00),Round(avg([cut paste percentage]),2),Round(sum(isnull([total unique users],0.00)),2)
from #tempmonthly with(nolock)
select * from #tempmonthly with(nolock)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT INTO tbl_Common_Error_Log
(Err_Number,Err_Severity,Err_State,Err_Pro,Err_Line,Err_Message,Created_On)
SELECT
ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity,ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage,getdate()
END CATCH
SET NOCOUNT OFF
END
--Monthly_Report ’15/05/2013 00:00’,’15/05/2013 23:59’
--Monthly_Report '15/05/2013 00:00','15/05/2013 23:59'
推荐答案
Have a look at past answers:
How can i reduce the execution time of running a query of SQL SERVER 2005?[^]
Different Execution time for Same SQL Query[^]
Help with sql server query.[^]
Next time, please, use SearchBox (right-top corner of this site).
Have a look at past answers:
How can i reduce the execution time of running a query of SQL SERVER 2005?[^]
Different Execution time for Same SQL Query[^]
Help with sql server query.[^]
Next time, please, use SearchBox (right-top corner of this site).
这篇关于存储过程需要花费很多时间来执行...光标是原因,,,,的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
