一个考勤处理的SQL过程,编完运行速度太慢了,用了游标,请大家帮忙给修改一下啊.现在350人,一个月的考勤,处理居然要30分钟.代码请贴子查看. ( 积分:

tl_lyq · 2007-11-23

一个考勤处理的SQL过程,编完运行速度太慢了,用了游标,请大家帮忙给修改一下啊.现在350人,一个月的考勤,处理居然要30分钟.代码请贴子查看. ( 积分: 40 ) CREATE PROCEDURE [dbo].[KqCalc]    @sdate datetime,@edate datetime    AS set nocount on  declare   @tmpdate0  datetime,@nmonths int IF @edate<@sdate    begin   set   @tmpdate0=@sdate  set   @sdate=@edate  set   @edate=@tmpdate0   end   set  @nmonths=year(@edate+1)*12+MONTH(@edate+1)-YEAR(@sdate-1)*12-MONTH(@sdate-1)+1 declare    @tmpdate varchar(20),  @tmpdate1 datetime,@currqpre varchar(10),@currq varchar(10) ,@currqnext varchar(20),@csjfwa1 varchar(20),@csjfwa2 varchar(20),@csjfwb1 varchar(20),@csjfwb2 varchar(20),@sjtmp varchar(20),   @ndays int,@i int,@j int,@y int,@m int,@tmpval int,   @lqj int,@csjfwa1_i bigint,@csjfwa2_i bigint,@csjfwb1_i bigint,@csjfwb2_i bigint ,   @cjcsj varchar(20),@mc varchar(20),@sj1 varchar(10),@sj2 varchar(10),@sj3 varchar(10),@sbfw varchar(10),@xbfw varchar(10),@jbsj varchar(10), @cjbsj varchar(10),   @bh varchar(20) ,@kh varchar(20) ,@bm varchar(20) ,@xm varchar(20) ,@bc varchar(20) ,@cfield varchar(20), @sqlstr nvarchar(4000),@bcsm varchar(20),   @okcs numeric(5,2),@qqcs numeric(5,2) ,@ztfk numeric(5,2),@cdfk numeric(5,2),@cqts numeric(5,2),@qqfk numeric(5,2),@cclx numeric(5,1),@sj numeric(5,1),@sj1a varchar(20),@sj1b varchar(20),@sj2a varchar(20),@sj2b varchar(20),@cdsj int,@ztsj int,@kjyear int,@kjmonth int   set @i=0 set @j=0 set @csjfwa1_i=0 set @csjfwb1_i=0 set @csjfwa2_i=0 set @csjfwb2_i=0   set @kjyear=year(@sdate) set @kjmonth=month(@sdate) --初始化 CREATE TABLE #kqtjtmp(bh varchar(10), kh varchar(20), xm varchar(10), rq varchar(12),sj1a varchar(10) not null default '', sj1b varchar(10) not null default '', sj2a varchar(10) not null default '', sj2b varchar(10) not null default '', sj3a varchar(10) not null default '' ,sj3b varchar(10) not null default '', cdsj int not null default 0, ztsj int not null default 0, jbsj int not null default 0, sbsj int not null default 0, bcsm varchar(50) not null default '', bm varchar(50) not null default '' &nbsp

create table #kqhz(cclxdays numeric(10,1), qjdays numeric(10,1), cqdays numeric(10,1), jsdays numeric(10,1), cdfk numeric(10,1), ztfk numeric(10,1), qqfk numeric(10,1) &nbsp

IF EXISTS (SELECT id  FROM tempdb..sysobjects   WHERE name = '##count') DROP TABLE ##count create table ##count(id int) select distinct  bh,kh,convert(varchar(8),rq,112) rq, REPLACE(CONVERT(char(5), sj, 8), ':', '') sj into #datatmp from rlkh.dbo.yskq where rq between @sdate and @edate order by bh,kh,rq create index  rqsj  ON #datatmp(rq) create table #sjtmp (sj varchar(20))  SELECT bc, Rq, sj1, sj2, sbfw, xbfw into #kqsj_bb FROM rlkh.dbo.kqsj_bb where rq between @sdate and @edate SELECT xmbh, rq, zl, lx into #qj FROM tl.dbo.qj where rq between @sdate and @edate delete from kqhz where  kjyear=@kjyear and kjmonth=@kjmonth delete from kqmx where year(rq)=@kjyear  and month(rq)=@kjmonth set @ndays= datediff(day,@sdate,@edate)  --根据人事库处理考勤 declare  Emp_cur  cursor for  Select bh,kh,bm,xm,bc  From  rlkh.dbo.employee  where kh<>''  order by bh open  Emp_cur fetch next from Emp_Cur into @bh,@kh,@bm,@xm,@bc while  (@@FETCH_STATUS = 0) begin         set @j=0 while @j<=@ndays  begin                     --开始j set @currqpre=convert(varchar(8),@sdate+@j-1,112) set @currq= convert(varchar(8), @sdate+@j,112)           set @currqnext=convert(varchar(8), @sdate+@j+1,112)                 delete from #sjtmp                  insert into #sjtmp  select rq+sj  FROM #datatmp WHERE bh=@bh AND rq between   @currqpre and  @currqnext    INSERT INTO #kqtjtmp (kh,xm,rq,bm,bh) VALUES(@kh,@xm,@currq,@bm,@bh) set @mc='' select @mc=mc  from  rlkh.dbo.jjr   where rq=@currq if @mc<>''         update #kqtjtmp  set  bcsm='(节:'+@mc+')'    else begin select @mc=zl+lx  from  #qj   where rq=@currq and xmbh=@bh  set @lqj=0 if @mc<>'' begin      update #kqtjtmp  set  bcsm='(请:'+@mc+')'      set @lqj=1 end select @sj1=sj1, @sj2=sj2, @sbfw=sbfw, @xbfw=xbfw  from #kqsj_bb where bc=@bc and rq=@currq IF EXISTS (SELECT id  FROM tempdb..sysobjects   WHERE name = '##A') DROP TABLE ##A create table ##A (id int ,a varchar(4),b varchar(4)&nbsp

insert into  ##A (id,a,b) values (1,LEFT(@sj1,4),RIGHT(@sj1,4)&nbsp

insert into  ##A (id,a,b) values (2,LEFT(@sj2,4),RIGHT(@sj2,4)&nbsp

insert into  ##A (id,a,b) values (3,LEFT(@sj3,4),RIGHT(@sj3,4)&nbsp

insert into  ##A (id,a,b) values (4,cast(LEFT(@sbfw,3) as integer),cast(RIGHT(@sbfw,3)as integer)) insert into  ##A (id,a,b) values (5,cast(LEFT(@xbfw,3) as integer),cast(RIGHT(@xbfw,3) as integer)) IF LEN(@jbsj)<>4 set @cjbsj='' ELSE set @cjbsj=@jbsj                    if (select count(sj) from #sjtmp)>0  begin set @i=1 while @i<=2 begin                                        set @csjfwa1_i=0 set @csjfwb1_i=0 set @csjfwa2_i=0 set @csjfwb2_i=0 set @sjtmp=''        if (select  len(a)+len(b) from ##A where id=@i) >= 4        begin  select   @csjfwa1_i=left(a,2)*60+right(a,2)-(select a from ##A where id=4) from ##A where id=@i set @tmpdate=@currq IF @csjfwa1_i<0 begin set @tmpdate=@currqpre set @csjfwa1_i=1440+@csjfwa1_i end ELSE IF @csjfwa1_i>1440 begin  set @tmpdate=@currqnext set @csjfwa1_i=@csjfwa1_i-1440 end set @csjfwa1=@tmpdate+replace(str(@csjfwa1_i/60, 2),' ','0')+replace(str(@csjfwa1_i % 60 ,2),' ','0') select   @csjfwb1_i=left(a,2)*60+right(a,2)+(select b from ##A where id=4) from ##A where id=@i set @tmpdate=@currq IF @csjfwb1_i>1440 begin set @tmpdate=@currqnext set @csjfwb1_i=@csjfwb1_i-1440 end set  @csjfwb1=@tmpdate+replace(str(@csjfwb1_i/60,2),' ' , '0')+replace(str(@csjfwb1_i % 60 ,2),' ','0') set @cfield='sj'+str(@i,1)+'a' select @sjtmp=sj from  #sjtmp where sj between  @csjfwa1 and  @csjfwb1 if @sjtmp<>'' begin /*set @lsb=1 set @y =year(LEFT(@sjtmp,8))+4800-(14- month(LEFT(@sjtmp,8))) / 12 set @m =month(LEFT(@sjtmp,8))+12*((14- month(LEFT(@sjtmp,8))) / 12)-3 set @nsbsj1= 1440*day(LEFT(@sjtmp,8))+(153*@m+2)/5+365*@y+@y/4-@y/100+@y/400-32045 +cast (SUBSTRing(@sjtmp,9,2) as integer)*60+cast(RIGHT(@sjtmp,2) as integer) */                                                         if @sjtmp>@currq+(select a from ##A where id=@i) begin --('+RIGHT(@sjtmp,4)+') set @sqlstr='update #kqtjtmp set '+@cfield+' = ''迟到''   '  exec sp_executesql @sqlstr set @tmpval=datediff(minute,@currq+' '+left((select a from ##A where id=@i),2)+':'+right((select a from ##A where id=@i),2)+':00',left(@sjtmp,8)+' '+left(right(@sjtmp,4),2)+':'+right(@sjtmp,2)+':00') update #kqtjtmp set cdsj = cdsj+@tmpval  end else begin --(''+RIGHT('+@sjtmp+',4)+'') set @sqlstr='update #kqtjtmp set '+@cfield+' = ''OK''  '  exec sp_executesql @sqlstr end end  else begin IF @lqj=0 begin set @sqlstr='update #kqtjtmp set '+@cfield+' =''--''   '  exec sp_executesql @sqlstr end end --开始11 IF   (select a from ##A where id=@i)<>(select b from ##A where id=@i)                                                 begin set @csjfwa2_i=cast(LEFT( (select b from ##A where id=@i) ,2) as integer)*60+cast(RIGHT((select b from ##A where id=@i) ,2) as integer)-(select a from ##A where id=5&nbsp

set @tmpdate1=@sdate+@j IF (select b from ##A where id=@i) < (select a from ##A where id=@i)          set @tmpdate1=@tmpdate1+1 set @tmpdate=convert(varchar(8),@tmpdate1,112) IF @csjfwa2_i<0   begin          set @tmpdate=convert(varchar(8),@tmpdate1-1,112) set @csjfwa2_i=1440+@csjfwa2_i end set @csjfwa2=@tmpdate+replace(str (@csjfwa2_i/60 ,2),' ','0')+replace(str(@csjfwa2_i % 60 ,2),' ','0') set @csjfwb2_i=cast(LEFT((select b from ##A where id=@i),2) as integer)*60+cast(RIGHT((select b from ##A where id=@i),2) as integer)+(select b from ##A where id=5) set @tmpdate=convert(varchar(8),@tmpdate1,112)   IF @csjfwb2_i>1440 begin set @tmpdate=convert(varchar(8),@tmpdate1+1,112)   set @csjfwb2_i=@csjfwb2_i-1440 end    set @csjfwb2=@tmpdate+replace(str(@csjfwb2_i/60,2),' ','0')+replace(str (@csjfwb2_i % 60,2),' ','0') set @cfield='sj'+str(@i ,1)+'b' select @sjtmp=sj from #sjtmp where sj between @csjfwa2 and @csjfwb2 --开始12 if (select count(sj) from #sjtmp where sj between @csjfwa2 and @csjfwb2)>0 begin /* IF @lsb=1 begin      set @nsbsj1= 1440*day(LEFT(@sjtmp,8))+(153*@m+2)/5+365*@y+@y/4-@y/100+@y/400-32045 + cast (SUBSTRing(@sjtmp,9,2) as integer)*60+cast(RIGHT(@sjtmp,2) as integer)-@nsbsj1 set @nsbsj1=@nsbsj1   IF @nsbsj1>0       set @nsbsj=@nsbsj+@nsbsj1 end */ IF @sjtmp<@tmpdate+ (select b from ##A where id=@i) begin set @tmpval=datediff(minute,@tmpdate+' '+left((select b from ##A where id=@i),2)+':'+right((select b from ##A where id=@i),2)+':00',left(@sjtmp,8)+' '+left(right(@sjtmp,4),2)+':'+right(@sjtmp,2)+':00')                      set @sqlstr='update #kqtjtmp set '+@cfield+' =''早退''   ' --('+RIGHT(@sjtmp,4)+') exec sp_executesql @sqlstr update #kqtjtmp set  ztsj =ztsj+@tmpval   --set @nztsjh=@nztsjh+@tmpval end else  begin  --('+RIGHT(@sjtmp,4)+') set @sqlstr='update #kqtjtmp set '+@cfield+' =''OK'''  exec sp_executesql @sqlstr end end ELSE begin IF @lqj=0 begin set @sqlstr='update #kqtjtmp set '+@cfield+' =''--'''  exec sp_executesql @sqlstr              end                                                            end                    end       end           set @i=@i+1 end  end  else begin IF @lqj=0 begin set @i=1 while @i<=2 begin    set @sqlstr='update #kqtjtmp set sj'+str(@i ,1)+'a =''--'',sj'+str(@i ,1)+'b =''--'' '     exec sp_executesql @sqlstr      set @i=@i+1 end end   end  end                     --统计                set @okcs=0 set @qqcs=0 set @qqfk=0 set @ztfk=0 set @cdfk=0 set @sj=0 set @cclx=0 set @cqts=0                   select @sj1a=sj1a,@sj1b=sj1b,@sj2a=sj2a,@sj2b=sj2b,@bcsm=bcsm from #kqtjtmp     if (@bcsm='(请:轮休整)' or @bcsm='(请:出差整)')  begin set @cclx=1 set @cqts=1 end   else if (@bcsm='(请:轮休半)' or @bcsm='(请:出差半)') begin set @cclx=0.5 set @cqts=1  end   else if (@bcsm='(请:事假整)') begin set @sj=1 set @cqts=0 end   else if (@bcsm='(请:事假半)') begin set @sj=0.5 set @cqts=0.5  end   else    begin         if @sj1a in ('ok','迟到','早退')  set @okcs=@okcs+1 else if @sj1a='--' set @qqcs=@qqcs+1     if @sj1b in ('ok','迟到','早退')  set @okcs=@okcs+1 else if @sj1b='--' set @qqcs=@qqcs+1     if @sj2a in ('ok','迟到','早退')  set @okcs=@okcs+1 else if @sj2a='--' set @qqcs=@qqcs+1     if @sj2b in ('ok','迟到','早退')  set @okcs=@okcs+1 else if @sj2b='--' set @qqcs=@qqcs+1      if @okcs in (1,2,3)  set @cqts=0.5  else if @okcs=4  set @cqts=1  else set @cqts=0     if @qqcs in (1,2,3) begin set @qqfk=10*@qqcs  set @cqts=1 end     else if @qqcs=4  set @cqts=0     end   if @cdsj>0 and @cdsj<=5 set @cdfk=1 else if (@cdsj >5 and @cdsj<=15) set @cdfk=2 else if (@cdsj >15 and @cdsj<=30) set @cdfk=5 else set @cdfk=0   if @ztsj > 0 and @ztsj<=30 set @ztfk=5  else set @ztfk=0   if exists(select cclxdays from #kqhz&nbsp

update #kqhz set cclxdays=cclxdays+@cclx,qjdays=qjdays+@sj,cqdays=cqdays+@cqts,cdfk=cdfk+@cdfk,ztfk=ztfk+@ztfk,qqfk=qqfk+@qqfk   else      insert into  #kqhz (cclxdays, qjdays, cqdays, jsdays, cdfk, ztfk, qqfk)  values(@cclx,@sj,@cqts,0,@cdfk,@ztfk,@qqfk)                         insert into kqmx (bh, kh, xm, rq, sj1a, sj1b, sj2a, sj2b,sj3a,sj3b,cdsj, ztsj,jbsj,sbsj, bcsm, bm) select * from #kqtjtmp                   if exists(select id from ##count)  update ##count set id=id+1 else insert into ##count (id)values(1)                   delete from #kqtjtmp    set @j=@j+1 end --结束j          insert into kqhz select @kjyear, @kjmonth, @xm, @bh, @bm, @kh, cclxdays, qjdays, cqdays, jsdays, cdfk, ztfk, qqfk, cdfk+ztfk+qqfk  from #kqhz         delete from #kqhz fetch next from Emp_Cur into @bh,@kh,@bm,@xm,@bc end close Emp_cur deallocate Emp_cur update  kqhz set jsdays=case when bm in ('棉一','棉二','织一','新车间') then 0 else  cast((cqdays / 6) as integer) end where  kjyear=@kjyear and kjmonth=@kjmonth GO

tl_lyq · 2007-11-23

代码折行了,可以拷到记事本看, 劳驾哪位高手啊...

meng_xia_xue · 2007-11-24

1.做大量数据处理 游标尽量少用,影响性能 2.考虑用UPDATE (按条件分几次) 3.正式更新时加上事务处理    (     insert into kqhz select @kjyear, @kjmonth, @xm, @bh, @bm, @kh, cclxdays, qjdays, cqdays, jsdays, cdfk, ztfk, qqfk, cdfk+ztfk+qqfk  from #kqhz 4.增加错误处理

tl_lyq · 2007-11-24

因为计算比较麻烦，不用游标不太好处理啊。。。如果用事务处理，会快些吗？我试试看

ycluo · 2007-11-24

不要用游标，最好用临时表，这样速度就快多了

3868474 · 2007-11-24

事务处理和速度无关, 如果处理时间长加上事务还容易发生死锁. 改善一下索引,速度可以加快

tl_lyq · 2007-11-25

谢谢各位啊,问题解决了,是我循环里边处理的不太好. 班次放外边去就可以了.原来要用30分钟,现在2分多就处理完了. 下边这段,放循环外边就OK了.这个考勤处理直接采集考勤机的原始考勤,然后生成汇总后的考勤 总算完工了 ............... select @sj1=sj1, @sj2=sj2, @sbfw=sbfw, @xbfw=xbfw  from #kqsj_bb where bc=@bc and rq=@currq IF EXISTS (SELECT id  FROM tempdb..sysobjects   WHERE name = '##A') DROP TABLE ##A create table ##A (id int ,a varchar(4),b varchar(4)&nbsp

insert into  ##A (id,a,b) values (1,LEFT(@sj1,4),RIGHT(@sj1,4)&nbsp

insert into  ##A (id,a,b) values (2,LEFT(@sj2,4),RIGHT(@sj2,4)&nbsp

insert into  ##A (id,a,b) values (3,LEFT(@sj3,4),RIGHT(@sj3,4)&nbsp

insert into  ##A (id,a,b) values (4,cast(LEFT(@sbfw,3) as integer),cast(RIGHT(@sbfw,3)as integer)) insert into  ##A (id,a,b) values (5,cast(LEFT(@xbfw,3) as integer),cast(RIGHT(@xbfw,3) as integer))

一个考勤处理的SQL过程,编完运行速度太慢了,用了游标,请大家帮忙给修改一下啊.现在350人,一个月的考勤,处理居然要30分钟.代码请贴子查看. ( 积分:

tl_lyq

Unregistered / Unconfirmed

tl_lyq

Unregistered / Unconfirmed

meng_xia_xue

Unregistered / Unconfirmed

tl_lyq

Unregistered / Unconfirmed

ycluo

Unregistered / Unconfirmed

3868474

Unregistered / Unconfirmed

tl_lyq

Unregistered / Unconfirmed

Similar threads

一个考勤处理的SQL过程,编完运行 速度太慢了,用了游标,请大家帮忙给修改一下啊.现在350人,一个月的考勤,处理居然要30分钟.代码请贴子查看. ( 积分:

tl_lyq

Unregistered / Unconfirmed

tl_lyq

Unregistered / Unconfirmed

meng_xia_xue

Unregistered / Unconfirmed

tl_lyq

Unregistered / Unconfirmed

ycluo

Unregistered / Unconfirmed

3868474

Unregistered / Unconfirmed

tl_lyq

Unregistered / Unconfirmed

Similar threads

一个考勤处理的SQL过程,编完运行速度太慢了,用了游标,请大家帮忙给修改一下啊.现在350人,一个月的考勤,处理居然要30分钟.代码请贴子查看. ( 积分: