Использование SQL CLR для увеличения производительности
Начиная c MS SQL Server 2005 в распоряжение разработчиков баз данных была добавлена очень мощная технология SQL CLR.Эта технология позволяет расширять функциональность SQL сервера с помощью .NET языков, например C# или VB.NET.
Используя SQL CLR можно создавать написанные на высокопроизводительных языках свои хранимые процедуры, триггеры, пользовательские типы и функции, а также агрегаты. Это позволяет серьезно повысить производительность и расширить функциональность сервера до немыслимых границ.
Рассмотрим простой пример: напишем пользовательскую функцию разрезания строки по разделителю используя SQL синтаксис и SQL CLR на базе C# и сравним результаты.
Пользовательская функция, возвращающая таблицу
CREATEFUNCTION SplitString (@text NVARCHAR(max), @delimiter nchar(1))
RETURNS @Tbl TABLE (part nvarchar(max), ID_ORDER integer) AS
BEGIN
declare @index integer
declare @part nvarchar(max)
declare @i integer
set @index = -1
set @i=1
while (LEN(@text) > 0) begin
set @index = CHARINDEX(@delimiter, @text)
if (@index = 0) AND (LEN(@text) > 0) BEGIN
set @part = @text
set @text = »
endelseif (@index > 1) begin
set @part = LEFT(@text, @index — 1)
set @text = RIGHT(@text, (LEN(@text) — @index))
endelsebegin
set @text = RIGHT(@text, (LEN(@text) — @index))
end
insertinto @Tbl(part, ID_ORDER) values(@part, @i)
set @i=@i+1
end
RETURN
END
go
Эта функция разрезает входную строку используя разделитель и возвращает таблицу. Применять такую функцию очень удобно, например, для быстрого заполнения временной таблицы записями.
select part into #tmpIDs from SplitString(‘11,22,33,44’, ‘,’)
В результате таблица #tmpIDs будет содержать
11
22
33
44
Модуль CLR написанный на C#
Создадим файл SplitString.cs со следующим содержимым:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
publicclass UserDefinedFunctions {
[SqlFunction(FillRowMethodName = "SplitStringFillRow", TableDefinition = "part NVARCHAR(MAX), ID_ORDER INT")]
staticpublic IEnumerator SplitString(SqlString text, char[] delimiter)
{
if(text.IsNull) yieldbreak;
int valueIndex = 1;
foreach(string s in text.Value.Split(delimiter, StringSplitOptions.RemoveEmptyEntries)) {
yieldreturnnew KeyValuePair(valueIndex++, s.Trim());
}
}
staticpublicvoid SplitStringFillRow(object oKeyValuePair, out SqlString value, out SqlInt32 valueIndex)
{
KeyValuePair keyValuePair = (KeyValuePair) oKeyValuePair;
valueIndex = keyValuePair.Key;
value = keyValuePair.Value;
}
}
Скомпилируем модуль:
%SYSTEMROOT%Microsoft.NETFrameworkv2.0.50727csc.exe /target:library c:SplitString.cs
На выходе получаем SplitString.dll
Теперь, необходимо разрешить использование CLR в SQL Server.
sp_configure ‘clr enabled’, 1
go
reconfigure
go
Все, можно подключать модуль.
CREATE ASSEMBLY CLRFunctions FROM’C:SplitString.dll’
go
И создавать пользовательскую функцию.
CREATEFUNCTION [dbo].SplitStringCLR(@text [nvarchar](max), @delimiter [nchar](1))
RETURNSTABLE (
part nvarchar(max),
ID_ODER int
) WITHEXECUTEAS CALLER
AS
EXTERNAL NAME CLRFunctions.UserDefinedFunctions.SplitString
Дополнительно о CLR
1. Сборка загружается на сервер и хранится там. Функции, которые ссылаются на сборку уже хранятся в базе. Поэтому нужно, чтобы на сервере, куда переносится база, сборка была загружена.
2. При создании сборки, если нужно, указывается аргумент PERMISSION_SET, который определяет разрешения для сборки. Советую посмотреть MSDN. Вкратце: SAFE — разрешает работать только с базой; EXTERNAL_ACCESS — разрешает работать с другими серверами, файловой системой и сетевыми ресурсами; UNSAFE — все что угодно, включая WinAPI.
3. Есть особенности при отладке, какие именно, указано в MSDN.
Результаты
Для сравнения скорости работы обычной SplitString и SplitStringCLR я вызвал эти функции 1000 раз с входной строкой, состоящей из 100 разделенных запятой чисел.
Среднее значение времени работы для SplitString получилось 6.152 мс, а для SplitStringCLR 1.936 мс.
Разница более чем в 3 раза.
Надеюсь, это будет кому-нибудь полезно.
Автор материала: Medal
Мой блог находят по следующим фразам