SQLServer中存储过程的执行效率问题(100分)

[blue]我的存储过程较大,里面有一个While循环,在QueryAnalyzer中执行时,
发现第一次循环执行较快,第二次时速度很慢,以后都很快,不知道是什么原因,
请各位帮忙看看[/blue]

能把你的code贴出来看看吗?

我感觉好像是SQLServer在做缓存或者编译之类的东西，会先用掉大约30秒，
然后很快的执行，执行一会后又占用大概30秒……
CREATE procedure spCareIdx(
@CrtUnit Char(9),
@NowDate0 dateTime,
@BgnDate dateTime,
@EndDate dateTime,
@ManChked int output,
@WmnChked int output,
@WmnIllChked int output,
@BornDeadPe decimal(7,6) output,
@BabyDeadPe decimal(7,6) output,
@5DeadPe decimal(7,6) output,
@BabyIllPe decimal(7,6) output,
@PregDeadPe decimal(7,6) output
)
as
declare
@iCnt0 int,
@iCnt1 int,
@iFirs int,
@iSeel int,
@cTmpHId char(19),
@iRegi int,
@cRegi char(2),
@iErr int,
@Item0 int,
@Item5 int,
@Item10 int,
@Item15 int,
@Item20 int,
@Item25 int,
@Item30 decimal(6,5),
@Item31 int,
@Item35 int,
@Item40 int,
@Item45 int,
@Item50 int,
@ItemBorn int,
@Item54 int,
@Item55 decimal(6,5),
@Item56 int,
@NowDate datetime,
@iTmpCnt int,
@specHosp1 varchar(50),
@specHosp2 varchar(50),
@TmpBuildDate datetime,
@basDate datetime,
@TmpBirthDate datetime,
@TmpExamDate0 datetime,
@TmpExamDate1 datetime,
@iAge int,
@iInterval int,
@iItem10 int,
@iTmpAge int,
@IsSysEd int,
@TmpItem56 int
set @specHosp1='本市'
set @specHosp2='非本市'
set @NowDate=@NowDate0
set @iRegi=0
set @TmpItem56=0
begin transaction
select @iRegi=1
while @iRegi<4
begin
select
@Item0=0,@Item5=0,@Item10=0,@Item15=0,@Item20=0,@Item25=0,
@Item30=0,@Item31=0,@Item35=0,@Item40=0,@Item45=0,@Item50=0,@iCnt0=0,@Item54=0,@Item56=0
set @cRegi='0'+convert(char(1),@iRegi)
set @iCnt0=
(
select count(brth.hid) from brth,care
where (brth.hid=care.hid and care.registertype=@cRegi)
and (brth.item150=@crtUnit)
and (brth.item20 between @bgnDate and @endDate)
--and (brth.item45<>NULL)
and ((brth.item25>=28) or (((brth.item25 is null) or (brth.item25=0)) and (brth.item95>=1000)))
)
select @iCnt1=count(b.hid) from
(select distinct a.hid from
(
select brth.hid from brth,care
where (brth.hid=care.hid and care.registertype=@cRegi)
and (brth.item150=@crtUnit)
and (brth.item20 between @bgnDate and @endDate)
and (brth.item130='02' or brth.item130='03')
and (brth.item150=@crtUnit)and ((brth.item25>=28) or (((brth.item25 is null) or (brth.item25=0)) and (brth.item95>=1000))) --deng 加上运周限制
union
select cdth.hid from cdth,care,brth
where (cdth.hid=care.hid and care.registertype=@cRegi)
and (cdth.Hospital=@crtUnit)
and (cdth.item1 between @bgnDate and @endDate)
and (cdth.item5='01' or cdth.item5='02')
and (cdth.hid=brth.hid)and ((brth.item25>=28) or (((brth.item25 is null) or (brth.item25=0)) and (brth.item95>=1000))) --deng 加上运周限制
)a
)b
select @Item0=@iCnt0-@iCnt1

set @Item5=
(
select count(wDth.hid) from wDth,care,brth
where (wDth.hid=care.HId and care.registertype=@cRegi)
and (wDth.item80=@crtUnit)
and (wDth.item1 between @bgnDate and @endDate)
and wDth.hid=brth.hid
)

select @Item10=count(b.hid) from
(select distinct a.hid from
(
select cdth.hid from cdth,care,brth,cbas
where (cdth.hid=care.hid and care.registertype=@cRegi)
and (cdth.Hospital=@crtUnit)
and (cdth.item1 between @bgnDate and @endDate)
and (brth.item150=@crtUnit)and ((brth.item25>=28) or (((brth.item25 is null) or (brth.item25=0)) and (brth.item95>=1000))) --deng 加上运周限制
and (cdth.item5='01' or cdth.item5='02' or (cdth.item5='03' and cDth.Hid=cBas.Hid and datediff(dd,cBas.birthday,cDth.item1)<=7))
union
select brth.hid from brth,care
where (brth.hid=care.hid and care.registertype=@cRegi)
and (brth.item150=@crtUnit)
and (brth.item20 between @bgnDate and @endDate)
and (brth.item130='03' or brth.item130='02'or brth.item130='04')
and (brth.item150=@crtUnit)and ((brth.item25>=28) or (((brth.item25 is null) or (brth.item25=0)) and (brth.item95>=1000))) --deng 加上运周限制
)a
)b
set @Item15=
(
select count(cdth.hid) from cdth,cBas,care
where cdth.item1 between @bgnDate and @endDate
and (cdth.hid=care.hid and care.registertype=@cRegi)
and (cdth.hospital=@crtUnit)
and (cdth.hid=cbas.hid and datediff(dd,cbas.birthday,cdth.item1)<365)
)
set @Item20=
(
select count(cdth.hid) from cdth,cBas,care
where cdth.item1 between @bgnDate and @endDate
and (cdth.hid=care.hid and care.registertype=@cRegi)
and (cdth.hospital=@crtUnit)
and (cdth.hid=cbas.hid and datediff(dd,cbas.birthday,cdth.item1)>=0 and datediff(dd,cbas.birthday,cdth.item1)<365*5)
)
set @Item25=
(
select count(cPsf.hid) from cPsf,care
where cPsf.item1 between @bgnDate and @endDate
and (cPsf.hid=care.hid and care.registertype=@cRegi)
and (cPsf.hospital=@crtUnit)
)
set @iCnt0=
(select count(brth.Hid) from brth,care
where (brth.hid=care.hid and care.registertype=@cRegi)
and (brth.item150=@crtUnit)
and (brth.item20 between @bgnDate and @endDate)
and (care.hid2 is null or care.hid2='')
)
declare cr0 cursor
for
select distinct (brth.Hid) from brth,care, Firs
where (brth.hid=care.hid and care.registertype=@cRegi)
and (brth.item150=@crtUnit)
and (brth.item20 between @bgnDate and @endDate)
and (brth.item170='01')
and Firs.item1 <=12
and Firs.hid =* brth.hid
open cr0
fetch next from cr0 into @cTmpHid
while @@fetch_status=0
begin
declare cr2 cursor
for
select count(hid) from rChk
where hid=@cTmpHid
open cr2

fetch next from cr2 into @iFirs
deallocate cr2
declare cr2 cursor
for

select count(item1) from seel
where hid=@cTmpHid
open cr2
fetch next from cr2 into @iSeel
deallocate cr2
if (@iFirs>=8 and @iSeel>=3)
select @Item31=@Item31+1
fetch next from cr0 into @cTmpHid
end
deallocate cr0
if @Item0=0
set @Item30=0
else
set @Item30=convert(float,@Item31)/convert(float,@Item0)
set @Item35=
(
select count(cutbear.hid) from cutbear,cutBearPeople
where cutbearPeople.BuildDate between @bgnDate and @endDate
and (cutbear.createunit=@crtUnit)
and (cutbear.hid=cutBearPeople.hid and cutBearPeople.registertype=@cRegi)
)

set @Item40=
(
select count(mancheck.hid) from mancheck,husband
where husband.BuildDate between @bgnDate and @endDate
and (mancheck.createunit=@crtUnit)
and (mancheck.hid=husband.hid and husband.registertype=@cRegi)
)
set @Item45=
(
select count(womencheck.hid) from womencheck,wife
where wife.BuildDate between @bgnDate and @endDate
and (womencheck.createunit=@crtUnit)
and (womencheck.hid=wife.hid and wife.registertype=@cRegi)
)
set @Item50=
(
select count(womencure.hid) from womencure,womenCurePeople
where womencurePeople.BuildDate between @bgnDate and @endDate
and (womencure.createunit=@crtUnit)
and (womencure.hid=womenCurePeople.hid and womenCurePeople.registertype=@cRegi)
)

set @ItemBorn=
(
select count(brth.Hid) as Lifes from brth,care
where brth.hid=care.Hid and care.registerType=@cRegi
and (brth.item20 between @bgnDate and @endDate )
and (brth.item150=@crtUnit) and ((brth.item25>=28) or (((brth.item25 is null) or (brth.item25=0)) and (brth.item95>=1000)))
)
set @Item54=0

--------------
set @iAge=0
set @Item56=0
if datediff(dd, @BgnDate, @EndDate) > 360
begin
while @iAge<7
begin
exec procChdIdx @CrtUnit,@EndDate,@cRegi,@iAge,@TmpItem56 output
set @Item56=@Item56+@TmpItem56
set @iAge=@iAge+1
end
end
set @Item55=0

insert into rCareIdx values(@crtUnit,@Nowdate,'',@cRegi,@bgnDate,@endDate,
@Item0,@Item5,@Item10,@Item15,@Item20,@Item25,@iCnt0,
@Item30,@Item31,@Item35,@Item40,@Item45,@Item50,@ItemBorn,@Item54,@Item55,@Item56)
set @iErr=@@error
if @iErr<>0
begin
rollback
return (@iErr)
end
set @iRegi=@iRegi+1
end
set @Item0=(select sum(item0) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item5=(select sum(item5) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item10=(select sum(item10) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item15=(select sum(item15) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item20=(select sum(item20) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item25=(select sum(item25) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @iCnt0=(select sum(item29) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item31=(select sum(item31) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item35=(select sum(item35) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item40=(select sum(item40) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item45=(select sum(item45) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item50=(select sum(item50) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @ItemBorn=(select sum(itemBorn) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item54=(select sum(item54) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item56=(select sum(item56) from rCareIdx where regType in('01','02') and CreateDate=@NowDate0 and createUnit=@crtUnit)
if @Item0=0
set @Item30=0
else
set @Item30=convert(float,@Item31)/convert(float,@Item0)
if @ItemBorn=0
set @BornDeadPe=0
else
set @BornDeadPe=convert(float,@Item10)/convert(float,@ItemBorn) --1129

if @Item0=0
set @BabyDeadPe=0
else
set @BabyDeadPe=convert(float,@Item15)/convert(float,@Item0)

if @Item0=0
set @5DeadPe=0
else
set @5DeadPe=convert(float,@Item20)/convert(float,@Item0)

if @Item0=0
set @BabyIllPe=0
else
set @BabyIllPe=convert(float,@Item25)/convert(float,@Item0)
if @Item0=0
set @PregDeadPe=0
else
set @PregDeadPe=convert(float,@Item5)/convert(float,@Item0)

set @Item55=0

set @ManChked=@Item40
set @WmnChked=@Item45
set @WmnIllChked=@Item50
insert into rCareIdx values(@crtUnit,@Nowdate,'','12',@bgnDate,@endDate,
@Item0,@Item5,@Item10,@Item15,@Item20,@Item25,@iCnt0,
@Item30,@Item31,@Item35,@Item40,@Item45,@Item50,@ItemBorn,@Item54,@Item55,@Item56)
set @iErr=@@error
if @iErr<>0
begin
rollback
return (@iErr)
end

set @Item0=(select sum(item0) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item5=(select sum(item5) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item10=(select sum(item10) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item15=(select sum(item15) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item20=(select sum(item20) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item25=(select sum(item25) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @iCnt0=(select sum(item29) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item31=(select sum(item31) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item35=(select sum(item35) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item40=(select sum(item40) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item45=(select sum(item45) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item50=(select sum(item50) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @ItemBorn=(select sum(itemBorn) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item54=(select sum(item54) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
set @Item56=(select sum(item56) from rCareIdx where regType in('01','02','03') and CreateDate=@NowDate0 and createUnit=@crtUnit)
if @Item0=0
set @Item30=0
else
set @Item30=convert(float,@Item31)/convert(float,@Item0)
if @ItemBorn=0
set @BornDeadPe=0
else

set @BornDeadPe=convert(float,@Item10)/convert(float,@ItemBorn) --1129

if @Item0=0
set @BabyDeadPe=0
else
set @BabyDeadPe=convert(float,@Item15)/convert(float,@Item0)

if @Item0=0
set @5DeadPe=0
else
set @5DeadPe=convert(float,@Item20)/convert(float,@Item0)

if @Item0=0
set @BabyIllPe=0
else
set @BabyIllPe=convert(float,@Item25)/convert(float,@Item0)
if @Item0=0
set @PregDeadPe=0
else
set @PregDeadPe=convert(float,@Item5)/convert(float,@Item0)
set @Item55=0

set @ManChked=@Item40
set @WmnChked=@Item45
set @WmnIllChked=@Item50
insert into rCareIdx values(@crtUnit,@Nowdate,'','13',@bgnDate,@endDate,
@Item0,@Item5,@Item10,@Item15,@Item20,@Item25,@iCnt0,
@Item30,@Item31,@Item35,@Item40,@Item45,@Item50,@ItemBorn,@Item54,@Item55,@Item56)
set @iErr=@@error
if @iErr<>0
begin
rollback
return (@iErr)
end

commit