Использование .NET CLR Stored Procedure в триггере

orunbek
Дата: 10.06.2011 23:28:00
Приветствую.
Сервер базы данных: 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.06.2011 23:51:42
Уберите все из триггера и асинхронно считайте джобами до посинения.
Mnior
Дата: 11.06.2011 12:49:27
Если в таблице TblRecords нет никакого смысла кроме как вставить данные для обработки, то выкиньте таблицу и отсылайте сообщения в ServiceBroker, ну и напишите обработчик очереди сообщений, сразу в CLR процедуре. И асинхронно и параллельно.
orunbek
Дата: 12.06.2011 08:05:49
В таблице есть смысл, из этих таблиц различные отчеты строятся.
По задаче я передал только суть, в реальной задаче кроме указанных полей есть еще куча других, но задача в обработке записей при поступлении в базу.
До этого я решал через определенное приложение, которая периодически проверяла таблицы на наличие не обработанных записей (по определенному значению из таблицы), при котором соотвественно происходила блокировка, чтобы одна и та же запись 2 раза не обработалась.
Вот из-за этого, т.е. чтобы блокировка таблицы была по минимуму, думал о варианте, когда при добавлении в таблицу сразу же программе (.NET CLR SP) передается ID записи, которая еще не обработана.
Но возникла проблема, когда таблица блокируется на время обработки и нету асинхронной, паралелльной обработки.

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

В данном случае проблема передачи ID записи в очередь основного приложения? Каким образом можно это реализовать?
Без блокировки TblRecords?
invm
Дата: 12.06.2011 09:38:14
orunbek,

Заведите себе таблицу-очередь, и работайте с ней. Тогда вставка в основную таблицу должна будет выглядеть примерно так:
insert into TblRecords (...)
output inserted.ID into TblQueue (ID)
values(...)
Mnior
Дата: 12.06.2011 19:24:16
invm, нафига?

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

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

Ваш КО.
invm
Дата: 12.06.2011 19:51:11
Mnior,

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

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

IMXO, что-то новое надо сразу в Open Source. Спекуляции в профессии это гиблый номер. А разрыв между бизнесом и специалистами будет при любом уровне развития, работы никогда не будет меньше. KO.
invm
Дата: 13.06.2011 00:27:54
Mnior,

Ну вы же сами признали, что изобретение велосипедов есть один из путей познания. А применение стандартных, проверенных и дешевых решений без четкого понимания что, как, почему и зачем, обычно ничем хорошим не заканчивается. Так что повторюсь -- пускай ТС сам выберет для себя подходящее решение.
Mnior
Дата: 13.06.2011 02:28:37
invm. Выбрать можно после понимания. Он не должен выбирать, он должен попробовать всё, если хочет понимать досконально.

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

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