存储过程1(用临时表):CREATE PROCEDURE [RCET_SYS].[P_TEST3]
as
declare @Err_Procedure varchar(50);
declare @Err_Line int;
declare @Err_Number int;
declare @Err_Message varchar(1000);
declare @Err_Severity int;
declare @Err_State int;
declare @sysdate datetime;
declare @i int
declare @total_count int
declare @EmpCo varchar(10)
begin
SET NOCOUNT ON;
set xact_abort on;
set @sysdate=getdate();
begin tran; BEGIN TRY
select identity(int,1,1) as id,* into #t from [dbo].[tb_data_eq_rt_tmp];
select @total_count = count(*) from #t
set @i = 1
while @i <= @total_count
begin
insert into [dbo].[tb_data_eq_rt_tmp2] select data_guid,org_id,eq_guid,cycle_time,pick_time,current_value from #t where id=@i;
set @i = @i + 1;
end;
commit;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN --回滚事务
select @Err_Procedure=ERROR_PROCEDURE(),@Err_Line=ERROR_LINE(),@Err_Number=ERROR_NUMBER(),@Err_Message='存储过程测试:'+ERROR_MESSAGE(),@Err_Severity=ERROR_SEVERITY(),@Err_State=ERROR_STATE();
exec loopback.[RCET3.0].[RCET_SYS].[P_ADD_PRO_LOG] '',@Err_Procedure,@Err_Line,@Err_Number,@Err_Message,@Err_Severity,@Err_State,@sysdate;
END CATCH
end;
存储过程2(用游标):CREATE PROCEDURE [RCET_SYS].[P_TEST4]
as
declare @Err_Procedure varchar(50);
declare @Err_Line int;
declare @Err_Number int;
declare @Err_Message varchar(1000);
declare @Err_Severity int;
declare @Err_State int;
declare @sysdate datetime;
declare @data_guid varchar(50);
declare @org_id varchar(50);
declare @eq_guid varchar(50);
declare @cycle_time datetime;
declare @pick_time datetime;
declare @current_value numeric(22,10);
DECLARE cur_eq_rt_tmp CURSOR FORWARD_ONLY STATIC for select data_guid,org_id,eq_guid,cycle_time,pick_time,current_value from [dbo].[tb_data_eq_rt_tmp];
begin
SET NOCOUNT ON;
set xact_abort on;
set @sysdate=getdate();
begin tran; BEGIN TRY
open cur_eq_rt_tmp --打开游标
FETCH NEXT FROM cur_eq_rt_tmp INTO @data_guid,@org_id,@eq_guid,@cycle_time,@pick_time,@current_value;
while(@@fetch_status=0) --判断fetch语句是否执行成功
begin
insert into [dbo].[tb_data_eq_rt_tmp2] values(@data_guid,@org_id,@eq_guid,@cycle_time,@pick_time,@current_value);
FETCH NEXT FROM cur_eq_rt_tmp INTO @data_guid,@org_id,@eq_guid,@cycle_time,@pick_time,@current_value;
end
close cur_eq_rt_tmp --关闭游标
deallocate cur_eq_rt_tmp --释放游标
commit;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN --回滚事务
select @Err_Procedure=ERROR_PROCEDURE(),@Err_Line=ERROR_LINE(),@Err_Number=ERROR_NUMBER(),@Err_Message='存储过程测试:'+ERROR_MESSAGE(),@Err_Severity=ERROR_SEVERITY(),@Err_State=ERROR_STATE();
exec loopback.[RCET3.0].[RCET_SYS].[P_ADD_PRO_LOG] '',@Err_Procedure,@Err_Line,@Err_Number,@Err_Message,@Err_Severity,@Err_State,@sysdate;
END CATCH
end;
过程:将tb_data_eq_rt_tmp的数据取出来插入tb_data_eq_rt_tmp2表中,我测试了3万条记录,结果,用临时表需要35秒左右,用游标基本上是1秒多点我在网上看到很多测试都是使用临时表快,我这里怎么是反的
as
declare @Err_Procedure varchar(50);
declare @Err_Line int;
declare @Err_Number int;
declare @Err_Message varchar(1000);
declare @Err_Severity int;
declare @Err_State int;
declare @sysdate datetime;
declare @i int
declare @total_count int
declare @EmpCo varchar(10)
begin
SET NOCOUNT ON;
set xact_abort on;
set @sysdate=getdate();
begin tran; BEGIN TRY
select identity(int,1,1) as id,* into #t from [dbo].[tb_data_eq_rt_tmp];
select @total_count = count(*) from #t
set @i = 1
while @i <= @total_count
begin
insert into [dbo].[tb_data_eq_rt_tmp2] select data_guid,org_id,eq_guid,cycle_time,pick_time,current_value from #t where id=@i;
set @i = @i + 1;
end;
commit;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN --回滚事务
select @Err_Procedure=ERROR_PROCEDURE(),@Err_Line=ERROR_LINE(),@Err_Number=ERROR_NUMBER(),@Err_Message='存储过程测试:'+ERROR_MESSAGE(),@Err_Severity=ERROR_SEVERITY(),@Err_State=ERROR_STATE();
exec loopback.[RCET3.0].[RCET_SYS].[P_ADD_PRO_LOG] '',@Err_Procedure,@Err_Line,@Err_Number,@Err_Message,@Err_Severity,@Err_State,@sysdate;
END CATCH
end;
存储过程2(用游标):CREATE PROCEDURE [RCET_SYS].[P_TEST4]
as
declare @Err_Procedure varchar(50);
declare @Err_Line int;
declare @Err_Number int;
declare @Err_Message varchar(1000);
declare @Err_Severity int;
declare @Err_State int;
declare @sysdate datetime;
declare @data_guid varchar(50);
declare @org_id varchar(50);
declare @eq_guid varchar(50);
declare @cycle_time datetime;
declare @pick_time datetime;
declare @current_value numeric(22,10);
DECLARE cur_eq_rt_tmp CURSOR FORWARD_ONLY STATIC for select data_guid,org_id,eq_guid,cycle_time,pick_time,current_value from [dbo].[tb_data_eq_rt_tmp];
begin
SET NOCOUNT ON;
set xact_abort on;
set @sysdate=getdate();
begin tran; BEGIN TRY
open cur_eq_rt_tmp --打开游标
FETCH NEXT FROM cur_eq_rt_tmp INTO @data_guid,@org_id,@eq_guid,@cycle_time,@pick_time,@current_value;
while(@@fetch_status=0) --判断fetch语句是否执行成功
begin
insert into [dbo].[tb_data_eq_rt_tmp2] values(@data_guid,@org_id,@eq_guid,@cycle_time,@pick_time,@current_value);
FETCH NEXT FROM cur_eq_rt_tmp INTO @data_guid,@org_id,@eq_guid,@cycle_time,@pick_time,@current_value;
end
close cur_eq_rt_tmp --关闭游标
deallocate cur_eq_rt_tmp --释放游标
commit;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN --回滚事务
select @Err_Procedure=ERROR_PROCEDURE(),@Err_Line=ERROR_LINE(),@Err_Number=ERROR_NUMBER(),@Err_Message='存储过程测试:'+ERROR_MESSAGE(),@Err_Severity=ERROR_SEVERITY(),@Err_State=ERROR_STATE();
exec loopback.[RCET3.0].[RCET_SYS].[P_ADD_PRO_LOG] '',@Err_Procedure,@Err_Line,@Err_Number,@Err_Message,@Err_Severity,@Err_State,@sysdate;
END CATCH
end;
过程:将tb_data_eq_rt_tmp的数据取出来插入tb_data_eq_rt_tmp2表中,我测试了3万条记录,结果,用临时表需要35秒左右,用游标基本上是1秒多点我在网上看到很多测试都是使用临时表快,我这里怎么是反的
临时表用自增ID为主键,这个应该就可以是索引了吧,因为我代码中检索获取数据也是根据id来获取的。
我目前的开发中所遇到的场景就是这样的,取出数据做判断处理!我的主要目的是对比这种场景下,用临时表和用传统游标的效率,网上很多测试都是临时表的效率高,所以我才觉得奇怪的。至于你说的临时表的其他优势,目前还没有去学习,谢谢你的指点!
运行并观察下面的代码select identity(int, 1, 1) xid, name, object_id into temp1 from sys.objects
go
sp_help temp1
go
alter table temp1 add primary key (xid)
go
sp_help temp1
go
drop table temp1
go