Какие проблемы возникают при использовании хранимых процедур CLR?

Ответ 1

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

Встроенные типы hierarchyid и геопространственные (т.е. geography) в SQL Server 2008 являются хорошими примерами проблемы денормализации. Оба содержат (почти) произвольно большой объем данных, которые трудно нормализовать без ущерба для производительности - вам нужно будет использовать рекурсию или курсоры для какой-либо значимой работы с ними в противном случае или использовать крысиное гнездо триггеров и/или запланированных задач для поддерживайте таблицу денормализации.

Другой проблемой, которую я решил с помощью CLR-типов, является встроенное сжатие. Это может показаться бессмысленным или академическим упражнением, но когда ваши полностью нормализованные данные вставляются в терабайты, сокращение на 80-90% означает много. SQL теперь имеет собственное встроенное сжатие, а SQL 2005 имеет vardecimal, а также хорошие инструменты, но алгоритм "минимизации", поддерживающий домен, может быть в несколько раз более эффективным с точки зрения загрузки и сжатия процессора. Очевидно, что это не относится к каждой проблеме, но относится к некоторым.

Еще одна очень распространенная проблема, часто встречающаяся на этом сайте, - это генерация последовательности "на лету" - например, последовательность последовательных дат. Общими решениями являются рекурсивные CTE, статические таблицы последовательности и малоизвестные таблицы spt_values, но простой CLR UDF работает лучше, чем любой из них, и предлагает гораздо большую гибкость.

В моем списке: Пользовательские потоковые агрегаты также очень полезны, особенно для любых связанных с статистикой. Есть некоторые вещи, которые вы просто не можете составить из встроенных агрегатов SQL, таких как медианы, взвешенные скользящие средние и т.д. UDA могут также принимать несколько аргументов, чтобы вы могли их параметризовать; технически агрегат не гарантирует получение данных в каком-либо конкретном порядке в текущей версии SQL Server, но вы можете обойти это ограничение, подав ему ROW_NUMBER в качестве дополнительного аргумента и использовать его для реализации практически любой функции окна (иметь совокупность, выплюнуть UDT, который затем может быть преобразован в таблицу).

На самом деле очень неприятно, как мало примеров из действительно полезных приложений SQL-CLR; поиск в Google, и вы получите 10 миллионов результатов, каждый из них для некоторой глупой конкатенации строк или регулярного выражения. Они полезны, но в течение нескольких минут вы узнаете о UDT и UDA SQL в частности, и вы начнете видеть множество применений для них в своих приложениях. Не сходите с ума, конечно, - тщательно подумайте о том, есть ли лучшее решение в чистом SQL, но не обесценивайте их.

Ответ 2

Обработка строк - поиск регулярных выражений является классическим. Очень легко разоблачить в CLR, очень сложно сделать в прямом T-SQL.

См. эту ссылку для получения подробной информации о реализации и микро-бенчмарке (SQLCLR is only 47 milliseconds compared to 6.187 seconds for the T-SQL UDF).

Ответ 3

Было упомянуто манипулирование строками (регулярные выражения), а также арифметика DateTime, и, конечно же, другие крупные вызовы внешних веб-сервисов.

Ответ 4

Вот пример того, что я использовал CLR-процессы для того, что я считал аккуратным:

Временные обновления данных из внешних веб-сервисов с использованием хранимых процедур CLR и заданий SQL.

У нас есть приложение, которое синхронизирует некоторые данные, которые он отслеживает, с внешними фидами данных. Синхронизация выполняется еженедельно для всех и по требованию для одиночных обновлений, так что у меня есть существующий API webservice для доступа к нему. Вещи уже запланированы службой Windows, но я подумал, почему бы не планировать их, как наши другие задания SQL?

Я создал хранимую процедуру CLR, которая возвращает API-интерфейс приложения webservice. Затем я добавил несколько parms для @RecordID для поддержки одиночной синхронизации и запланировал его в SQL-заданиях Enterprise Manager.

Теперь я могу использовать Job для запуска dB syncs или использовать proc в других обработках SQL или Triggers для обновления данных из внешнего фида.

В будущем может оказаться более понятным использовать API-интерфейс приложения webservice и просто использовать внешний веб-сервис напрямую. Пока что это было очень быстро реализовать и классный способ расширить функциональность для группы SQL.

Ответ 5

• Пользовательские агрегаты
• Обработка строк
• Пользовательские типы данных

Честно говоря, я вижу только обработку строк, которая включает разделение CSV на строки.

Я бы рассмотрел все, что требовало больше, чем уровень доверия по умолчанию, чтобы быть вне пределов, если только я не был DBA, делающим материал типа DBA.

Из MSDN с примерами в RegEx и RSS: Использование интеграции CLR в SQL Server 2005

Ответ 6

Это очень полезно для вытаскивания данных из системы, которая не обеспечивает традиционный SQL-интерфейс, или реализация этого интерфейса поставщика не соответствует.

У нас есть основное приложение, построенное на старой платформе MUMPS, работающей в базе данных Intersystems Cache. Данные являются иерархическими, а не реляционными по своей природе. Основной глобальный массив (т.е. Таблица) имеет несколько уровней данных и элементов, сгруппированных по номеру учетной записи. Сканирование даже одного столбца требует, чтобы весь глобальный загружался с диска, и он занимает 8 часов. Поставщик действительно снабжает драйвер ODBC и сопоставления с глобальными, но часто приводит к сканированию и чрезвычайно медленным запросам.

Я построил табличную функцию, которая выполняет программу ObjectScript (Intersystem dialect of MUMPS), выполняет ее на сервере Cache и возвращает выходные строки в виде строк данных. Я могу настроить путь доступа к данным на стороне MUMPS (что действительно необходимо для получения эффективного доступа к данным), предоставляя конкретную программу для выполнения на этой стороне, а затем легко импортировать данные в MSSQL в качестве встроенного источника данных ad-hoc.

Я могу использовать TVF для управления выбором данных или использовать CROSS APPLY, чтобы сделать поиск на другом конце, и он достаточно эффективен. Я могу даже запускать несколько запросов на удаленном конце параллельно, если я заставляю MSSQL использовать параллельный план выполнения.