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

Откуда: Гималай
Сообщений: 2101
Приветствую.
Сервер базы данных: SQL Server 2008 R2
Есть (описываю обощенно) система, в одну таблицу в которой записываются данные, назем TblRecords.
Есть несколько web-шлюзов, через которые должны обработаться данные записываемые в таблицу TblRecords, в зависимости от значений определенного поля.

Обработка через web-шлюзы проходит в несколько этапов, кроме этого есть сложные действия, связанные не только с данными. В связи с чем вся эта обработка проходит в хранимке VB.NET в виде .NET CLR Stored procedure. При этом длительность обработки одной записи может длиться более 1ой минуты.

Вызов данной хранимки делаю в триггере на INSERT для таблицы TblRecords. В таблицу TblRecords одновременно могут добавляться более 100 записей. И когда работает .NET CLR хранимка таблица блокируется до завершения, соответственно другие операции добавления, чтения или изменения "ждут" пока завершится .NET CLR хранимка.
Собственно вызов хранимки (если считать что хранимка называется CLR_SP_Processor
CREATE TRIGGER [dbo].[TR_TblRecords_INSERT] ON [dbo].[TblRecords]
AFTER INSERT
AS
BEGIN
	DECLARE
		@ID bigint
	DECLARE
		CUR_Records CURSOR LOCAL FAST_FORWARD FOR
			SELECT ID FROM inserted
	OPEN CUR_Records
	FETCH NEXT FROM CUR_Records INTO @ID
	WHILE @@FETCH_STATUS=0 BEGIN
		EXEC CLR_SP_Processor @ID
	--next record
		FETCH NEXT FROM CUR_Records INTO @ID
	END
	CLOSE CUR_Records
	DEALLOCATE CUR_Records
END

Вопрос: Еще каким другим образом можно вызывать .NET CLR хранимку для добавленной записи?
Скажем так, осуществить асинхронную работу .NET CLR SP, к примеру добавил запись, вызвалась хранимка и "сама по себе" обрабатывает запись, при этом таблица и обрабатываемая запись не блокируется (кроме случаев обновления)
Потому как при вызове хранимки в тригерре обработки добавлений таблица блокируется на время обработки .NET CLR SP, при этом хранимка может работать и 1 минуту, и 5 минут.

Спасибо за внимание!
10 июн 11, 23:28    [10800103]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Уберите все из триггера и асинхронно считайте джобами до посинения.
10 июн 11, 23:51    [10800257]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Если в таблице TblRecords нет никакого смысла кроме как вставить данные для обработки, то выкиньте таблицу и отсылайте сообщения в ServiceBroker, ну и напишите обработчик очереди сообщений, сразу в CLR процедуре. И асинхронно и параллельно.
11 июн 11, 12:49    [10801431]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
В таблице есть смысл, из этих таблиц различные отчеты строятся.
По задаче я передал только суть, в реальной задаче кроме указанных полей есть еще куча других, но задача в обработке записей при поступлении в базу.
До этого я решал через определенное приложение, которая периодически проверяла таблицы на наличие не обработанных записей (по определенному значению из таблицы), при котором соотвественно происходила блокировка, чтобы одна и та же запись 2 раза не обработалась.
Вот из-за этого, т.е. чтобы блокировка таблицы была по минимуму, думал о варианте, когда при добавлении в таблицу сразу же программе (.NET CLR SP) передается ID записи, которая еще не обработана.
Но возникла проблема, когда таблица блокируется на время обработки и нету асинхронной, паралелльной обработки.

Сейчас думаю о варианте, схожей предложенный Mnior'ом.
При котором есть .NET приложение, которой нужно передавать ID добавленной записи, при котором основное приложение создает поток, в котором уже запись обрабатывается через web-шлюз.
Чтобы не создавать большое количество потоков, потому как количество одновременных добавлений может быть много.
Хочу создать очередь обработки, по типу FIFO. Когда создается фиксированное количество потоков, когда определенный поток завершает свою работу, программа берет из очереди первую добавленную запись и обрабатывает через поток.

В данном случае проблема передачи ID записи в очередь основного приложения? Каким образом можно это реализовать?
Без блокировки TblRecords?
12 июн 11, 08:05    [10802958]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
orunbek,

Заведите себе таблицу-очередь, и работайте с ней. Тогда вставка в основную таблицу должна будет выглядеть примерно так:
insert into TblRecords (...)
output inserted.ID into TblQueue (ID)
values(...)
12 июн 11, 09:38    [10803046]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm, нафига?

Для слепых:
Mnior
ServiceBroker
Количество параллельных процессов регулируется опцией: MAX_QUEUE_READERS
orunbek
FIFO
Оно так и работает. Зачем мучится писать ласапед когда готовое уже в нос упирается.
orunbek
При котором есть .NET приложение
В PROCEDURE_NAME можете указать и CLR процедуру.
Или можете в отдельном приложении (на другой машине) читать эту очередь (и даже реагировать на внешнюю активацию, пример).
orunbek
передачи ID записи
В message_body можно положить хоть XML со всеми нужными данными (чтоб не читать всё повторно по сто раз), да хоть в бинарные виде.

Вы можете одновременно вставлять запись в очередь и в таблицу или сразу в триггере. Если хотите по одной записи, тогда курсор придётся писать (нельзя создать одной командой сразу несколько сообщений , разве что динамикой). Со стороны .Net в одну строку десериализовать в элемент класса, замэпав атрибуты.

Ваш КО.
12 июн 11, 19:24    [10804185]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Mnior,

Затем, чтобы ТС сам выбрал из различных вариантов.
12 июн 11, 19:51    [10804241]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
Затем, чтобы ТС сам выбрал из различных вариантов.
Да пожалуйста, вопрос то не в этом.
Зачем дополнительная таблица для очереди? Если уже есть таблица (притом ещё со статусами).
Хотя согласен, это ещё один (третий) способ. С таблицей чуть проще.

invm, вообще-то не хорошо предлагать писать свой ласапед. В первую очередь человек должен уметь применять уже готовое, а не клепать квадратные велосипеды. Не, ласапеды надо делать, но для того лишь, чтобы понимать кухню, для собственного развития, и не применять микроскопы вместо молотка. Но применять готовые отработанные проверенные стандартные дешёвые и всем известные методы/продукты. Велосипедов у нас и так океан, неминуемо падающих в забвение.

IMXO, что-то новое надо сразу в Open Source. Спекуляции в профессии это гиблый номер. А разрыв между бизнесом и специалистами будет при любом уровне развития, работы никогда не будет меньше. KO.
12 июн 11, 23:28    [10804794]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Mnior,

Ну вы же сами признали, что изобретение велосипедов есть один из путей познания. А применение стандартных, проверенных и дешевых решений без четкого понимания что, как, почему и зачем, обычно ничем хорошим не заканчивается. Так что повторюсь -- пускай ТС сам выберет для себя подходящее решение.
13 июн 11, 00:27    [10804931]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm. Выбрать можно после понимания. Он не должен выбирать, он должен попробовать всё, если хочет понимать досконально.

Вы предложили явно (для нас) ласапед (что зря, т.к. без объяснения). А написать он его должен для себя, поняв некоторые (далеко не все) тонкости работы SB и почему именно он так спроектирован.
Повторюсь: В первую очередь человек должен уметь искать и применять уже готовое.
Решать (не применять) задачу можно (и лучше) до, никого не спрашивая (иначе смысла нет). Ещё со школы. Решение любой задачи должно быть всегда (у orunbek оно есть). Но решение не повод голого применения. Всё равно, уже всё сделано до нас, и нам следует не увеличивать этот хаос. Естественно инструменты (точнее языки) не всегда полностью покрывают всё. Да, используемые инструменты надо улучшать, но опять таки, не изобретая (гордо) свой ласапед с нуля.

orunbek может изучить сразу SB, строя модель его работы. Явно узнает что-то новое, и для каких всевозможных задач его ещё можно применять. Тем более что понимание у него концептуальное, что ещё меньше ставит полезность вашего частного случая в этой тематике.
Ваш случай, как раз расширяет понимание основных принципов самой работы скуля, а не асинхронность/параллельность/очереди/и всё такое. Не зная оных решить задачу сложнее, что может плохо сказаться на обучении.
Ваш случай, если строго подходить - костыль/микроскоп и т.п. Если вы (вдруг) считаете что очереди это тупо таблица, то зря. Да, очередь можно отобразить как системную таблицу (как и всякие другие системные объекты), но внутри она может быть реализована немного по своему.
На скуле можно всё многое решить, но это не повод применения.
13 июн 11, 02:28    [10805160]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Mnior,

Ну уж не надо меня так опускать -- что такое очередь/асинхронность/параллелизм и т.п. я узнал гораздо раньше, чем что такое таблица И я ни в коей мере не против SB. Все гораздо проще. По словам ТС, для обработки данных у него есть как CLR-процедура, так и приложение. Что он выберет в конечном итоге нам доподлинно не известно и навязывать ему выбор мы не можем, т.к. не знаем задачи. Так вот, способ с промежуточной таблицей как раз и пригодится, если ТС остановится на варианте с приложением.
13 июн 11, 10:46    [10805394]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
Ну уж не надо меня так опускать -- что такое очередь/асинхронность/параллелизм и т.п. я узнал гораздо раньше
Это где я утверждал обратное?

invm
Так вот, способ с промежуточной таблицей как раз и пригодится, если ТС остановится на варианте с приложением.
Это почему? Как ни крути таблица (со всеми правильно составленными к ней запросами) есть костыль. Приложение/не приложение - по барабану.

Повторяю - умение написать решение через чистый скуль, есть навык понимания механизмов скуля, а не решения самой задачи.
Для очередей понимать работу скуля с таблицами не обязательно.
13 июн 11, 19:12    [10806996]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Mnior
invm
Ну уж не надо меня так опускать -- что такое очередь/асинхронность/параллелизм и т.п. я узнал гораздо раньше
Это где я утверждал обратное?

Вот это
Mnior
Ваш случай, как раз расширяет понимание основных принципов самой работы скуля, а не асинхронность/параллельность/очереди/и всё такое. Не зная оных решить задачу сложнее, что может плохо сказаться на обучении.

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

Mnior
Это почему? Как ни крути таблица (со всеми правильно составленными к ней запросами) есть костыль. Приложение/не приложение - по барабану.

Потому что адаптировать уже готовое приложение под доп.таблицу гораздо легче, чем под SB. А если еще и вычитка организована через процедуры/вьюхи, то весьма вероятно, что приложение и менять не потребуется. И, с точки зрения трудозатрат, иногда поставить костыль значительно выгоднее, чем привести уже рабочий вариант к идеологически правильному виду.
13 июн 11, 21:48    [10807515]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
Потому что адаптировать уже готовое приложение под доп.таблицу гораздо легче, чем под SB. А если еще и вычитка организована через процедуры/вьюхи, то весьма вероятно, что приложение и менять не потребуется.
Это вы уже фантазировать начали. Кода на SB не больше.

Строка на сервис, строка на очередь - вместо описания таблицы. Запрос из очереди тот же что и селект. Вставка сообщения то же что и в таблицу, ну ладно две команды (ВEGIN DIALOG + SEND). Удаление короче - END DIALOG.

Всё остальное идентично. А с таблицей надо правильно команды расставлять - что-бы не блокировало и не считывало одно и тоже, организовывать потоки (это ещё куча кода) - это всё надо знать как. И работать всё равно не будет так слажено как SB. А SB - топор, синтаксис однозначный.

С таблицей ощущение что всё легко, но самом деле сложнее и придётся допиливать по ходу. С SB кажется что всё сложно, т.к. новый синтаксис, а на самом дело всё проще. Ну ладно ещё одну команду надо ALTER DATABASE ENABLE BROKER.
14 июн 11, 00:52    [10807931]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Mnior,

Никаких фантазий. В варианте с таблицей, конечно если приложение адекватно написано, изменений минимум.
14 июн 11, 01:18    [10807954]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
Всем спасибо, буду пытаться "переварить" Service Broker
Если не получится, буду "плодить" новые темы ;-)
Ну а если вообще никак, то буду придумывать свой велосипед
16 июн 11, 08:13    [10819453]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
изменений минимум
С чёго вы это взяли?
Наглядно можете написать?
16 июн 11, 12:00    [10820914]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Mnior
invm
изменений минимум
С чёго вы это взяли?
Наглядно можете написать?

Есть готовое приложение, обрабатывающее новые записи из таблицы. Чтобы адаптировать его для работы с таблицей-очередью, достаточно изменить запрос, выбирающий записи для обработки и запрос завершения обработки. Если же эти запросы реализованы не напрямую к таблице, а через вью/процедуру, то приложение вообще менять не придется.
16 июн 11, 12:17    [10821000]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm,
Приложение не надо менять вообще при всех случаях (хоть с процедурами хоть прямиком, хоть очередь, хоть триггер, хоть обработка другой таблицы), т.к. основная таблица не меняется и не меняется всё остальное.

Приложение не имеет и не могло иметь код запуска, оно запускалось аля
EXEC CLR_SP_Processor @ID
Какая разница что стоит вокруг этой строки?!
Приложение не инициатор.
Вы видимо о чём-то другом говорите, не по этой теме.

Если приложение было инициатором, само считывало строки для обработки, и решало какие надо и какие нет. То вопрос стоял бы совсем по другому - уже есть стратегия инициации обработки, её нужно поменять.
Но сейча нет никакой.

invm, пока я вас не понял.
16 июн 11, 16:49    [10823364]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Mnior,

Я об этом:
orunbek
...До этого я решал через определенное приложение, которая периодически проверяла таблицы на наличие не обработанных записей...
16 июн 11, 17:07    [10823503]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
orunbek
Member

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

Я об этом:
orunbek
...До этого я решал через определенное приложение, которая периодически проверяла таблицы на наличие не обработанных записей...


Да, я так и делал, даже в свое время тему поднимал
Создание системы очереди
и даже тогда предлагали мне Service Broker, но я чёт не "допёр", но в данной схеме, были проблемы с блокировками, конечно это может быть из-за неправильной или не оптимальной организации данной системы, но сейчас хочу применить отточенный вариант организации системы очереди
16 июн 11, 20:16    [10824457]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
Я об этом:
orunbek
...До этого я решал через определенное приложение, которая периодически проверяла таблицы на наличие не обработанных записей...
Да, это я пропустил. Но это ничего не меняет.
Повторяю:
Mnior
Если приложение было инициатором, само считывало строки для обработки, и решало какие надо и какие нет. То вопрос стоял бы совсем по другому - уже есть стратегия инициации обработки, её нужно поменять.
Т.е. как ни крути, менять надо. Хоть там через процедуры (что очень маловероятно), хоть напрямую.

invm
достаточно изменить запрос, выбирающий записи для обработки и запрос завершения обработки
А это ещё ограничивает круг приложений, заданной структурой/последовательностью работы с записями. Как вы видите, организовано всё совершенно по другому.

Но допустим и так, тогда для очереди совершенно точно также можно поменять процедуры и приложение менять не надо.

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

Вывод: через таблицу ничем не лучше, чем через очереди.
А если с таблицами вероятно проффтыкать и неправильно организовать процесс, то смысл сводится только к обучению, и не к выбору. (Если нет SB, то опять никакого выбора ;) )
Согласны?

Далее лишь можно разобрать ошибки подхода в табличном случае и как правильно организовать процесс.
16 июн 11, 22:16    [10824883]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
Mnior,

Я абсолютно согласен с вами в том, что:
а) таблица-очередь это костыль;
б) нужно стремиться к использованию штатных механизмов (в данном случае SB)

Я всего лишь хотел сказать, что ситуации встречаются разные, и в некоторых из них поставить костыль бывает банально выгоднее с точки зрения экономики, или как временное решение. Например: у ТС есть готовое приложение. Адаптация оного под использование "костыльной" таблицы-очереди поможет частично разгрузить проблемное место, пока ТС изучит SB и реализует обработку через него (возможно пересмотрев логику этой обработки). Вот и получится, что постановка костыля освободит ТС от необходимости прикручивать SB в режиме "хватай мешки, вокзал отходит".
17 июн 11, 00:26    [10825345]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
Например: у ТС есть готовое приложение. Адаптация оного под использование "костыльной" таблицы-очереди поможет частично разгрузить проблемное место ...
Ok.
Покажите где конкретно и что конкретно ему надо сделать, чтобы у него заработало временное решение.
Это может быть полезно не только ему.

Если я правильно понял он читал (UPDATE) прямо из таблицы, а ему надо читать (DELETE Top(1)) из другой (таблица-очередь). Так?
17 июн 11, 16:41    [10829989]     Ответить | Цитировать Сообщить модератору
 Re: Использование .NET CLR Stored Procedure в триггере  [new]
orunbek
Member

Откуда: Гималай
Сообщений: 2101
у меня было велосипедный очередь был в другой схожей системе
а это уже новая, в которой я уже хотел использовать обработку запросов уже прямо в базе посредством .NET CLR SP
а сейчас уже пробую Service Broker, сегодня-завтра опишу какой вариант использовал, т.е. сами коды и обработку отравленных сообщений, точнее "небольшой велосипед" вместо обработки отравленных сообщений
17 июн 11, 20:19    [10831659]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить