Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
qwkefjhwef
можно так запилить как выше предлагал, хоть агрегирующие индексы, хоть лисапед тут по мне так один хрен
В общем да и работает хоть на 2000м.
Но никто не запрещает руками править агрегирующую таблу (client). А вот со вью - фиг.
8 авг 13, 18:10    [14683122]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
hertherthrt
Guest
Mnior,
руками править агрегирующую таблу (client)


это да.
8 авг 13, 18:17    [14683139]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Testor1
Member

Откуда:
Сообщений: 679
qwkefjhwef
можно так запилить как выше предлагал, хоть агрегирующие индексы, хоть лисапед тут по мне так один хрен,

CREATE TABLE [dbo].[Client](
	[id_client] [int] NOT NULL,
	[name] [nvarchar](100) NOT NULL,
	[total] [money] NOT NULL,
 CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED 
(
	[id_client] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Transaction](
	[id_transaction] [int] NOT NULL,
	[s] [money] NOT NULL,
	[id_client] [int] NOT NULL,
 CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED 
(
	[id_transaction] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Client] ADD  CONSTRAINT [DF_Client_total]  DEFAULT ((0)) FOR [total]
GO
ALTER TABLE [dbo].[Transaction]  WITH CHECK ADD  CONSTRAINT [FK_Transaction_Client] FOREIGN KEY([id_client])
REFERENCES [dbo].[Client] ([id_client])
GO
ALTER TABLE [dbo].[Transaction] CHECK CONSTRAINT [FK_Transaction_Client]
GO
ALTER TABLE [dbo].[Client]  WITH CHECK ADD  CONSTRAINT [CK_ClientTotalNonNegative] CHECK  (([Total]>=(0)))
GO
ALTER TABLE [dbo].[Client] CHECK CONSTRAINT [CK_ClientTotalNonNegative]
GO

create trigger [dbo].[ad_update_totals] 
   on  [dbo].[Transaction]
   after delete
as 
begin
	set nocount on;

	update tt2
	set tt2.total -= delta
	from
	(
		select id_client, sum(s) as delta
		from deleted t1
		group by t1.id_client
	) as tt1
		inner join
	client tt2 on tt1.id_client = tt2.id_client
end

GO
create trigger [dbo].[ai_update_totals] 
   on  [dbo].[Transaction]
   after insert
as 
begin
	set nocount on;

	update tt2
	set tt2.total += delta
	from
	(
		select id_client, sum(s) as delta
		from inserted t1
		group by t1.id_client
	) as tt1
		inner join
	client tt2 on tt1.id_client = tt2.id_client
end

create trigger [dbo].[au_update_totals] 
   on  [dbo].[Transaction]
   after update
as 
begin
	set nocount on;

	raiserror(N'ХРЮ!', 18, 1);
end
GO


У меня на первом этапе было такое решение, но я от него отказался. Не нравилось решение с триггерами и второй аггрегирующей таблицей. Если view показывает всегда актуальную величину, то при аггрегирующей таблице возможна ситуация, когда даны не актуальны. Например, при решении инцидента отключили триггер, а потом забыли его включить вовремя. Есть риск что за то время появяться новые данные, которые не попадут в результирующую таблицу.
9 авг 13, 10:02    [14685165]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Testor1
при решении инцидента отключили триггер
Моветон.
http://dilbertru.blogspot.com/2007/10/20071026.html
9 авг 13, 15:40    [14687752]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Testor1
Member

Откуда:
Сообщений: 679
Mnior
Testor1
при решении инцидента отключили триггер
Моветон.
http://dilbertru.blogspot.com/2007/10/20071026.html


перестань. у каждого свой путь развития.
9 авг 13, 15:45    [14687784]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
KRS544
Member

Откуда:
Сообщений: 497
Mnior
KRS544
Не агрегируйте данные, которые приводят к отрицательному результату - т.е. надо переделать View...
Что за бред? Зачем вообще превращать из базы помойку?


PS: Не надо таких советов, бомжатников надо выкидывать из города, пока это не стало эпидемией.
Хотя уже поздно ...


Т.е. Вы считаете, что каждый раз при вставке анализировать историческую таблицу, по сути таблицу логов операций, вместо написания нормального отчета по ней это правильно?
Если клиентов 10 - это еще будет работать, а если их сотни?
Будем ждать агрегатов в триггере?
9 авг 13, 15:46    [14687796]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
KRS544
Т.е. Вы считаете, что каждый раз при вставке анализировать историческую таблицу, по сути таблицу логов операций, вместо написания нормального отчета по ней это правильно?
Смешались кони люди.
Если уже не различают логи и операции это уже не лечится.
Лог на то и лог, что он в стороне бизнес логики. Даже его наличие необязательно.
А с операциями ДА - правильно делать то что требует логика.

Если вы имеете ввиду не учитывать операции c пометкой Deleted или ещё чего, то:
1. Надо было так и писать "добавь свойство бла-бла"
2. Это тут совершенно не причём. Задача как раз в предотвращении создания таких записей, а не в учёте их.
А то что будет ли это действие логироваться или нет, и как - полный оффтопик.

И последнее - как раз тут показано (двумя способами) как можно избавиться от "анализирования всей таблицы".
И вообще акцентирование внимания на таких "проблемах" говорит о слабости понимания программирования.
9 авг 13, 18:59    [14689094]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Testor1
Member

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

Полагаю, что сервер в первом случае по сути делает то же самое, что и второе решение, но быстрее поскольку использует внутренние "оптимизированные" механизмы хранения и обновления такого рода информации.
10 авг 13, 09:25    [14690685]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
KRS544
Member

Откуда:
Сообщений: 497
Mnior
KRS544
Т.е. Вы считаете, что каждый раз при вставке анализировать историческую таблицу, по сути таблицу логов операций, вместо написания нормального отчета по ней это правильно?
Смешались кони люди.
Если уже не различают логи и операции это уже не лечится.
Лог на то и лог, что он в стороне бизнес логики. Даже его наличие необязательно.
А с операциями ДА - правильно делать то что требует логика.

Если вы имеете ввиду не учитывать операции c пометкой Deleted или ещё чего, то:
1. Надо было так и писать "добавь свойство бла-бла"
2. Это тут совершенно не причём. Задача как раз в предотвращении создания таких записей, а не в учёте их.
А то что будет ли это действие логироваться или нет, и как - полный оффтопик.

И последнее - как раз тут показано (двумя способами) как можно избавиться от "анализирования всей таблицы".
И вообще акцентирование внимания на таких "проблемах" говорит о слабости понимания программирования.


Ну, хорошо, давайте разберемся.
В условии была историческая таблица, которую я назвал ПО СУТИ ЛОГОМ.
Вот определение - ЛОГ.
То, что вы называете бизнес логикой, завтра поменяется.
И надо будет посчитать как раз отрицательное что-то там.
Что человек ответит на это - "мы это по нашей супер бизнес логике не сохраняем в базу". Блеск.
То что нужно человеку, это построить отчет по исторической таблице, такой как нужно, а не писать триггеры.
Я понимаю что по условию задачи данное решение напрашивается. Но там еще было и "простое" решение, не так ли?
И акцентирование внимания на данном вопросе действительно не относится к программированию, оно относится к правильному построению системы.
10 авг 13, 10:49    [14690762]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
werfqwefwqef
Guest
Testor1
Полагаю, что сервер в первом случае по сути делает то же самое, что и второе решение, но быстрее поскольку использует внутренние "оптимизированные" механизмы хранения и обновления такого рода информации.


+ agg index

create table [dbo].[client]
(
	[id_client] [int] not null,
	[name] [nvarchar](100) not null
 constraint [pk_client] primary key clustered ([id_client] asc)
)
go
create table [dbo].[transaction](
	[id_transaction] [int] not null,
	[s] [money] not null,
	[id_client] [int] not null,
 constraint [pk_transaction] primary key clustered ([id_transaction] asc)
)
go
create view [dbo].[totalsview] with schemabinding
as
select
	id_client,
	sum(s) as total,
	count_big(*) as [count]
from
	dbo.[transaction]
group by
	id_client


go
create unique clustered index [idx_totals_view_id_client] on [dbo].[totalsview]([id_client] asc)
go
insert [dbo].[client] ([id_client], [name]) values (1, N'qwefqwef')
go
insert [dbo].[client] ([id_client], [name]) values (2, N'ergergerg')
go
alter table [dbo].[transaction]  with check add  constraint [fk_transaction_client] foreign key([id_client])
references [dbo].[client] ([id_client])
go
alter table [dbo].[transaction] check constraint [fk_transaction_client]
go
create trigger [dbo].[ad_check_totals]
   on  [dbo].[transaction] 
   after delete
as 
begin
	set nocount on;

	if exists
	(
		select 1
		from
			deleted t1
				inner join
			totalsview t2 on t1.id_client = t2.id_client
		where t2.total < 0
	)
	raiserror('хрю!', 18, 1);
end

go
create trigger [dbo].[ai_check_totals]
   on  [dbo].[transaction] 
   after insert
as 
begin
	set nocount on;

	if exists
	(
		select 1
		from
			inserted t1
				inner join
			totalsview t2 on t1.id_client = t2.id_client
		where t2.total < 0
	)
	raiserror('хрю!', 18, 1);
end
go
create trigger [dbo].[au_check_totals]
   on  [dbo].[transaction] 
   after update
as 
begin
	set nocount on;

	raiserror('хрю!', 18, 1);
end
go



+ agg index test

--truncate table [transaction];
--delete from [transaction]
--update client set total = 0;
--set statistics io on;
--set statistics time on;

set nocount on;

declare @cnt int = 100000;

while @cnt > 0
begin
	insert into [transaction](id_transaction, id_client, s)
		values(@cnt, 1, rand(checksum(newid())) * 100.0),
			  (100000 + @cnt, 2, rand(checksum(newid())) * 100.0)
	set @cnt -= 1
end;

--set statistics io off;
--set statistics time off;



+ agg table

create table [dbo].[client](
	[id_client] [int] not null,
	[name] [nvarchar](100) not null,
	[total] [money] not null,
 constraint [pk_client] primary key clustered ([id_client] asc)
)
go

create table [dbo].[transaction](
	[id_transaction] [int] not null,
	[s] [money] not null,
	[id_client] [int] not null,
 constraint [pk_transaction] primary key clustered ([id_transaction] asc)
)
go
insert [dbo].[client] ([id_client], [name], [total]) values (1, N'ооо "ручеек"', 0.0000)
go
insert [dbo].[client] ([id_client], [name], [total]) values (2, N'зао "кафэ шантан"', 0.0000)
go
alter table [dbo].[client] add  constraint [df_client_total]  default ((0)) for [total]
go
alter table [dbo].[transaction]  with check add  constraint [fk_transaction_client] foreign key([id_client])
references [dbo].[client] ([id_client])
go
alter table [dbo].[transaction] check constraint [fk_transaction_client]
go
alter table [dbo].[client]  with check add  constraint [ck_clienttotalnonnegative] check  (([total]>=(0)))
go
alter table [dbo].[client] check constraint [ck_clienttotalnonnegative]
go
create trigger [dbo].[ad_update_totals] 
   on  [dbo].[transaction]
   after delete
as 
begin
	set nocount on;

	update tt2
	set tt2.total -= delta
	from
	(
		select id_client, sum(s) as delta
		from deleted t1
		group by t1.id_client
	) as tt1
		inner join
	client tt2 on tt1.id_client = tt2.id_client
end

go
create trigger [dbo].[ai_update_totals] 
   on  [dbo].[transaction]
   after insert
as 
begin
	set nocount on;

	update tt2
	set tt2.total += delta
	from
	(
		select id_client, sum(s) as delta
		from inserted t1
		group by t1.id_client
	) as tt1
		inner join
	client tt2 on tt1.id_client = tt2.id_client
end

go
create trigger [dbo].[au_update_totals] 
   on [dbo].[transaction]
   after update
as 
begin
	set nocount on;

	raiserror(N'хрю!', 18, 1);
end

go



+ agg table test

--truncate table [transaction];
--update client set total = 0;

--set statistics io on;
--set statistics time on;

set nocount on;

declare @cnt int = 100000;

while @cnt > 0
begin
	insert into [transaction](id_transaction, id_client, s)
		values(@cnt, 1, rand(checksum(newid())) * 100.0),
			  (100000 + @cnt, 2, rand(checksum(newid())) * 100.0)
	set @cnt -= 1
end;

--set statistics io off;
--set statistics time off;




AGGREGATE TABLE - 36 сек
AGGREGATE INDEX - 58 сек
10 авг 13, 10:50    [14690763]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
werfwerfewrfewrg rgter
Guest
KRS544
Что человек ответит на это - "мы это по нашей супер бизнес логике не сохраняем в базу".


Насколько я понял у человека задача предотвратить действия, которыми клиент может загнать свой баланс в 0. Т. е. речь идет не только о не сохранении но и предотвращении таких действий... Например, подключение услуги стоит 150 р., на счету у клиента осталось 50. Он не может подключить эту услугу и надо делать ему отлуп...
10 авг 13, 10:54    [14690767]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
KRS544
Member

Откуда:
Сообщений: 497
werfwerfewrfewrg rgter, меня смутила формулировка - историческая.
10 авг 13, 11:09    [14690779]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
KRS544
Member

Откуда:
Сообщений: 497
Опять же, наверняка клиент захочет узнать когда и за что были начислены или сняты бонусы.
10 авг 13, 11:14    [14690782]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Testor1
Member

Откуда:
Сообщений: 679
werfqwefwqef
Testor1
Полагаю, что сервер в первом случае по сути делает то же самое, что и второе решение, но быстрее поскольку использует внутренние "оптимизированные" механизмы хранения и обновления такого рода информации.


+ agg index

create table [dbo].[client]
(
	[id_client] [int] not null,
	[name] [nvarchar](100) not null
 constraint [pk_client] primary key clustered ([id_client] asc)
)
go
create table [dbo].[transaction](
	[id_transaction] [int] not null,
	[s] [money] not null,
	[id_client] [int] not null,
 constraint [pk_transaction] primary key clustered ([id_transaction] asc)
)
go
create view [dbo].[totalsview] with schemabinding
as
select
	id_client,
	sum(s) as total,
	count_big(*) as [count]
from
	dbo.[transaction]
group by
	id_client


go
create unique clustered index [idx_totals_view_id_client] on [dbo].[totalsview]([id_client] asc)
go
insert [dbo].[client] ([id_client], [name]) values (1, N'qwefqwef')
go
insert [dbo].[client] ([id_client], [name]) values (2, N'ergergerg')
go
alter table [dbo].[transaction]  with check add  constraint [fk_transaction_client] foreign key([id_client])
references [dbo].[client] ([id_client])
go
alter table [dbo].[transaction] check constraint [fk_transaction_client]
go
create trigger [dbo].[ad_check_totals]
   on  [dbo].[transaction] 
   after delete
as 
begin
	set nocount on;

	if exists
	(
		select 1
		from
			deleted t1
				inner join
			totalsview t2 on t1.id_client = t2.id_client
		where t2.total < 0
	)
	raiserror('хрю!', 18, 1);
end

go
create trigger [dbo].[ai_check_totals]
   on  [dbo].[transaction] 
   after insert
as 
begin
	set nocount on;

	if exists
	(
		select 1
		from
			inserted t1
				inner join
			totalsview t2 on t1.id_client = t2.id_client
		where t2.total < 0
	)
	raiserror('хрю!', 18, 1);
end
go
create trigger [dbo].[au_check_totals]
   on  [dbo].[transaction] 
   after update
as 
begin
	set nocount on;

	raiserror('хрю!', 18, 1);
end
go



+ agg index test

--truncate table [transaction];
--delete from [transaction]
--update client set total = 0;
--set statistics io on;
--set statistics time on;

set nocount on;

declare @cnt int = 100000;

while @cnt > 0
begin
	insert into [transaction](id_transaction, id_client, s)
		values(@cnt, 1, rand(checksum(newid())) * 100.0),
			  (100000 + @cnt, 2, rand(checksum(newid())) * 100.0)
	set @cnt -= 1
end;

--set statistics io off;
--set statistics time off;



+ agg table

create table [dbo].[client](
	[id_client] [int] not null,
	[name] [nvarchar](100) not null,
	[total] [money] not null,
 constraint [pk_client] primary key clustered ([id_client] asc)
)
go

create table [dbo].[transaction](
	[id_transaction] [int] not null,
	[s] [money] not null,
	[id_client] [int] not null,
 constraint [pk_transaction] primary key clustered ([id_transaction] asc)
)
go
insert [dbo].[client] ([id_client], [name], [total]) values (1, N'ооо "ручеек"', 0.0000)
go
insert [dbo].[client] ([id_client], [name], [total]) values (2, N'зао "кафэ шантан"', 0.0000)
go
alter table [dbo].[client] add  constraint [df_client_total]  default ((0)) for [total]
go
alter table [dbo].[transaction]  with check add  constraint [fk_transaction_client] foreign key([id_client])
references [dbo].[client] ([id_client])
go
alter table [dbo].[transaction] check constraint [fk_transaction_client]
go
alter table [dbo].[client]  with check add  constraint [ck_clienttotalnonnegative] check  (([total]>=(0)))
go
alter table [dbo].[client] check constraint [ck_clienttotalnonnegative]
go
create trigger [dbo].[ad_update_totals] 
   on  [dbo].[transaction]
   after delete
as 
begin
	set nocount on;

	update tt2
	set tt2.total -= delta
	from
	(
		select id_client, sum(s) as delta
		from deleted t1
		group by t1.id_client
	) as tt1
		inner join
	client tt2 on tt1.id_client = tt2.id_client
end

go
create trigger [dbo].[ai_update_totals] 
   on  [dbo].[transaction]
   after insert
as 
begin
	set nocount on;

	update tt2
	set tt2.total += delta
	from
	(
		select id_client, sum(s) as delta
		from inserted t1
		group by t1.id_client
	) as tt1
		inner join
	client tt2 on tt1.id_client = tt2.id_client
end

go
create trigger [dbo].[au_update_totals] 
   on [dbo].[transaction]
   after update
as 
begin
	set nocount on;

	raiserror(N'хрю!', 18, 1);
end

go



+ agg table test

--truncate table [transaction];
--update client set total = 0;

--set statistics io on;
--set statistics time on;

set nocount on;

declare @cnt int = 100000;

while @cnt > 0
begin
	insert into [transaction](id_transaction, id_client, s)
		values(@cnt, 1, rand(checksum(newid())) * 100.0),
			  (100000 + @cnt, 2, rand(checksum(newid())) * 100.0)
	set @cnt -= 1
end;

--set statistics io off;
--set statistics time off;




AGGREGATE TABLE - 36 сек
AGGREGATE INDEX - 58 сек


Код неравнозначный

1) В первом случае делается проверка
2) Во втором случае делается только обновление без проверки

Что касается тестов, чтобы все было максимально по честному, нужно делать вставку одинаковых данных. То есть предварительно тестовые данные необходимо загрузить в #tmp таблицу.

Я сам проведу тесты, но попозже. SQL на другом компе.

P.S.
Почему вы создали отдельные триггеры на Insert, Update и Delete, а не использовали один (Insert, Update, Delete) или максимум два (Insert, Update) и Delete?
10 авг 13, 11:57    [14690829]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Testor1
Member

Откуда:
Сообщений: 679
Testor1
werfqwefwqef
пропущено...


+ agg index

create table [dbo].[client]
(
	[id_client] [int] not null,
	[name] [nvarchar](100) not null
 constraint [pk_client] primary key clustered ([id_client] asc)
)
go
create table [dbo].[transaction](
	[id_transaction] [int] not null,
	[s] [money] not null,
	[id_client] [int] not null,
 constraint [pk_transaction] primary key clustered ([id_transaction] asc)
)
go
create view [dbo].[totalsview] with schemabinding
as
select
	id_client,
	sum(s) as total,
	count_big(*) as [count]
from
	dbo.[transaction]
group by
	id_client


go
create unique clustered index [idx_totals_view_id_client] on [dbo].[totalsview]([id_client] asc)
go
insert [dbo].[client] ([id_client], [name]) values (1, N'qwefqwef')
go
insert [dbo].[client] ([id_client], [name]) values (2, N'ergergerg')
go
alter table [dbo].[transaction]  with check add  constraint [fk_transaction_client] foreign key([id_client])
references [dbo].[client] ([id_client])
go
alter table [dbo].[transaction] check constraint [fk_transaction_client]
go
create trigger [dbo].[ad_check_totals]
   on  [dbo].[transaction] 
   after delete
as 
begin
	set nocount on;

	if exists
	(
		select 1
		from
			deleted t1
				inner join
			totalsview t2 on t1.id_client = t2.id_client
		where t2.total < 0
	)
	raiserror('хрю!', 18, 1);
end

go
create trigger [dbo].[ai_check_totals]
   on  [dbo].[transaction] 
   after insert
as 
begin
	set nocount on;

	if exists
	(
		select 1
		from
			inserted t1
				inner join
			totalsview t2 on t1.id_client = t2.id_client
		where t2.total < 0
	)
	raiserror('хрю!', 18, 1);
end
go
create trigger [dbo].[au_check_totals]
   on  [dbo].[transaction] 
   after update
as 
begin
	set nocount on;

	raiserror('хрю!', 18, 1);
end
go



+ agg index test

--truncate table [transaction];
--delete from [transaction]
--update client set total = 0;
--set statistics io on;
--set statistics time on;

set nocount on;

declare @cnt int = 100000;

while @cnt > 0
begin
	insert into [transaction](id_transaction, id_client, s)
		values(@cnt, 1, rand(checksum(newid())) * 100.0),
			  (100000 + @cnt, 2, rand(checksum(newid())) * 100.0)
	set @cnt -= 1
end;

--set statistics io off;
--set statistics time off;



+ agg table

create table [dbo].[client](
	[id_client] [int] not null,
	[name] [nvarchar](100) not null,
	[total] [money] not null,
 constraint [pk_client] primary key clustered ([id_client] asc)
)
go

create table [dbo].[transaction](
	[id_transaction] [int] not null,
	[s] [money] not null,
	[id_client] [int] not null,
 constraint [pk_transaction] primary key clustered ([id_transaction] asc)
)
go
insert [dbo].[client] ([id_client], [name], [total]) values (1, N'ооо "ручеек"', 0.0000)
go
insert [dbo].[client] ([id_client], [name], [total]) values (2, N'зао "кафэ шантан"', 0.0000)
go
alter table [dbo].[client] add  constraint [df_client_total]  default ((0)) for [total]
go
alter table [dbo].[transaction]  with check add  constraint [fk_transaction_client] foreign key([id_client])
references [dbo].[client] ([id_client])
go
alter table [dbo].[transaction] check constraint [fk_transaction_client]
go
alter table [dbo].[client]  with check add  constraint [ck_clienttotalnonnegative] check  (([total]>=(0)))
go
alter table [dbo].[client] check constraint [ck_clienttotalnonnegative]
go
create trigger [dbo].[ad_update_totals] 
   on  [dbo].[transaction]
   after delete
as 
begin
	set nocount on;

	update tt2
	set tt2.total -= delta
	from
	(
		select id_client, sum(s) as delta
		from deleted t1
		group by t1.id_client
	) as tt1
		inner join
	client tt2 on tt1.id_client = tt2.id_client
end

go
create trigger [dbo].[ai_update_totals] 
   on  [dbo].[transaction]
   after insert
as 
begin
	set nocount on;

	update tt2
	set tt2.total += delta
	from
	(
		select id_client, sum(s) as delta
		from inserted t1
		group by t1.id_client
	) as tt1
		inner join
	client tt2 on tt1.id_client = tt2.id_client
end

go
create trigger [dbo].[au_update_totals] 
   on [dbo].[transaction]
   after update
as 
begin
	set nocount on;

	raiserror(N'хрю!', 18, 1);
end

go



+ agg table test

--truncate table [transaction];
--update client set total = 0;

--set statistics io on;
--set statistics time on;

set nocount on;

declare @cnt int = 100000;

while @cnt > 0
begin
	insert into [transaction](id_transaction, id_client, s)
		values(@cnt, 1, rand(checksum(newid())) * 100.0),
			  (100000 + @cnt, 2, rand(checksum(newid())) * 100.0)
	set @cnt -= 1
end;

--set statistics io off;
--set statistics time off;




AGGREGATE TABLE - 36 сек
AGGREGATE INDEX - 58 сек


Код неравнозначный

1) В первом случае делается проверка
2) Во втором случае делается только обновление без проверки

Что касается тестов, чтобы все было максимально по честному, нужно делать вставку одинаковых данных. То есть предварительно тестовые данные необходимо загрузить в #tmp таблицу.

Я сам проведу тесты, но попозже. SQL на другом компе.

P.S.
Почему вы создали отдельные триггеры на Insert, Update и Delete, а не использовали один (Insert, Update, Delete) или максимум два (Insert, Update) и Delete?



Сорри сразу не заметил
alter table [dbo].[client] with check add constraint [ck_clienttotalnonnegative] check (([total]>=(0)))
go
10 авг 13, 12:00    [14690833]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Testor1
Можете помочь разобраться чем отличается агрегированная вьюха с кластерным уникальным индексом, от решения с агрегированной табличкой и триггерами на обновление этой таблички? Вопрос в контексте логической реализации и быстродействия.

Полагаю, что сервер в первом случае по сути делает то же самое, что и второе решение, но быстрее поскольку использует внутренние "оптимизированные" механизмы хранения и обновления такого рода информации.
Достаточно посмотреть план выполнения для запроса на создание кластерного индекса вьюхи и на обновление данных
10 авг 13, 12:03    [14690838]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
ytiutiutuiy
Guest
+ на таком тесте

;with
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
l5(n) as (select 1 from l4 t1, l4 t2),
rt(n) as (select row_number() over (order by (select 0)) from l5 t1, l5 t2)

insert into [transaction](id_transaction, id_client, s)
	select top(1000000) n as id_transaction, 1 as id_client, rand(checksum(newid())) * 100.0 as s
	from rt



agg table - 12 c
agg index - 15 c
10 авг 13, 12:05    [14690840]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
werfqwefwqef
Guest
Testor1, проверка в обоих случаях была. сравнивал решения полностью.

теперь сделал так.

1) agg table
удалил все check'и с таблички client

2) agg index
удалил все триггеры

сравниваем только аггрегирование.

данные одинаковые, последовательность подачи данных тоже одинаковая. вот тест:

+

set nocount on;

declare @cnt int = 100000;

while @cnt > 0
begin
	insert into [transaction](id_transaction, id_client, s)
		values(@cnt, 1, 100.0),
			  (100000 + @cnt, 2, 150.0)
	set @cnt -= 1
end;



agg table - 32 c
agg index - 44 с

и планы вставки 1-й строки в первом и втором случае.

К сообщению приложен файл (plans.zip - 8Kb) cкачать
10 авг 13, 12:35    [14690873]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
цукецке34е34е
Guest
Testor1
P.S.
Почему вы создали отдельные триггеры на Insert, Update и Delete, а не использовали один (Insert, Update, Delete) или максимум два (Insert, Update) и Delete?


чтобы не делать лишних проверок, когда это не надо.
10 авг 13, 12:47    [14690888]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
fdhfgdjf
Guest
еще интересно было бы потестить оба решения в конкурентной среде
10 авг 13, 12:51    [14690891]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
KRS544
То, что вы называете бизнес логикой, завтра поменяется.
Закон обратного действия не имеет.
А поменяется логика, поменяется и решение.
KRS544
Что человек ответит на это - "мы это по нашей супер бизнес логике не сохраняем в базу". Блеск.
Опа, не сошлись остатки за прошлый месяц. Блеск.
Да, именно, операцию не создаём, логи - может быть.
KRS544
То что нужно человеку, это построить отчет по исторической таблице, такой как нужно, а не писать триггеры.
Цитирую старт-пост 14661852:
Testor1
Нужно запретить вставку/обновление/удаление записей в исторической таблице
Топик-стартер очередной раз не реагирует на твой посыл - следовательно он не подходит.
Хотя надо было чтобы он сразу ответил, и отмёл.
KRS544
... оно относится к правильному построению системы.
Это очень правильно, не спорю. И к сожалению мы не знаем реальной задачи.
автор
и планы вставки 1-й строки в первом и втором случае
У вас планы только с оптимизацией вставки одной строки, для 2х и более строк планы будут совершенно другими.
Побочных эффектов много. От резервирования памяти на индексы, до размера логов. А также локировки.
+ Тестовая среда
USE tempdb
GO
CREATE TABLE [dbo].[Client] (
	 [Client]	Int	IDENTITY
	 CONSTRAINT [PK_Client]		PRIMARY KEY
	,[Name]		NVarChar(100)	NOT NULL
	,[Total]	Money		NOT NULL
	 CONSTRAINT [DF_Client_Total]	DEFAULT (0)
	 CONSTRAINT [CK_Client_Total]	CHECK ([Total] >= 0)
)
CREATE TABLE [dbo].[Transaction] (
	 [Transaction]	Int	IDENTITY
	 CONSTRAINT [PK_Transaction]		PRIMARY KEY
 	,[Amount]	Money	NOT NULL
	,[Client]	Int	NOT NULL
	 CONSTRAINT [FK_Transaction_Client]	REFERENCES [dbo].[Client] ([Client])
)
GO	------------------------------------------------------------------------
CREATE TRIGGER [dbo].[trTransactionCheckDelete] ON [dbo].[Transaction]
AFTER DELETE AS BEGIN
	SET NOCOUNT ON;

	WITH [Delta] AS (
		SELECT	 Client
			,-Sum(Amount)	AS Delta
		FROM	Deleted
		GROUP BY Client
	)	UPDATE	C
		SET	Total	+= D.Delta
		FROM	[Delta]		D
		JOIN	dbo.Client	C ON C.Client = D.Client
END
GO
CREATE TRIGGER [dbo].[trTransactionCheckUpdate] ON [dbo].[Transaction]
AFTER UPDATE AS BEGIN
	SET NOCOUNT ON;

	WITH [DeltaAll] AS (
		SELECT	 Client
			,Sum(Amount)	AS Delta
		FROM	Inserted
		GROUP BY Client
	UNION ALL
		SELECT	 Client
			,-Sum(Amount)	AS Delta
		FROM	Deleted
		GROUP BY Client
	), [Delta] AS (
		SELECT	 Client
			,Sum(Delta)	AS Delta
		FROM	[DeltaAll]
		GROUP BY Client
	)	UPDATE	C
		SET	Total	+= D.Delta
		FROM	[Delta]		D
		JOIN	dbo.Client	C ON C.Client = D.Client
END
GO
CREATE TRIGGER [dbo].[trTransactionCheckInsert] ON [dbo].[Transaction]
AFTER INSERT AS BEGIN
	SET NOCOUNT ON;

	WITH [Delta] AS (
		SELECT	 Client
			,Sum(Amount)	AS Delta
		FROM	Inserted
		GROUP BY Client
	)	UPDATE	C
		SET	Total	+= D.Delta
		FROM	[Delta]		D
		JOIN	dbo.Client	C ON C.Client = D.Client
END
GO	------------------------------------------------------------------------
GO
CREATE VIEW [dbo].[VClientTransaction] WITH SCHEMABINDING AS
SELECT	 Client
	,Count_Big(*)	AS [Count]
	,Sum(Amount)	AS Total
FROM	dbo.[Transaction]
GROUP BY Client
GO
CREATE UNIQUE CLUSTERED INDEX [PK_VClientTransaction] ON [dbo].[VClientTransaction] (Client)
GO
ALTER TRIGGER [dbo].[trTransactionCheckDelete] ON [dbo].[Transaction]
AFTER DELETE AS BEGIN
	SET NOCOUNT ON;

	IF Exists (
		SELECT	*
		FROM	Deleted			D
		JOIN	dbo.VClientTransaction	T WITH(NoExpand) ON T.Client = D.Client
		WHERE	T.Total < 0
	)	RAISERROR('хрю!',18,1);
END
GO
ALTER TRIGGER [dbo].[trTransactionCheckUpdate] ON [dbo].[Transaction]
AFTER UPDATE AS BEGIN
	SET NOCOUNT ON;

	IF Exists (
		SELECT	*
		FROM	Inserted		I
		JOIN	dbo.VClientTransaction	T WITH(NoExpand) ON T.Client = I.Client
		WHERE	T.Total < 0
	)	RAISERROR('хрю!',18,1);
END
GO
ALTER TRIGGER [dbo].[trTransactionCheckInsert] ON [dbo].[Transaction]
AFTER INSERT AS BEGIN
	SET NOCOUNT ON;

	IF Exists (
		SELECT	*
		FROM	Inserted		I
		JOIN	dbo.VClientTransaction	T WITH(NoExpand) ON T.Client = I.Client
		WHERE	T.Total < 0
	)	RAISERROR('хрю!',18,1);
END
GO	------------------------------------------------------------------------
DROP TRIGGER dbo.trTransactionCheckInsert, dbo.trTransactionCheckUpdate, dbo.trTransactionCheckDelete;
ALTER TABLE dbo.Client DROP CONSTRAINT [CK_Client_Total];
GO	------------------------------------------------------------------------
DROP VIEW dbo.VClientTransaction;
DROP TABLE dbo.[Transaction], dbo.Client;
+ Тесты
SET NOCOUNT ON;
INSERT	dbo.Client	(Name)
SELECT	Top(1000)	 number
FROM	master.dbo.spt_values
WHERE	type = 'P'

INSERT	dbo.[Transaction]	(Client,Amount)
SELECT				 Client,0
FROM	dbo.Client
GO
DELETE dbo.[Transaction] WHERE Amount != 0;
DECLARE	 @Count Int = 0
	,@From	DateTime	= GetDate()
WHILE (@Count < 100000) BEGIN SET @Count += 1;
	INSERT	dbo.[Transaction]	(Client,Amount)
	SELECT				 Client,1
	FROM	dbo.Client
	WHERE	Client IN (@Count % 1000 + 1),@Count % 1000 + 2)
END
SELECT DateDiff(MilliSecond,@From,GetDate())
-- 1:
-- 18'426, 9`016, 8`396
-- 11`836,11`833,11`706
-- 2:
-- 17`103,17`626,17`090
-- 24`930,24`770,24`483
GO
SELECT * FROM dbo.Client
SELECT * FROM dbo.VClientTransaction
-- UPDATE dbo.Client SET Total = 0
В зависимости от случая (вплоть до вставки по всем клиентам) планы жутко прыгают.
И отлично показывают много багов оптимизации для агрегаток (лишние телодвижения). Которые чередуются неописуемым способом.
Testor1
Можете помочь разобраться чем отличается агрегированная вьюха с кластерным уникальным индексом, от решения с агрегированной табличкой и триггерами на обновление этой таблички?
Вот результаты другого анализа: 12925191 По локировкам.
Где видно, что декларативная форма агрегатки казалось бы могла избавить от проблемы дедлоков в данной задаче. Ан нет - те же проблемы.
автор
Полагаю, что сервер в первом случае по сути делает то же самое, что и второе решение, но быстрее поскольку использует внутренние "оптимизированные" механизмы хранения и обновления такого рода информации.
1. Как вы видите, новая волна девелоперов скуля недотягивают до старпёров.
Не смотря на то что триггера надо ещё и запустить, другие команды.
2. Это второстепенная вещь. Ибо основная логика решения идентична. Планы почти одинаковые.
А вот побочные эффекты могут быть существеннее.
Как я уже писал выше, таблицу можно поменять напрямую, и это может быть как недостатком, так и преимуществом (быстро решить проблему).
А с другой стороны, поменялась логика - придётся пересоздавать агрегатку с нуля. Если объём будет огромным - придётся ухищряться, ибо в лоб всё надолго залипнет.
С таблицей надо ухо в остро, чуть что - данные превращаются в мусор, и собирай их сначала. Но при смене логики можно умудрится воспользоваться текущими данными.

А вообще дело не в реализации, а в подходе. Выйдет 20NN версия с новыми оптимизациями, и агрегатки станут быстрее и решат заодно проблемы дедлоков. Дело именно в потенциале декларативного программирования.
А тягомотина оптимизации продукта в его проприетарности. Сообщество хочет одного, а M$ делает совершенно другое.
Вообще MS SQL поразителен тем что при своей проприетарности он декларативнее свободных аналогов. Пока ещё ... кажется
10 авг 13, 16:32    [14691209]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
KRS544
Member

Откуда:
Сообщений: 497
Ок, тогда вопрос к автору
1. Почему бы не создать таблицу, где будет ID клиента (уникальный кластерный индекс) и поле с бонусом.
2. при изменении бонуса эту таблицу апдейтить (одну запись) по кластерному индексу, что несравнимо быстрее и проще, чем агрегаты.
3. написать триггер, а лучше хранимку (мне как то больше нравится, имхо) на внесение изменений в эту таблицу, и проверять текущее значение бонуса.
11 авг 13, 20:42    [14693936]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
KRS544
Ок, тогда вопрос к автору
1. Почему бы не создать ...
И снова здравствуйте.
Четвёртый же ответ топика.
Остальная его половина это обсуждение преимуществ/недостатков.
KRS544
... что несравнимо быстрее и проще, чем агрегаты
Почитайте чтоле темку, может мнение поменяется.
Вот у ТС адекватное восприятие, в отличии от вашего "несравнимо".
11 авг 13, 22:51    [14694176]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
KRS544
Member

Откуда:
Сообщений: 497
Mnior, вы мне тоже очень понравились...
Нет никакого желания больше с Вами общаться.

Я задавал вопрос автору.
Вот кстати ответ, цитата "Будет работать быстрее на селект, но не надежней" - ???
12 авг 13, 00:04    [14694339]     Ответить | Цитировать Сообщить модератору
 Re: Проверка для view. Нужно правильное и простое решение.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
KRS544
Я задавал вопрос автору.
Ну не проблема, ответит, если найдёт время и желание.
Просто я не понимаю смысл вопроса. И зачем растягивать тему намёками?
KRS544
Вот кстати ответ, цитата "Будет работать быстрее на селект, но не надежней" - ???
Ну да. Он так и ответил:
Testor1
У меня на первом этапе было такое решение, но я от него отказался. Не нравилось решение с триггерами и второй аггрегирующей таблицей. Если view показывает всегда актуальную величину, то при аггрегирующей таблице возможна ситуация, когда даны не актуальны. Например, при решении инцидента отключили триггер, а потом забыли его включить вовремя. Есть риск что за то время появяться новые данные, которые не попадут в результирующую таблицу.
Побочные эффекты решения бывают важнее самого решения, зависит от среды.
KRS544
Mnior, вы мне тоже очень понравились...
Мне кажется что вы что-то путаете/надумываете. Ярлыки вешать никому не надо.
Ну совершает человек ошибки, ну и ладно. Всё нормально.

Мне нравится что вы задаёте вопрос об общей рациональности подхода, и импонирует у автора тематика о применимости/выборе решения для конкретной производственной атмосферы технологий управления.
Далеко не каждый умеет/может/хочет задавать эти вопросы.
В эпоху информационной перегрузки, особенно.
12 авг 13, 04:18    [14694575]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить