Difference execution time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Difference execution time

Author

Topic

khan_ing
Starting Member

2 Posts

Posted - 2005-04-28 : 04:48:15

hi,
i create a store procedure to execute my sql statement, but it took a lot of time to finish , and lately it become unfinish job. when i extract the query and run it on sql analyzer, it just took a couple of minutes. Theres is any explanation of it ???

for illustration :

exec sp_generate_something @id = '01' - > when i exec it on sql analyzer, it will take hours to finish. But when i take out the query ,
example :

declare @id char(10)

select @id = '01'

sql logic statment for bisnis proses

that statement took just a couple of minutes.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-04-28 : 05:04:19

Post the full query used in stored Procedure

Madhivanan

Failing to plan is Planning to fail

khan_ing
Starting Member

2 Posts

Posted - 2005-04-28 : 05:13:56

create proc dbo.spr_lakeu_create_Rencana_Bayar_BP3
@priod char(4), @kdsem char(1), @kdfak char(2), @kdjur char(2), @jenis char(4),
@tglpmb1 char(10), @tagih numeric, @usrid char(50), @status char(1), @nimhspr char(10)
as
--declare @priod char(4), @kdsem char(1), @kdfak char(2), @kdjur char(2), @jenis char(4), @tglpmb1 char(10), @tagih numeric, @usrid char(50), @status char(1), @nimhspr char(10)

set nocount on
-- select @priod = '2005', @kdsem = '1', @kdfak ='AL', @kdjur = 'AL', @jenis = '0001' , @tglpmb1 = '07-04-2005' , @tagih = 100 , @usrid = 'Wihendro' , @status = '2' , @nimhspr ='0600633895'

declare @nopmb char(13), @nimhs char(10), @nlbyr numeric, @tglpmb datetime, @kdjur1 char(2), @nopmblm char(13), @terbayar numeric
, @nopmbtgsatu char(13) -- nopmb tagihan pertama
, @nlbyrtgsatu numeric -- pembayaran untuk tagihan pertama
if @tglpmb1 is not null set @tglpmb=convert(datetime, @tglpmb1, 105)
-- blok registrasi
select nimhs into #delete from transaksi_blok_registrasi
where stsrc='A' and priod = @priod and kdsem= @kdsem and sttunda not in ( '1','3')
and nimhs = case when @nimhspr = '' then nimhs else @nimhspr end
-- blok registrasi

select a.nimhs,
nlbyr = case when (d.stsrc<>'R') or (d.stsrc is null)then --kdfak in ( '02','04','05') then
case @jenis
when '0001' then (coalesce(a.tgbp3,0) - (coalesce(a.pmbp3, 0)-coalesce(e.nlbyr, 0))) * @tagih * 1.0 / 100
when '0002' then (coalesce(a.tgsks,0) - (coalesce(a.pmsks, 0)-coalesce(e.nlbyr, 0))) * @tagih * 1.0 / 100
when '0003' then (coalesce(a.tgdp3,0) - (coalesce(a.pmdp3, 0)-coalesce(e.nlbyr, 0))) * @tagih * 1.0 / 100
end
else
case @jenis
when '0001' then (coalesce(a.tgbp3,0) - (coalesce(a.pmbp3, 0)-coalesce(e.nlbyr, 0)))
when '0002' then (coalesce(a.tgsks,0) - (coalesce(a.pmsks, 0)-coalesce(e.nlbyr, 0)))
when '0003' then (coalesce(a.tgdp3,0) - (coalesce(a.pmdp3, 0)-coalesce(e.nlbyr, 0)))
end
end,
b.kdjur, thang
into #2
from (select * from mastag with (nolock)
where priod=@priod and kdsem=@kdsem and stsrc='A' and len(nimhs) = 10
and nimhs = case when @nimhspr='' then nimhs else @nimhspr end ) a
join (select nimhs, kdfak, kdjur, thang from msmhs1 where stsrc = 'A' -- and stkul not in ('D','W' , 'L' )
and kdfak = case when @nimhspr<> '' then kdfak else case when @kdfak='AL' then kdfak else @kdfak end end
and kdjur = case when @nimhspr<> '' then kdjur else case when @kdjur='AL' then kdjur else @kdjur end end
and nimhs = case when @nimhspr<>'' then @nimhspr else nimhs end
and thang > '1996' -- and kdjur not in ( '36' )
)b on a.nimhs = b.nimhs
left join transaksi_krss d on d.stsrc in ('A','T','R') and d.priod=@priod and d.kdsem=@kdsem and a.nimhs=d.nimhs
-- left join trrek c on a.nimhs = c.nimhs and c.stsrc = 'A'
left join (select a.nimhs, nlbyr=sum(b.nlbyr) from transaksi_rencana_pembayaran a
join transaksi_pembayaran b
on a.priod=b.priod and a.kdsem=b.kdsem and a.nimhs=b.nimhs and a.nopmb=b.nopmb and a.kdpmb=b.kdpmb
and b.stsrc='A' and b.nokwc is null
where a.priod=@priod and a.kdsem=@kdsem and a.stsrc='A' and a.kdpmb=@jenis and a.status=@status
group by a.nimhs) e
on a.nimhs=e.nimhs

delete #2 where nlbyr <= 0

-- yg mhs baru gak ditagih
if @status<>'0'
begin
if @kdsem='1'
delete #2 where thang=@priod
else
delete #2 where thang =@priod
and nimhs in (select nimhs from tabel_transfer_jurusan with(nolock) where stsrc = 'a' )
-- international class tidak ditagih
delete #2
where kdjur in ('25','26','43','44')
end

insert into #delete
select distinct a.nimhs
from #2 a inner join transaksi_wisuda_mahasiswa b with (Nolock)
on a.nimhs = b.nimhs and b.stsrc <> 'D'
--
insert into #delete
select distinct a.nimhs
from #2 a inner join transaksi_pengunduran_diri_mahasiswa b with (Nolock)
on a.nimhs = b.nimhs and b.stsrc = 'A'

declare cs1 cursor for
select distinct nimhs, nlbyr, kdjur from #2
where nlbyr>0 and nimhs not in ( select nimhs from #delete )

select @nopmb = coalesce(max(nopmb), case @jenis
when '0001' then 'BP3'
when '0002' then 'SKS'
when '0003' then 'DP3' end + @priod + '000000')
from transaksi_rencana_pembayaran where priod=@priod and kdsem=@kdsem and kdpmb=@jenis

update transaksi_rencana_pembayaran
set stsrc='D', tglup=getdate(), usrid=@usrid
from transaksi_rencana_pembayaran a
join msmhs1 b on a.nimhs=b.nimhs
where a.priod=@priod and a.kdsem=@kdsem
and a.nimhs= case when @nimhspr='' then a.nimhs else @nimhspr end
and kdpmb=@jenis and a.status=@status and len(a.nimhs)=10
and stsby = '1'
and kdfak = case when @nimhspr<> '' then kdfak else case when @kdfak='AL' then kdfak else @kdfak end end
and kdjur = case when @nimhspr<> '' then kdjur else case when @kdjur='AL' then kdjur else @kdjur end end
-- cursor mulai dibuka
open cs1
fetch from cs1 into @nimhs, @nlbyr, @kdjur1
while @@fetch_status=0
begin

-- tagihan 1 disesuikan dengan pembayaran 1 dan langsung di anggap lunas.....
-- sesuai dengan kesepatakan dengan ibu siti tanggal 17-08-2003
-- untuk penarikan dana tanggal 18-09-2003
if @nlbyr > 0
begin -- jika ada perlu ditagih

select @nopmblm=nopmb from transaksi_rencana_pembayaran with (nolock)
where priod=@priod and kdsem=@kdsem and
nimhs=@nimhs and kdpmb=@jenis and status=@status --and stsrc = 'A'

if @nopmblm is not null
begin

if coalesce((select sum(nlbyr) from transaksi_pembayaran with (nolock)
where priod=@priod and kdsem=@kdsem and nimhs=@nimhs and nopmb=@nopmblm and kdpmb=@jenis
and stsrc='A' and nokwc is null
),0) < @nlbyr
begin
update transaksi_rencana_pembayaran
set stsrc='A', tglup=getdate(), usrid=@usrid, nlbyr=@nlbyr, tglpmb=@tglpmb, stsby='1' --nopmb=@nopmb,
where priod=@priod and kdsem=@kdsem and
nimhs=@nimhs and kdpmb=@jenis and status=@status and nopmb=@nopmblm --and stsrc = 'A'
end
else
begin
update transaksi_rencana_pembayaran
set stsrc='A', tglup=getdate(), usrid=@usrid, nlbyr=@nlbyr, tglpmb=@tglpmb, stsby='2' --nopmb=@nopmb,
where priod=@priod and kdsem=@kdsem and
nimhs=@nimhs and kdpmb=@jenis and status=@status and nopmb=@nopmblm --and stsrc = 'A'
end
end
else
begin
set @nopmb=left(@nopmb, 7) + right('00000' + ltrim(rtrim(convert(char(6), convert(int, right(@nopmb, 6))+1))), 6)

insert into transaksi_rencana_pembayaran
(Stsrc, Tglpr, usrid, Priod, Kdsem, Nimhs, Kdpmb, Nopmb, tglpmb, Nlbyr, status, stsby)
values
('A', getdate(), @usrid, @priod, @kdsem, @nimhs, @jenis, @nopmb, @tglpmb, @nlbyr, @status, '1')
end

set @nopmblm = null

end -- jika ada perlu ditagih
fetch next from cs1 into @nimhs, @nlbyr, @kdjur1
end

close cs1
deallocate cs1

drop table #delete
drop table #2

---
thats my full query. It will takes hours to finish.
when i try to execute it manually ( just delete
create proc dbo.spr_lakeu_create_Rencana_Bayar_BP3
@priod char(4), @kdsem char(1), @kdfak char(2), @kdjur char(2), @jenis char(4),
@tglpmb1 char(10), @tagih numeric, @usrid char(50), @status char(1), @nimhspr char(10)
as

and uncomment the declare variable and select )
the execution time just took 1-2 minutes..

regards,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources