Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Сравнение СУБД Новый топик    Ответить
 logged tables vs. nonlogged & delete vs. truncate  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034

тута давеча спор был, по поводу нужности и ненужности нетранзакционных
таблиц, а также нужности/ненужности сессионных временных таблиц (см.
Сайбес, спр. ASCRUS).
для проверки нужности (или ненужности) написал маленький скрипт,
упрощенный вид расчета сальдо по счетам, исходя из начальных остатков и
движения по ДТ/КТ.
delete vs. truncate проверяется легко, стоит только заменить :-)
а вот logged vs. nonlogged - увы, в MS SQL нельзя создать
нетранзакционную таблицу. обошелся тем, что для проверки запускал в
tempdb, там лог не сразу пишется на диск, так что хоть немного, но его
влияние уменьшилось.

скрипт по созданию
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
запускал на обычной базе, потом на tempdb.
получилось так
обычная база: TimeWithTruncate=2453, TimeWithDelete=3920
tempdb: TimeWithTruncate=1736, TimeWithDelete=1923

т.е. применяя сесионные временные таблицы и nonlogged таблицы я бы мог
получить выигрышь по скорости в 3920/1736=2.25 раза.

Вопросы по нужности этих фич будут?

--
-------------------------
There's no silver bullet!

Posted via ActualForum NNTP Server 1.3

11 окт 05, 16:18    [1958739]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
Ну тогда уже надо рассмотреть и табличные переменные, которые не являются транзакционными, данные хранятся в tempdb. Я думаю, несложно их будет их подставить в эти скрипты.

Какие будут результаты?
11 окт 05, 16:58    [1958955]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034

AAron wrote:
> Ну тогда уже надо рассмотреть и табличные переменные, которые не
> являются транзакционными, данные хранятся в tempdb. Я думаю, несложно их
> будет их подставить в эти скрипты.
>
> Какие будут результаты?
табличные переменные рассматривать не буду, даже в принципе :-)
поелику они всё-таки транзакционные, это раз.
а во вторых область видимости у них не-ага, меж процедур их не передашь.
а в третьих, набор индексов на них весьма убог, однако :-(


--
-------------------------
There's no silver bullet!

Posted via ActualForum NNTP Server 1.3

11 окт 05, 17:07    [1959007]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
Т.е. если есть что-то, что не укладывается в теорию, то значит этого нет? С индексами, конечно, засада. Можно рулить только в пределах PK.

Давайте все же рассмотрим. Таблички действительно нетранзакционные. Простой пример:

set nocount on
go
declare @t table (id int)
begin tran
insert into @t values (1)
select * from @t
rollback tran
select * from @t
go
set nocount off
go

id
-----------
1

id
-----------
1
11 окт 05, 17:53    [1959157]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034

AAron wrote:
> Т.е. если есть что-то, что не укладывается в теорию, то значит этого
> нет? С индексами, конечно, засада. Можно рулить только в пределах PK.
>
про неукладку в теорию - не понял, тупой сегодня... чего нет, кого нет?

> Давайте все же рассмотрим. Таблички действительно нетранзакционные.
> Простой пример:
>
> set nocount on
> go
> declare @t table (id int)
> begin tran
> insert into @t values (*1*)
> select * from @t
> rollback tran
> select * from @t
> go
> set nocount off
> go
>
> id
> -----------
> *1*
>
> id
> -----------
> *1*
>
давайте-давайте....
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
как видим, табличка пустая, т.е. атомарность на уровне оператора там
есть. как эту атомарность организовать без лога - ума не приложу.

скажем так, наверное, нетранзакционность - не очень правильное слово,
правильнее наверное нелоггируемая. потому как лог таки есть.

--
-------------------------
There's no silver bullet!

Posted via ActualForum NNTP Server 1.3

11 окт 05, 17:59    [1959180]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
hvlad
Guest
locky
атомарность на уровне оператора там
есть. как эту атомарность организовать без лога - ума не приложу
Не каждый лог должен называться transaction log ;)
В смысле - для табличных переменных может быть организован собственный лог, отбрасываемый после выполнения оператора. Вариантов реализации - уйма
11 окт 05, 22:56    [1959688]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034

