怎么会这么慢?
自己试着编的小东西,在一个5万条记录的表里一秒一条记录,应该还能更快,请高手给指点下,哪里写的不好,还可以优化呀,请多指教!
declare @t int,@c int,@d int,@d1 int,@x int,@j numeric,@m numeric,@m1 numeric,@h int,@h1 int,@h2 int,@kh1 as char(10)
declare @yyy as char(10),@z int,@z1 int
declare @ddd as int
declare @str varchar(4)
declare @i varchar(10),@ii varchar(10)
declare @p varchar(50)
declare @t1 int,@tt int
declare @tttt4 table(产品编号 char(13),票号 char(16),序号 int,客户 char(8))
set @yyy= '20040101 '--月份
set @str= 'A01C '
while cast(substring(@yyy,5,2) as int) <13
begin
set @c=0 /*在下面输入每月的金额*/
set @m=(case when substring(@yyy,5,2)= '01 ' then 2863755 when substring(@yyy,5,2)= '02 ' then 3400709 when substring(@yyy,5,2)= '03 ' then 3153385 when substring(@yyy,5,2)= '04 ' then 3215216 when substring(@yyy,5,2)= '05 ' then 3246132 when substring(@yyy,5,2)= '06 ' then 4142682 when substring(@yyy,5,2)= '07 ' then 3709865 when substring(@yyy,5,2)= '08 ' then 3579694 when substring(@yyy,5,2)= '09 ' then 3508100 when substring(@yyy,5,2)= '10 ' then 3400709 when substring(@yyy,5,2)= '11 ' then 4617806 when substring(@yyy,5,2)= '12 ' then 5550144 end)
set @j=round(@m/cast(day(dateadd(mm,1,@yyy)-1) as int),2)--日平均金额
set @ddd=0
while (select cast(day(dateadd(mm,1,@yyy)-1) as int))> @ddd
begin
set @d1=1
set @m1=0
set @i=substring(convert(varchar(8),cast(convert(varchar,(@yyy+@ddd),112) as datetime)-30*cast(6+rand()*(9-6) as int),112),3,6)
set @ii=substring(convert(varchar(8),cast(convert(varchar,(@yyy+@ddd),112) as datetime)-5,112),3,6)
while @j> @m1
begin
SET @h1=0
set @d=cast(1+rand()*(4-1) as int)
set @kh1=(case when @d=1 then (select top 1 客户编码 from kh where 档案号= 'A ' order by newid()) when @d=2 then (select top 1 客户编码 from kh where 档案号 in( 'B ', 'C ') order by newid()) end)
set @p=(@str+left(convert(varchar,(@yyy+@ddd),112),8)+(case when len(@d1)=1 then '000 ' when len(@d1)=2 then '00 ' when len(@d1)=3 then '0 ' end)+cast(@d1 as char(4)))
if @d=1
begin
set @t1=cast(1+rand()*(20-1) as int)
set @x=1
set @c=0
while @t1> @c
begin
set @z=cast(1+rand()*(5-1) as int)
insert into yscc(票号,序号,小号,产品编号,库房,批号,效期,标识,库房数量,数量,件数,保管员,审核时间,业务1,业务2,业务3,业务4,业务5,单价,金额,入库票号,入库序号) select top 1 @p,@x, '1 ',yfcc.产品编号,yfcc.库房,yfcc.批号,yfcc.效期,yfcc.标识,yfcc.库房数量,10, '1 ',@kh1,getdate(), ' ', ' ', ' ', ' ', ' ',round(单价,2),round(round(单价,2)*10,2),票号,序号 from yfcc where(left(批号,6) between @i and @ii) and cast(convert(varchar,(@yyy+@ddd),112) as datetime) <效期 and yfcc.库房数量> 0 and substring(yfcc.票号,5,8)> substring(@p,5,8) and yfcc.产品编号 not in(select 产品编号 from @tttt4) order by newid()
insert into @tttt4(产品编号,票号,序号,客户) select 产品编号,入库票号,入库序号,保管员 from yscc where 票号=@p and 序号=@x
set @c=@c+1
set @x=@x+1
if @t1 <=@c
break
else
continue
end
end
if @d=2
begin
set @t1=cast(1+rand()*(8-1) as int)
set @x=1
set @c=0
while @t1> @c
begin
set @z=cast(1+rand()*(3-1) as int)
insert into yscc(票号,序号,小号,产品编号,库房,批号,效期,标识,库房数量,数量,件数,保管员,审核时间,业务1,业务2,业务3,业务4,业务5,单价,金额,入库票号,入库序号) select top 1 @p,@x, '1 ',yfcc.产品编号,yfcc.库房,yfcc.批号,yfcc.效期,yfcc.标识,yfcc.库房数量,10, '1 ',@kh1,getdate(), ' ', ' ', ' ', ' ', ' ',round(单价,2),round(round(单价,2)*10,2),票号,序号 from yfcc where(left(批号,6) between @i and @ii) and cast(convert(varchar,(@yyy+@ddd),112) as datetime) <效期 and yfcc.库房数量> 0 and substring(yfcc.票号,5,8)> substring(@p,5,8) and yfcc.产品编号 not in(select 产品编号 from @tttt4) order by newid()
insert into @tttt4(产品编号,票号,序号,客户) select 产品编号,入库票号,入库序号,保管员 from yscc where 票号=@p and 序号=@x
set @c=@c+1
set @x=@x+1
if @t1 <=@c
break
else
continue
end
end
set @d1=@d1+1
set @m1=(select isnull(sum(金额),0) from yscc where substring(票号,5,8)=left(convert(char,(@yyy+@ddd),112),8))
if @j <@m1
break
else
continue
end
set @ddd=@ddd+1
delete @tttt4
if cast(day(dateadd(mm,1,@yyy)-1) as int) <(cast(@ddd as int))
break
else
continue
end
set @yyy= '2004 '+rtrim(ltrim((case when len(cast(substring(@yyy,5,2) as int))=1 and substring(@yyy,5,2) <> '09 ' then '0 '+convert(char,(cast(substring(@yyy,5,2) as int)+1)) else convert(char,(cast(substring(@yyy,5,2) as int)+1)) end )))+ '01 '
if cast(substring(@yyy,5,2) as int)> 13
break
else
continue
end
[解决办法]
把你的代码用 SQL2005 的数据库引擎优化顾问 分析下
