Use SQLCLR to improve performance

Starting with MS SQL Server 2005, very powerful SQL CLR technology has been added to database developers.

This technology allows you to extend the functionality of SQL Server using .NET languages, such as C # or VB.NET.

Using SQL CLR, you can create your own stored procedures, triggers, custom types and functions, as well as aggregates, written in high-performance languages. This allows you to seriously improve performance and extend the functionality of the server to unimaginable boundaries.
')
Consider a simple example: we will write a custom function for cutting a string through a separator using SQL syntax and SQL CLR based on C # and compare the results.

User function returning a table

CREATE FUNCTION 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 = ''
end else if (@ index > 1) begin
set @part = LEFT (@text, @ index - 1)
set @text = RIGHT (@text, (LEN(@text) - @ index ))
end else begin
set @text = RIGHT (@text, (LEN(@text) - @ index ))
end
insert into @Tbl(part, ID_ORDER) values (@part, @i)
set @i=@i+1
end
RETURN
END
go


This function cuts the input string using a delimiter and returns a table. It is very convenient to use such a function, for example, to quickly fill a temporary table with records.

select part into #tmpIDs from SplitString( '11,22,33,44' , ',' )

As a result, the #tmpIDs table will contain

11
22
33
44

CLR Module written in C #

Create a SplitString.cs file with the following contents:

using System;
using System.Collections;
using System.Collections. Generic ;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class UserDefinedFunctions {
[SqlFunction(FillRowMethodName = "SplitStringFillRow" , TableDefinition = "part NVARCHAR(MAX), ID_ORDER INT" )]

static public IEnumerator SplitString(SqlString text, char [] delimiter)
{
if (text.IsNull) yield break ;

int valueIndex = 1;
foreach ( string s in text.Value.Split(delimiter, StringSplitOptions.RemoveEmptyEntries)) {
yield return new KeyValuePair< int , string >(valueIndex++, s.Trim());
}
}

static public void SplitStringFillRow( object oKeyValuePair, out SqlString value , out SqlInt32 valueIndex)
{
KeyValuePair< int , string > keyValuePair = (KeyValuePair< int , string >) oKeyValuePair;

valueIndex = keyValuePair.Key;
value = keyValuePair.Value;
}
}


Compile the module:

%SYSTEMROOT%\Microsoft.NET\Framework\v2.0.50727\csc.exe /target:library c:\SplitString.cs

At the output we get SplitString.dll

Now, you need to enable CLR usage in SQL Server.

sp_configure 'clr enabled' , 1
go
reconfigure
go

Everything, it is possible to connect the module.

CREATE ASSEMBLY CLRFunctions FROM 'C:\SplitString.dll'
go

And create a custom function.

CREATE FUNCTION [dbo].SplitStringCLR(@text [nvarchar]( max ), @delimiter [ nchar ](1))
RETURNS TABLE (
part nvarchar( max ),
ID_ODER int
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME CLRFunctions.UserDefinedFunctions.SplitString


More about CLR

1. The build is uploaded to the server and stored there. Functions that reference the assembly are already stored in the database. Therefore, it is necessary that the assembly be loaded on the server where the base is being transferred.

2. When creating an assembly, if necessary, the PERMISSION_SET argument is specified, which defines the permissions for the assembly. I advise you to look at MSDN . Briefly: SAFE - allows to work only with the base; EXTERNAL_ACCESS - allows to work with other servers, file system and network resources; UNSAFE - anything, including WinAPI.

3. There are features when debugging, which ones are specified in MSDN .

results

To compare the speed of the usual SplitString and SplitStringCLR, I called these functions 1000 times with an input string consisting of 100 comma-separated numbers.

The average working time for SplitString was 6.152 ms, and for SplitStringCLR 1.936 ms.

The difference is more than 3 times.

I hope it will be useful to someone.