hvlad wrote:
> locky
> атомарность на уровне оператора там
> есть. как эту атомарность организовать без лога - ума не приложу
>
> Не каждый лог должен называться transaction log ;)
> В смысле - для табличных переменных может быть организован собственный
> лог, отбрасываемый после выполнения оператора. Вариантов реализации - уйма
во-во!!! Но таки организован! а он ить бывает и не нужен...

--
-------------------------
There's no silver bullet!

Posted via ActualForum NNTP Server 1.3

11 окт 05, 23:19    [1959721]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
ChA
Member

Откуда: Москва
Сообщений: 11383
locky
как видим, табличка пустая, т.е. атомарность на уровне оператора там есть.
Разумеется есть, оператор либо выполняется полностью, либо нет. Выполнение его наполовину в лучшем случае вызовет нервный смех. Насколько помню, есть СУБД, где это не так, и тогда, например, оператор
INSERT INTO t SELECT * FROM t
может привести к зацикливанию.
К данному случаю транзакционный лог не имеет никакого отношения. Сначала формируется выборка для вставки, а затем она проверяется на соответствие ограничениям, в частности, NOT NULL. Вы можете это увидеть, если включите оценочный план, но при попытке посмотреть реальный, Вы не увидите запроса вставки, так как ограничения проверяются до реальной вставки данных, а ее не будет, до нее просто не дойдет, и все закончится на этапе проверки входных данных.
12 окт 05, 00:10    [1959795]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
hvlad
Guest
ChA
оператор либо выполняется полностью, либо нет. Выполнение его наполовину в лучшем случае вызовет нервный смех. Насколько помню, есть СУБД, где это не так, и тогда, например, оператор
INSERT INTO t SELECT * FROM t
может привести к зацикливанию
И где тут а) неатомарность и\или б) выполнение наполовину ?
12 окт 05, 00:24    [1959817]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
ChA
Member

Откуда: Москва
Сообщений: 11383
hvlad
И где тут а) неатомарность и\или б) выполнение наполовину ?
В данном примере неатомарность заключается в том, что SELECT "видит" данные, которые появляются в процессе вставки, т.е., оператор
INSERT INTO t SELECT * FROM t
выполняется не как одна команда, а как цикл более мелких итераций, состоящая из INSERT и SELECT. Мне казалось это очевидным, извините. Кстати, упомянутый locky пример, если бы такое было возможно в MS SQL, наверняка привел бы к тому, что в таблицу не вставилась только запись со значением NULL в поле, что, IMHO, было бы неверно.
В дальнейшем, не хотелось бы обсуждать верность поведения, которое зафиксировано, если правильно помню, в стандартах ANSI SQL. Смысл топика заключается несколько в ином. Кстати, даже не помню, в каком из серверов такой казус возможен, посему просьба не воспринимать его упоминание как "наезд" на эту СУБД или ее адептов.
12 окт 05, 00:56    [1959847]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
hvlad
Guest
ChA
hvlad
И где тут а) неатомарность и\или б) выполнение наполовину ?
В данном примере неатомарность заключается в том, что SELECT "видит" данные, которые появляются в процессе вставки
Да, вечно я про это забываю - я обычно думаю об атомарности тр-ции, а не оператора ;)

ChA
В дальнейшем, не хотелось бы обсуждать верность поведения, которое зафиксировано, если правильно помню, в стандартах ANSI SQL. Смысл топика заключается несколько в ином. Кстати, даже не помню, в каком из серверов такой казус возможен, посему просьба не воспринимать его упоминание как "наезд" на эту СУБД или ее адептов.
Ок, конечно
12 окт 05, 01:18    [1959853]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
интересный вопрос...
разве атомарность операции зависит от лога, точнее лога транзакций?
Собственн ChA уже ответил.
12 окт 05, 10:35    [1960490]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034

ChA wrote:
> Кстати, упомянутый locky пример, если бы такое было возможно в MS SQL,
> наверняка привел бы к тому, что в таблицу не вставилась только запись со
> значением NULL в поле, что, IMHO, было бы неверно.
ну, разве что имхо. как мне кажется, верно то, что хочет разработчик :-)
Конечно, в примере есть абсолютно неправильная ситуация, но он (пример)
нужен был для того чтобы продемонстрировать атомарность стэйтмента.
Просто хотелось бы мне знать, как много людей ощущают потребность
откатывать изменения во временных таблицах при проведении каких-либо
расчетов в случае возникновения ошибки. Как мне кажется, большая часть
просто рапортует ошибку и выходит. В отчетах, к примеру. да мало ли где
еще при расчетах. думаю, мало кого беспокоит содержимое # таблиц в
случае ошибки. А лог для них ведется, однако. противненько.

--
-------------------------
There's no silver bullet!

Posted via ActualForum NNTP Server 1.3

12 окт 05, 11:34    [1960807]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
для tempdb лог-транзакций вообще-то свой. tempdb как правило лежит на "скоростном" устройстве, если с ней идет интенсивная работа.
А лог можно засунуть и на ramdrive. В случае краша или остановки сервера, все равно tempdb умрет.



Но вообще, я просил просто выполнить те же действия для табличных переменных, чтобы можно было сравнить числа
А наличие нетранзакционных таблиц - было бы неплохо, конечно.
12 окт 05, 12:39    [1961301]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034

AAron wrote:
> для tempdb лог-транзакций вообще-то свой. tempdb как правило лежит на
> "скоростном" устройстве, если с ней идет интенсивная работа.
> А лог можно засунуть и на ramdrive. В случае краша или остановки
> сервера, все равно tempdb умрет.
>
на рам-драйв? типун вам на п... в общем, не надо так...
а свой лог - это да, пишется он куда реже, чем ля обычной базы..
доказанно занусси.
>
>
> Но вообще, я просил просто выполнить те же действия для табличных
> переменных, чтобы можно было сравнить числа
там по условиям задачи не покатят тэйбл-вары, но я померяю
> А наличие нетранзакционных таблиц - было бы неплохо, конечно.


--
-------------------------
There's no silver bullet!

Posted via ActualForum NNTP Server 1.3

12 окт 05, 12:48    [1961361]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
AAron
Member

Откуда: Москва
Сообщений: 4324
2locky - лог tempdb на рам-драйв а не основной базы.
Но вообще, эта экзотика. В промышленных решениях я так не делал. Хотя перенос всей БД (2ГБ) на RAMDRIVE давал в связке с сервером приложений прирост в скорости от 30% до 50% в зависимости от выполняемых действий.
12 окт 05, 13:16    [1961556]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034

AAron wrote:
> 2locky - лог tempdb на рам-драйв а не основной базы.
да я уж как-то и сам это понимаю :-)
> Но вообще, эта экзотика. В промышленных решениях я так не делал. Хотя
> перенос всей БД (2ГБ) на RAMDRIVE давал в связке с сервером приложений
> прирост в скорости от 30% до 50% в зависимости от выполняемых действий.
хм... интересно. но стрёмно.
--
-------------------------
There's no silver bullet!

Posted via ActualForum NNTP Server 1.3

12 окт 05, 13:22    [1961602]     Ответить | Цитировать Сообщить модератору
 Re: logged tables vs. nonlogged & delete vs. truncate  [new]
ChA
Member

Откуда: Москва
Сообщений: 11383
locky
ну, разве что имхо. как мне кажется, верно то, что хочет разработчик
А это, полагаю, Ваше IMHO. "Разрабочики" иногда хотят, мягко говоря, очень странного :)
locky
Конечно, в примере есть абсолютно неправильная ситуация, но он (пример) нужен был для того чтобы продемонстрировать атомарность стэйтмента.
Возможно неправ, но мне показалось, что Вы пытались доказать наличие транзакционного лога там, где он напрочь не нужен и отсутствует.
locky
Просто хотелось бы мне знать, как много людей ощущают потребность откатывать изменения во временных таблицах при проведении каких-либо расчетов в случае возникновения ошибки. Как мне кажется, большая часть просто рапортует ошибку и выходит. В отчетах, к примеру. да мало ли где еще при расчетах. думаю, мало кого беспокоит содержимое # таблиц в случае ошибки. А лог для них ведется, однако.
Согласен, что как "фича", возможно и не помешала бы, хотя не уверен, что в реальности Вы получили бы упомянутый Вами потрясающий прирост производительности. Доступ к tempdb, насколько понимаю, и так оптимизирован по самое не хочу. Вам ни о чем не говорит то, что сервер пользует ее для разного рода worktable ?

P.S. Вообще-то, не понимаю, что делает этот топик в форуме "Сравнение СУБД".
12 окт 05, 14:53    [1962120]     Ответить | Цитировать Сообщить модератору
Все форумы / Сравнение СУБД Ответить