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

Откуда: Гималай
Сообщений: 2101
Приветствую всех, еще раз :)
Данная тема больше подошла бы для раздела Проектирование БД, но если модер позволит, хотелось бы чтобы данная тема была бы здесь, так как база на SQL Server 2005 и у участников данного подфорума очень много опыта по разработке различных приложений под MS SQL.
Разработал одну систему, скажем программный комплекс (скажем, связанная с телеметрией).
База на еще раз, упоминаю, на SQL Server 2005.
Клиентские приложения к базе подключаются через ADO
Есть различные устройства, с которых поступают данные. Сами устройства различные, но характер данных одинаковый. Затем эти данные обрабатываются на логическом уровне.
Есть программы (назовем DeviceChecker), которые периодически проверяют наличие новых данных на устройствах, и если таковые есть записывают в базу в одну таблицу, скажем в таблицу NewData.
Запись данных в таблицу NewData идет через одну общу хранимую процедуру.
При этом есть, на некоторых устройствах, может быть такое, что одни те же данные дважды записались, поэтому есть правило, что одни и те же данные от одного и того же устройства не могут быть записаны в таблицу в течении часа. Поэтому, хранимая процедура перед тем как записать данные, блокирует таблицу полностью, и проверяет на наличие дубликатов и если нету, записывает.
Есть другая программа (назову Processor), которая проверяет наличие новых данных в таблице NewData и если таковые есть, обрабатывает их на логическом уровне и ставит соответсвующий статус обработанной записи. Затем после обработки этих данных, дальше разбивает на "кусочки данных" и записывает их в таблицу Ready
Дальше эти данные с таблицы Ready передаются по различным устройствам, в зависимости от типа. Есть программы (Sender), тоже периодически проверяют таблицу и если есть записи на отправку, передают через устройство и ставят соотвествующий статус (то что передано или не удалось передать и т.д.)

Структуры таблиц
NewData
ID
Data
Device
...
Status
UID
Processor

Data - сами данные
Device - с какого устройства пришел
Status: 0 - не обработан, 1 - обрабатывается, 2 - оработан
Processor - наименование обработчика (Processor)
UID - GUID, используется при резеревировании записи на обработку

Ready
ID
Source_ID
Data
...
Status
UID
Processor

почти то же самое что и в NewData
Source_ID - ID записи из таблицы NewData, т.е. откуда они "произошел"
Status: 0 - не обработан, 1 - обрабатывается, 2 - оработан
Processor - наименование обработчика (Sender)
UID - GUID, используется при резеревировании записи на обработку

Проблема, в основном, в следующем так как данные может быть очень много для обработки, программ обработчиков на логическом уровне (Processor), может быть несколько, штук 5 и больше, т.е. 5 программ периодически сканят таблицу NewData на наличие данных для обработку. И при этом не должно быть так что одна и та же запись 2 раза обработается. Для этого Processor'ы периодически запускают такую инструкцию
SET ROWCOUNT 1
UPDATE NewData WITH (UPDLOCK) SET @Reserved=1, Status=1, UID=NEWID(), Processor=@Proc WHERE Status=0
SET ROWCOUNT 0
При этом чтобы не было такого что одну и ту же запись зарезервируют несколько программ, во время "резервации" блокируется таблицу на UPDATE

Та же ситуация и для Sender'ов, их несколько и они периодически проверяют данные для передачи по таблице Ready.

Во время блокировок таблиц NewData, Ready
невозможно добавлять новые записи или же резервировать записи для обработки.

Думал изменить систему, путем замены Processor'ов на триггер .NET CLR, который при вставке новых данных сразу же "обрабатывает" и записывает в Ready.
Но участники форума "говорят", что этим проблема может и не решиться.
Вот поэтому и данную тему поднимаю, посоветуйте пожалуйста.
Опыта в решении подобных задач нету.

Тема, которые создавал по .NET CLR триггерам, касающиеся данной проблемы
И еще по T-SQL

Спасибо за внимание.

P.S. Если непоятно изъяснился, извиняйте ;) Скажите что непонятно, попробую еще раз объяснить
28 июл 09, 15:26    [7469485]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
"Товарищ" iljy
Предлагал вариант с отдельной таблицей, т.е. скажем будет таблица Quee
В которую будут добавлять ID записей, которые необходимо обработать, после обработки каким-либой программой запись удаляется, я тоже сейчас склоняюсь к данному методу
Но вдруг у кого лучше идея будет :) Как говорится одна голова хорошо, а sql.ru лучше :))

И еще, хотел дополнить, также может быть необходимость переобработки определенных записей
28 июл 09, 15:37    [7469556]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
Алексей2003
Member

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

для спящего время бодрствования равносильно сну
28 июл 09, 15:46    [7469628]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
Спасибо Алексей2003 за внимание ;-)
Пока конкретных оптимизаций не делал еще, так как введение изменений очень "болезненный процесс" поэтому сначала хотел "посоветоваться".
Хотел .NET CLR триггеры ввести, но по теме .NET CLR триггера участники форума высказали свое мнение о том, что триггеры могут и не помочь.
Оптимизировать в первую очередь хотелось бы именно резерирование записей для обработки программами Processor и Sender, так как они налогают блокировку, из-за чего и вся "система стоит"
Решение проблемы с "дубликатами" и "двойными обработками" без наложения блокировок не вижу пока :-(
28 июл 09, 16:03    [7469788]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2orunbek
вводите таблицу "последний час" в которой хранится информация за последний час (какой нужно). когда необходимо, часть данных переливаете в основную таблицу, но уже без проверок.
+ делаете вьюшку с union all.

для спящего время бодрствования равносильно сну
28 июл 09, 16:20    [7469935]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
в смысле будет таблица ПоследнийЧас
в котором будут данные за последний час из таблицы NewData
И соответственно резервация данных идет из таблицы ПоследнийЧас?
правильно понял?
28 июл 09, 16:23    [7469961]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
Алексей2003
Member

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

а почему бы не воспользоваться грязным чтением при выборке IDшника строки, которую хотите выбрать? после этого делаете UPDATE той строки, которая нужна и вперед поехали.. если вдруг транзакция откатится, тогда status вернется в 0 и следующий обработчик будет брать эту запись опять..

для спящего время бодрствования равносильно сну
28 июл 09, 16:41    [7470124]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
Только за текущий час не сойдет, так как может возникнуть необходимость повторной обработки данных и за предыдущие сутки
А по поводу грязного чтения, такой вариант?:
declare @id bigint
set @id=-1
select @id from NewData with (readpast) where status=0)
if @id<>-1
    update NewData with(updlock) set status=1 where id=@id
28 июл 09, 17:08    [7470331]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
ChA
Member

Откуда: Москва
Сообщений: 10989
orunbek
Решение проблемы с "дубликатами" и "двойными обработками" без наложения блокировок не вижу пока :-(
Если какой-то записи сделано обновление, то она уже заблокирована, причём исключительно и до тех пор, пока не закончится транзакция. И никакой параллельный процесс(если только он не в READ UNCOMMITTED) не сможет прочитать эту запись. Хинт UPDLOCK в этом скрипте
SET ROWCOUNT 1
UPDATE NewData WITH (UPDLOCK) SET @Reserved=1, Status=1, UID=NEWID(), Processor=@Proc WHERE Status=0
SET ROWCOUNT 0
попросту бессмысленен.

Если правильно понял, то записи из NewData в результате обработки программой Processor перебрасываются в таблицу Ready. Если они переходят в разряд обработанных, то зачем они нужны в таблице NewData ? Пусть программа Processor сразу же после "захвата" удаляет запись из таблицы NewData. Грубо, что-то вроде такого
BEGIN TRAN
SELECT TOP 1 @ID = ID, ..., @FldN = FldN FROM NewData WITH (XLOCK, READPAST) WHERE Status=0
DELETE NewData WHERE ID = @ID
SELECT @ID AS ID, ..., @FldN AS FldN
COMMIT
Хотя, пожалуй, лучше оформить это процедурой и возвращать значения через параметры.
Если после обработки запись нужно сохранять в архиве, заведите отдельную таблицу Archieve и сбрасывайте данные обработанной записи туда, все значения этой записи программа Processor имеет. С таблицей Ready и программой Sender ситуация, похоже, аналогичная. Т.е., получили данные, удалили из входной табилцы, передали дальше, в том числе в архив, если таковой нужен.
28 июл 09, 17:13    [7470369]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
ChA
Member

Откуда: Москва
Сообщений: 10989
ChA
Кстати, в предложенном варианте нет необходимости в поле Status или Processor, поэтому в коде условие
WHERE Status=0
излишне.
28 июл 09, 17:16    [7470393]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
orunbek
Только за текущий час не сойдет, так как может возникнуть необходимость повторной обработки данных и за предыдущие сутки
А по поводу грязного чтения, такой вариант?:
declare @id bigint
set @id=-1
select @id from NewData with (readpast) where status=0)
if @id<>-1
    update NewData with(updlock) set status=1 where id=@id


примерно так. естественно тут нужно понять задачу и просчитать, могут ли быть пересечения данных по считыванию, чтобы не было коллизий...
28 июл 09, 17:19    [7470407]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
Алексей2003
Member

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

для спящего время бодрствования равносильно сну
28 июл 09, 17:22    [7470431]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
orunbek
Member

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

для спящего время бодрствования равносильно сну

извиняюсь что долго не отвечал, отходил
да, в первую очередь из-за того что логика обработки данных может отличаться и время обработки тоже соотвественно, это только на уровне Processor'а, из-за этого и несколько штук
могут быть данные, обработка которые займет милисекунды, могут быть данные, обработка которых может занять минуты из-за этого и разделение
а на уровне Sender'ов, там вообще, отправка некоторых данных вообще может быть отключена, а некоторых включена, не говоря уже о скорости обработки данных и Ready.
И эти факторы зависят от характера данных и т.д.
28 июл 09, 20:58    [7471124]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
2ChA
Спасибо за ваш ответ,
По теме, вот я тоже думал о таком варианте, т.е. при вставке записи уже обрабатывать и дальше передавать, данный вариант планировал на .NET CLR триггере реализовать, в данной теме про этот вариант и расписывал.
В данном случаи, были вопросы которые остались для меня не ясными:
1. Скажем, обработал запись из INSERTED, после завершения обработки, как изменить статус обработанной записи из INSERTED?
2. Как писал уже Алексею, могут быть различные данные в NewData, от которых и скорость обработки может быть различной от мс до нескольких минут. От сюда вопрос, к примеру добавили запись в таблицу NewData, время обработки которой, предположительно 2 минуты. В это время, т.е. пока идет обработка можно ли добавлять новые записи, также UPDATE'ить другие записи?

А по поводу обработки данных из таблицы Ready, метод обработки во время добавления не пойдет, так как во первых обработка бывает различной, и может быть нужно будет делать на различных компьютерах и принцип и особенности обработки каждой записи тоже быть отличной
28 июл 09, 21:23    [7471175]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
Поднимаю
29 июл 09, 11:28    [7472692]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
Рассматриваю следующий вариант предложенный участниками:
Будет простой триггер на вставку, который будет добавлять ID добавленных записей для обработки в отдельную таблицу, скажем ToProcess, обработчики периодически сканят именно эту таблицу, после завершения, удаляют обработанную запись.
При этом, количество записей в таблице ToProcess минимально будет (максимум 100-600), т.е. записи которые нужно обработать или же которые еще обрабатываются. В то время как при старой логике работы, сканируется вся таблица NewData, а количество записей может быть сотни миллионов и больше.
Соотвественно скорость резервирования записей на обработку должна быть гораздо больше.
Единственное что смущает, то что очень часто будут добавляться и удалятся записи в таблице ToProcess.
Как я знаю (может быть что неправильно понял), при удалении данных, физически из базы место занимаемое данными не освобождается, т.е. размер базы остается таким же и появляются "пустоты".
Соотвественно, при скорости поступлении сотни тысяч записей в течении часа, и при этом эта частота сохраняется круглые сутки, появление "пустот" в базе и как повлияет на скорость работы базы в целом
31 июл 09, 18:33    [7486327]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
vino
Member

Откуда:
Сообщений: 1191
orunbek, это разумный алгоритм (кэширование кода в таблице ToProcess), так как таблица маленькая и вопросы фрагментации здесь не существенны - вы же удаляете только записи строгой рамерности. А вот для данных должна быть только одна таблица (и только добавление новых записей) и после обработки никакие записи переносить не надо - так и от фрагментации избавитесь и блокировок будет существенно меньше.
Остается только вопрос корректного захвата записей таблицы ToProcess на обработку. И здесь, вполне возможно, понадобится поле с кодом обработчика, а также статуса и/или временнОй версии.
PS: Примерно такая же схема у меня успешно работает больше года
31 июл 09, 19:01    [7486413]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
vino,

Спасибо за ответ, попробую данный вариант, проблема "захвата" записи решена, есть столбец GUID типа, значение которого устанавливается во время апдейта "на захват", и повторно проверяется, примерно так:
declare @g uniqueidentifier, @id bigint, @reserved bit
set @g=newid() 
set @reserved=0
if exists (select * NewData where status=0) begin
     set rowcount 1    
     update NewData @id=id, uid=@g where status=0
     select @reserved=1 from NewData with (nolock) where id=@id and uid=@g
end
При добавлении/удалении волнует не проблема фрагментации таблицы ToProcess, а появление "пустот" в файлах базы данных. Как насчет этого?
И как насчет кода захвата?
31 июл 09, 20:50    [7486596]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
поднимаю
3 авг 09, 12:57    [7490444]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
sherzod_
Member

Откуда:
Сообщений: 598
Блог
как вариант можно использовать случайное равномерное распределение записей между "Processor"aми.
в таблицу NewData добавляем поле ProcessorNo.
при запуске процессора указываем ему No. Соответсвтенно он обрабатывает только записи с его No.

при достаточно хорошем анализе распределения и опционально добавочного кода(средсвами базы или приложений) для регулирования распределения можно, я думаю, добиться высокой производительности обработки.
3 авг 09, 13:34    [7490690]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
sherzod_
Member

Откуда:
Сообщений: 598
Блог
зы
и обойтись без локов))
3 авг 09, 13:35    [7490696]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
sherzod_
как вариант можно использовать случайное равномерное распределение записей между "Processor"aми.
в таблицу NewData добавляем поле ProcessorNo.
при запуске процессора указываем ему No. Соответсвтенно он обрабатывает только записи с его No.

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

это слишком "сложно" при выборке записей, думаю предложенный вариант попроще и пошустрее будет

P.S. а Шерзод, как дела?
3 авг 09, 14:29    [7491023]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
sherzod_
зы
и обойтись без локов))


локи по любому нужны, чтобы предотвратить двойную обработку
3 авг 09, 14:30    [7491024]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
sherzod_
Member

Откуда:
Сообщений: 598
Блог
orunbek
локи по любому нужны, чтобы предотвратить двойную обработку


дела отлично))

по-моему ты меня не понял.

каждый процесс будет обрабатывать свои записи и по-очереди.
выбрал, обработал изменил статус.
локи не нужны

а при совместном доступе к одной записи по принципу "кто первый" ты теряешь время проца(ов). пока один процесс изменяет статус все остальные стоят и курят.
3 авг 09, 14:39    [7491065]     Ответить | Цитировать Сообщить модератору
 Re: Изменение логики работы системы  [new]
sherzod_
Member

Откуда:
Сообщений: 598
Блог
select
	data
from
	new_data
where
	(ProcessorNo = [myNo]) and (your conditions)
;
1 добавочное условие не усложнение
тем более если (как предложено выше) архивировать периодически обработанные записи в другой таблице выборка будет ой-какой шустрой
3 авг 09, 14:42    [7491085]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить