if object_id('DocDebet','U') is not null drop table DocDebet
go
if object_id('DocCredit','U') is not null drop table DocCredit
go
if object_id('Rem','U') is not null drop table Rem
go
if object_id('CalcSaldo_t','U') is not null drop table CalcSaldo_t
go
if object_id('CalcSaldo2','U') is not null drop table CalcSaldo2
go
if object_id('CalcSaldo','V') is not null drop view CalcSaldo
go
if object_id('acfi','U') is not null drop table acfi
go
if object_id('Calc','p') is not null drop procedure calc
go
if object_id('Calc2','p') is not null drop procedure calc2
go


create table acfi(id int primary key clustered,code varchar( 512 ))
go
create table DocDebet(id int identity( 1 , 1 ) primary key nonclustered,acc 
int,Date datetime not null,summ money not null )
go
create clustered index cl on docdebet(date)
go
create index byacc on docdebet(acc)
go
create table DocCredit(id int identity( 1 , 1 ) primary key nonclustered,acc 
int,Date datetime not null,summ money not null )
go
create clustered index cl on DocCredit(date)
go
create index byacc on DocCredit(acc)
go
create table Rem(Acc int not null,date datetime not null,saldo money not 
null)
go
create clustered index BydateAcc on Rem(Date,acc)
go
create table CalcSaldo_t(SPID smallint not null default @@SPID,acc int 
not null,SaldoIn money,Sd money,Sc money,SaldoOut money)
go
create clustered index cl on CalcSaldo_t(SPID,Acc)
go
create table CalcSaldo2(acc int not null,SaldoIn money,Sd money,Sc 
money,SaldoOut money)
go
create clustered index cl on CalcSaldo2(Acc)
go
create view CalcSaldo as select acc,Saldoin,Sd,Sc,SaldoOut from 
CalcSaldo_t where spid = @@SPID
go
set nocount on
declare @i int
set @i =  0 
while @i <  10000  begin
  insert into acfi(id,code) values(@i,@i)
  set @i = @i +  1 
end
go
declare @date datetime
set @date = '20040101'
while @date < '20050101' begin
insert into DocDebet(acc,date,summ) select id,@date, 10  from acfi
insert into DocCredit(acc,date,summ) select id,@date, 5  from acfi
insert into Rem(acc,date,Saldo) select id,@date, 2  from acfi
set @date = dateadd(month, 1 ,@date)
end
go

create procedure Calc @si int,@ei int,@df datetime,@dt datetime
as begin
set nocount on
delete CalcSaldo
insert into CalcSaldo(acc) select id from acfi where id >= @si and id <= 
@ei
update	C
set	SaldoIn= Saldo,SaldoOut=Saldo
from	CalcSaldo C join Rem R on C.Acc = R.Acc
where	R.date = @df

update	C
set	Sd = S.Sd
	,SaldoOut = SaldoOut+S.Sd
from	CalcSaldo C join (select Acc,Sd=Sum(Summ) from DocDebet where date 
 >= @df and date < @dt and acc >= @si and acc <= @ei group by acc) S on 
C.Acc = S.Acc

update	C
set	Sc = S.Sc
	,SaldoOut = SaldoOut-S.Sc
from	CalcSaldo C join (select Acc,Sc=Sum(Summ) from DocCredit where date 
 >= @df and date < @dt and acc >= @si and acc <= @ei group by acc) S on 
C.Acc = S.Acc
return  0 
end
go

create procedure Calc2 @si int,@ei int,@df datetime,@dt datetime
as begin
set nocount on
truncate table calcSaldo2
insert into CalcSaldo2(acc) select id from acfi where id >= @si and id 
<= @ei
update	C
set	SaldoIn= Saldo,SaldoOut=Saldo
from	CalcSaldo2 C join Rem R on C.Acc = R.Acc
where	R.date = @df

update	C
set	Sd = S.Sd
	,SaldoOut = SaldoOut+S.Sd
from	CalcSaldo2 C join (select Acc,Sd=Sum(Summ) from DocDebet where date 
 >= @df and date < @dt and acc >= @si and acc <= @ei group by acc) S on 
C.Acc = S.Acc

update	C
set	Sc = S.Sc
	,SaldoOut = SaldoOut-S.Sc
from	CalcSaldo2 C join (select Acc,Sc=Sum(Summ) from DocCredit where 
date >= @df and date < @dt and acc >= @si and acc <= @ei group by acc) S 
on C.Acc = S.Acc
return  0 
end
go

dbcc dropcleanbuffers
dbcc freeproccache
go

set nocount on
declare @i int,@d datetime,@si int,@ei int
set @i =  0 
set @d = GetDate()
while @i <  11  begin
set @si = @i* 1000 
set @ei = @si+ 1000 
exec Calc2 @si,@ei,'20040201','20040301'
set @i = @i +  1 
end
select datediff(ms,@d,GetDate()) as TimeWithTruncate

go

dbcc dropcleanbuffers
dbcc freeproccache
go

set nocount on
declare @i int,@d datetime,@si int,@ei int
set @i =  0 
set @d = GetDate()
while @i <  11  begin
set @si = @i* 1000 
set @ei = @si+ 1000 
exec Calc @si,@ei,'20040201','20040301'
set @i = @i +  1 
end
select datediff(ms,@d,GetDate()) as TimeWithDelete
go

declare @tab table(id int not null)
insert into @tab select top  10  id from sysobjects union all select null 
order by  1  desc option(maxdop  1 )
select * from @tab