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

Откуда:
Сообщений: 23
Всем привет и с Новым годом!

Для начала описание ситуации:
Есть 2 сервера, и приложение, условно трехзвенка (клиент - сервер - бд)
Сервер приложения и база MS SQL 2008 R2 стоят на разных серверах, общение между собой через отдельные сетевые
гигабитные (вроде) карты. Жесткие диски обычные ~7000об/мин. Сервер приложения принимает клиентов, работает с
ними и дергает БД. Работа сессионная, логинится клиент, работает, выходит. Вся логика работы с бд обернута в хранимые процедуры.

Примерное структура БД
В вопросах участвуют несколько таблиц, поэтому примерная структура приводится только для них:

clients:
id int not null,
login nvarchar(50) not null,
balance int not null, -- точка интереса

payments:
id int not null,
td_stamp datetime not null, -- getdate()
cashier_id int null, -- идентификатор кассира, может быть null
client_id int not null, -- идентификатор клиента
value int not null, -- non zero constraint -- сумма операции
comment nvarchar(500) null --


Итак вопросы:
Используя вот таккую хранимую процедуру мы производим платеж, эта процедура вызывается очень часто (примерно около 8к клиентов,
от 0 до 3х раз для каждого в секунду), и должна выполняться максимально быстро, поэтому см. ниже код (точка интереса) смущает меня.

-- некоторые вещи мне не понятны, человек работавший с ней уволился, а у меня уровень примерно продвинутого пользователя в БД
-- так что привожу код почти как есть:
CREATE PROCEDURE make_payment(
  @client_id int,
  @value int 
) as begin
 	SET NOCOUNT ON
    SET XACT_ABORT ON  
    BEGIN TRY     
		BEGIN TRANSACTION

		-- тут проверки на существование и валидность данных, если они важны, могу привести и эти примеры кода

		-- точка интереса
		update clients
		set balance = balance + @value -- ведь это потребует блокировок постранично?
		where id=@client_id

		insert into playments(cashier_id, client_id, value)
		values(null, @client_id, @value)




		COMMIT TRANSACTION
    END TRY
    	BEGIN CATCH            
        	IF (XACT_STATE()) = -1 BEGIN
                	ROLLBACK TRANSACTION
			DECLARE @ErrorMessage nvarchar(MAX)
			SELECT @ErrorMessage = ERROR_MESSAGE()           
			RAISERROR (@ErrorMessage,16,1)
		END;    
		IF (XACT_STATE()) = 1
		COMMIT TRANSACTION 
		RETURN 0   
	END CATCH

end

-- Мне предложили изменить этот код, который написан выше, примерно так:
-- Изменение в структуре бд: сначала самое поле balance становится вычисляемым (вызывается функция getBalance, внутри
-- нее просто сумма, что-то подобное - select sum(value) from payments where client=@id)
-- т.к. значение этого поля важно лишь изредка (ну т.е. обращаются к нему относительно "редко") то наверное
-- это вполне терпимо, а код меняется просто, исчезает update clients остается один легкий инсерт и все вроде хорошо
-- проблемы начинаются только тогда когда самих "платежей" (записей в таблице payments) становится ОЧЕНЬ много, (миллионы и т.п.)

-- поэтому был предложен способ:
-- когда клиент выходит, происходит "архивация" платежей, все его платежи переносятся из (payments) в отдельную таблицу (archive_payments),
-- в текущей (payments) остается только 1 платеж - сумма на момент выхода, таким образом вся активная работа происходит
-- в таблице payments, баланс выводится быстро, платежи проходят быстро. А отчеты смотрим по архиву

И вот вопрос:
Насколько целесообразно менять текущую логику работы с балансом\платежами? Может есть какие-то по-строчные блокировки таблиц для транзакции, чтобы оставить обычный update? Или все же придется городить огород с архивами, архивацией и прочими вычисляемыми полями?
5 янв 14, 12:41    [15378707]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
aleks2
Guest
JustOxlamon
-- Мне предложили изменить этот код, который написан выше, примерно так:
-- 1. Изменение в структуре бд: сначала самое поле balance становится вычисляемым (вызывается функция getBalance, внутри
-- нее просто сумма, что-то подобное - select sum(value) from payments where client=@id)
-- 2. т.к. значение этого поля важно лишь изредка (ну т.е. обращаются к нему относительно "редко") то наверное
-- это вполне терпимо, а код меняется просто, исчезает update clients остается один легкий инсерт и все вроде хорошо
-- 3. проблемы начинаются только тогда когда самих "платежей" (записей в таблице payments) становится ОЧЕНЬ много, (миллионы и т.п.)

-- 4. поэтому был предложен способ:
-- когда клиент выходит, происходит "архивация" платежей, все его платежи переносятся из (payments) в отдельную таблицу (archive_payments),
-- в текущей (payments) остается только 1 платеж - сумма на момент выхода, таким образом вся активная работа происходит
-- в таблице payments, баланс выводится быстро, платежи проходят быстро. А отчеты смотрим по архиву

И вот вопрос:
5. Насколько целесообразно менять текущую логику работы с балансом\платежами? Может есть какие-то по-строчные блокировки таблиц для транзакции, чтобы оставить обычный update? Или все же придется городить огород с архивами, архивацией и прочими вычисляемыми полями?


1. Бред. Изучите Indexed View.
2. Забавная иллюзия.
3. Тоже бред. Изучите Indexed View.
4. Феерический бред.
5. Вас то чего не устраивает?
5 янв 14, 13:09    [15378749]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 888
JustOxlamon,
у вас таблица без индексов и ключей первичных?

в таблице clients может быть много @client_id одного клиента?
5 янв 14, 13:10    [15378751]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
sdet
Member

Откуда:
Сообщений: 463
aleks2
JustOxlamon
-- Мне предложили изменить этот код, который написан выше, примерно так:
-- 1. Изменение в структуре бд: сначала самое поле balance становится вычисляемым (вызывается функция getBalance, внутри
-- нее просто сумма, что-то подобное - select sum(value) from payments where client=@id)
-- 2. т.к. значение этого поля важно лишь изредка (ну т.е. обращаются к нему относительно "редко") то наверное
-- это вполне терпимо, а код меняется просто, исчезает update clients остается один легкий инсерт и все вроде хорошо
-- 3. проблемы начинаются только тогда когда самих "платежей" (записей в таблице payments) становится ОЧЕНЬ много, (миллионы и т.п.)

-- 4. поэтому был предложен способ:
-- когда клиент выходит, происходит "архивация" платежей, все его платежи переносятся из (payments) в отдельную таблицу (archive_payments),
-- в текущей (payments) остается только 1 платеж - сумма на момент выхода, таким образом вся активная работа происходит
-- в таблице payments, баланс выводится быстро, платежи проходят быстро. А отчеты смотрим по архиву

И вот вопрос:
5. Насколько целесообразно менять текущую логику работы с балансом\платежами? Может есть какие-то по-строчные блокировки таблиц для транзакции, чтобы оставить обычный update? Или все же придется городить огород с архивами, архивацией и прочими вычисляемыми полями?


1. Бред. Изучите Indexed View.
2. Забавная иллюзия.
3. Тоже бред. Изучите Indexed View.
4. Феерический бред.
5. Вас то чего не устраивает?

Indexed View к данному случаю мало применимы
5 янв 14, 13:18    [15378760]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
JustOxlamon
Member

Откуда:
Сообщений: 23
Slava_Nik
JustOxlamon,
у вас таблица без индексов и ключей первичных?

в таблице clients может быть много @client_id одного клиента?


В clients первичный по id, и еще пара индексов по логину и кое-какой еще поисковой информации.
пользователей в этой таблице порядка 30к-50к, сейчас пока 10к добавил для тестирования
5 янв 14, 13:18    [15378762]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
JustOxlamon
Member

Откуда:
Сообщений: 23
(не обращаясь ни к кому лично)
Просто хотелось бы более или менее аргументированного ответа. Возможно с отсылкой в документацию. Односложные ответы пока не принимаются, в силу отсутствия аргументов.
5 янв 14, 13:20    [15378763]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
aleks2
Guest
sdet
aleks2
пропущено...


1. Бред. Изучите Indexed View.
2. Забавная иллюзия.
3. Тоже бред. Изучите Indexed View.
4. Феерический бред.
5. Вас то чего не устраивает?

Indexed View к данному случаю мало применимы


Что такое?
Прогулял занятия и не ведаешь чо несешь?

ЗЫ. Indexed View, аккурат, весьма применимо к данной проблеме.
5 янв 14, 13:20    [15378765]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
JustOxlamon
-- ведь это потребует блокировок постранично?
Зависит от индексов. Этот код делает блокировку строки, но при наличии индексов будет заблокировано больше.
JustOxlamon
-- поэтому был предложен способ:
-- когда клиент выходит, происходит "архивация" платежей, все его платежи переносятся из (payments) в отдельную таблицу (archive_payments),
-- в текущей (payments) остается только 1 платеж - сумма на момент выхода, таким образом вся активная работа происходит
-- в таблице payments, баланс выводится быстро, платежи проходят быстро. А отчеты смотрим по архиву
Естественно, если у вас делается много-много изменений, а правильный "баланс" нужно показывать, только когда клиент "вышел", то это вполне нормальный вариант.

Так же можно сделать и обновление таблицы clients, когда клиент "вышел".
JustOxlamon
сначала самое поле balance становится вычисляемым (вызывается функция getBalance, внутри
-- нее просто сумма, что-то подобное - select sum(value) from payments where client=@id)
Не надо вычисляемых полей с функциями, будет только хуже.
JustOxlamon
Насколько целесообразно менять текущую логику работы с балансом\платежами? Может есть какие-то по-строчные блокировки таблиц для транзакции, чтобы оставить обычный update? Или все же придется городить огород с архивами, архивацией и прочими вычисляемыми полями?
Если обеспечить короткие транзакции и высокую скорость одной операции, то не нужно. Несколько тысяч простых обновлений записи в секунду по ключу - это не так уж много...
aleks2
Изучите Indexed View.
Думаю, обновление Indexed View займёт как минимум такое же время, как и обновления поля в таблице. Не вижу, откуда тут взяться уменьшению блокировок и IO.
5 янв 14, 13:21    [15378767]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
aleks2
Guest
JustOxlamon
Односложные ответы пока не принимаются, в силу отсутствия аргументов.


Щас, разжуютЬ и в рот тредстартеру выплюнут.
5 янв 14, 13:22    [15378769]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
aleks2
Guest
alexeyvg
aleks2
Изучите Indexed View.
Думаю, обновление Indexed View займёт как минимум такое же время, как и обновления поля в таблице. Не вижу, откуда тут взяться уменьшению блокировок и IO.


Наверное, правильно думаешь. Тока
1. Будет все ж правильный кластерный индекс.
2. Обновлять будет ядро СУБД, а не кривой код тредстартера.
3. И быстрее, ты все одно,не сделаешь. Баланс то считать надо.
4. А "идея" тредстартера породит ишо больше этих блокировок.
5 янв 14, 13:27    [15378774]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
JustOxlamon
Может есть какие-то по-строчные блокировки таблиц для транзакции
А у вас в теле процедуры нет каких либо других обновлений clients?

Укажите на всякий хинт ROWLOCK в этом апдэйте, может поможет.

Думаю, процедура оптимальна, быстрее не сделать. Разве что чтения баланса клиентов черезвычайно редкие, и можно заменить получение баланса на простой запрос с агрегированием из платежей (или на неиндексированную вьюху).

Но это только если очень редкий запрос баланса!
5 янв 14, 13:29    [15378775]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
aleks2
1. Будет все ж правильный кластерный индекс.
2. Обновлять будет ядро СУБД, а не кривой код тредстартера.
3. И быстрее, ты все одно,не сделаешь. Баланс то считать надо.
Кластерный индекс (ПК) у клиентов - куда уж оптимальнее для получения записи/обновления?

Ядро субд будет обновлять баланс пересчётом по всем платежам.

Естественно, код ТС это делает быстрее, именно потому, что не надо считать баланс (всего лишь прибавляет переменную вместо пересчёта).
5 янв 14, 13:33    [15378781]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
aleks2
Guest
alexeyvg
Но это только если очень редкий запрос баланса!

Наверное он от безделья баланс считает.
5 янв 14, 13:33    [15378782]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
aleks2
Guest
alexeyvg
Ядро субд будет обновлять баланс пересчётом по всем платежам.

Иди, убейся ап стенку лучше. И не позорься.
5 янв 14, 13:35    [15378783]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
JustOxlamon
Member

Откуда:
Сообщений: 23
alexeyvg,
В общем текущая реализация как раз нормальная, только грамотно подойти к вопросу об индексах надо. А если я, например, разделю таблицу clients на 2, и вторую сделаю примерно такой:
balances:
id int not null,
balance int not null,

тут будет только один индекс - id - primary key
и сделаю связь один-к-одному с таблицей clients, тогда update же будет ощутимо легче?

А для отображения пользователей-с-балансом, как раз можно использовать ind. view.

aleks2,
С вами все ясно, спасибо за внимание. Все же, если бы у вас был хотя-бы пример (не код), а словесно описано, как применить к текущей задаче ind. views, было бы проще.
5 янв 14, 13:36    [15378785]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
sdet
Member

Откуда:
Сообщений: 463
aleks2
sdet
пропущено...

Indexed View к данному случаю мало применимы


Что такое?
Прогулял занятия и не ведаешь чо несешь?

ЗЫ. Indexed View, аккурат, весьма применимо к данной проблеме.

Да ты проблему похоже то не понял.
В общем как сказал ТС
aleks2,
С вами все ясно, спасибо за внимание.
5 янв 14, 13:41    [15378793]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
aleks2
Guest
JustOxlamon
aleks2,
С вами все ясно, спасибо за внимание. Все же, если бы у вас был хотя-бы пример (не код), а словесно описано, как применить к текущей задаче ind. views, было бы проще.

Исключительно по доброте душевной и специально для недоучек

create view dbo.Balances
with schemabinding
as
select client_id, SUM(value) Balance, COUNT_BIG(*) as PaymentCount from dbo.payments group by client_id ;
go

create UNIQUE CLUSTERED INDEX IDX_Balances on dbo.Balances (client_id);
go

CREATE PROCEDURE make_payment(
  @client_id int,
  @value int 
) as begin
 	SET NOCOUNT ON
    SET XACT_ABORT ON  
    BEGIN TRY     
		BEGIN TRANSACTION

		-- тут проверки на существование и валидность данных, если они важны, могу привести и эти примеры кода

                          -- ничо считать НЕ НАДА.
		insert into playments(cashier_id, client_id, value)
		values(null, @client_id, @value)




		COMMIT TRANSACTION
    END TRY
    	BEGIN CATCH            
        	IF (XACT_STATE()) = -1 BEGIN
                	ROLLBACK TRANSACTION
			DECLARE @ErrorMessage nvarchar(MAX)
			SELECT @ErrorMessage = ERROR_MESSAGE()           
			RAISERROR (@ErrorMessage,16,1)
		END;    
		IF (XACT_STATE()) = 1
		COMMIT TRANSACTION 
		RETURN 0   
	END CATCH

end
GO
-- тока при работе с dbo.Balances не забываем указывать WITH(NOEXPEND). Рази тока у вас Enterprise
-- select * from dbo.Balances WITH(NOEXPEND) where client_id=...
5 янв 14, 13:45    [15378798]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
JustOxlamon
Member

Откуда:
Сообщений: 23
aleks2,
Вот видите, как приятно быть добрым. С новым годом )

Таким образом сам скул внутри себя как-то оптимизирует данные, чтобы не проводить полный пересчет, если не надо. Очень интересно. И да, у нас действительно Enterprise SQL.
5 янв 14, 13:52    [15378807]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
JustOxlamon
В общем текущая реализация как раз нормальная, только грамотно подойти к вопросу об индексах надо. А если я, например, разделю таблицу clients на 2, и вторую сделаю примерно такой:
balances:
id int not null,
balance int not null,

тут будет только один индекс - id - primary key
и сделаю связь один-к-одному с таблицей clients, тогда update же будет ощутимо легче?
Я думаю да, хороший вариант.
Тогда апдэйт будет мешать только другим таким же апдэйтам баланса, и конфликтовать с редкими чтениями баланса.
5 янв 14, 14:02    [15378818]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
JustOxlamon
Таким образом сам скул внутри себя как-то оптимизирует данные, чтобы не проводить полный пересчет, если не надо. Очень интересно. И да, у нас действительно Enterprise SQL.
Ну что сиквел тут сможет соптимизировать? В лучшем случае он будет не обращаться к таблице payments, но я в это не верю - скорее всего, он просто пересчитает баланс из payments для клиента и сохранит.

По ресурсоёмкости это будет совершенно равноценно отдельной таблице баланса, но с пересчётом из таблицы payments. То есть по сравнению с первоначальным вариантом не блокируется таблица клиентов, но блокируется на чтение таблица payments (ну и естественно блокируется индекс этой вьюхи).

А в варианте с отделной таблицей балансов блокируется эта таблица, но не блокимруется таблица payments, то есть это получается оптимальнее.
5 янв 14, 14:08    [15378827]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
aleks2
Guest
alexeyvg
JustOxlamon
Таким образом сам скул внутри себя как-то оптимизирует данные, чтобы не проводить полный пересчет, если не надо. Очень интересно. И да, у нас действительно Enterprise SQL.
Ну что сиквел тут сможет соптимизировать? В лучшем случае он будет не обращаться к таблице payments, но я в это не верю - скорее всего, он просто пересчитает баланс из payments для клиента и сохранит.


MS SQL НЕ пресчитывает суммы в Indexed View.
Он тупо и незатейливо прибавляет к текущему значению при вставке записи в базовую таблицу и вычитает при удалении записи из базовой таблицы.

Учи матчасть, чтоле?
5 янв 14, 14:16    [15378836]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
JustOxlamon
Member

Откуда:
Сообщений: 23
aleks2,
Ссылки на матчасть есть? Или можно смотреть планы выполнения? Ну т.е. любой документ подтверждающий слова есть? Потому что все, что я сейчас нарыл не содержит ничего подобного.

Пока значит оставляю как есть, и идею с архивацией закрываю. В пользу ind.view пока ничего не нашел.
5 янв 14, 15:21    [15378920]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
JustOxlamon
Ссылки на матчасть есть? Или можно смотреть планы выполнения?
В плане выполнения все видно.

К тому же, в случае использования индексированного представления, отпадает необходимость в try/catch. Который у вас написан абсолютно безграмотно.
5 янв 14, 16:19    [15379022]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
sdet
Member

Откуда:
Сообщений: 463
invm
JustOxlamon
Ссылки на матчасть есть? Или можно смотреть планы выполнения?
В плане выполнения все видно.

К тому же, в случае использования индексированного представления, отпадает необходимость в try/catch. Который у вас написан абсолютно безграмотно.

А разве с SET XACT_ABORT ON сейчас есть необходимость в try/catch?
5 янв 14, 16:39    [15379068]     Ответить | Цитировать Сообщить модератору
 Re: Грамотная организация запросов.  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
sdet
А разве с SET XACT_ABORT ON сейчас есть необходимость в try/catch?
Зависит от того, что находится в
JustOxlamon
		-- тут проверки на существование и валидность данных, если они важны, могу привести и эти примеры кода
Так что частично беру свои слова обратно - блок try/catch возможно нужен.
5 янв 14, 17:08    [15379128]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить