september 13, 2024
Using SQL CLR technology to enhance the performance of MS SQL functionality
It is crucial to ensure the high performance of a large analytical system at all stages of operation. However, the built-in functionality of MS SQL does not always process data efficiently.
For instance, when working with large data sets, it is highly discouraged to manipulate them using user-defined functions, as this significantly slows down the program. This is because MS SQL follows a set-theoretic programming paradigm, meaning that the language treats data as set objects. It excels at data retrieval and handling data as sets of records. However, user-defined functions in MS SQL act as a «black box» and perform operations row by row when processing data.
One way to improve the speed of such functions is by using SQL CLR technology. This allows the extension of SQL Server functionality through high-level programming languages such as C# or VB.NET, enabling the creation of user-defined stored procedures, triggers, data types, and functions using high-performance programming languages. This significantly enhances performance and allows the integration of powerful algorithms and operations that are not available within pure SQL, thereby greatly expanding the capabilities of SQL Server.
In this article, we will compare two implementations of string-splitting algorithms using C# and T-SQL.
Let’s consider the implementation of the algorithm in MS SQL (Listing 1).
The function takes two inputs: the string to be split into substrings and the delimiter string. The function outputs a table with the substring number and the corresponding substring itself.
Listing 1. Algorithm implementation in T-SQL
Now let’s create a similar function in Visual Studio using the C# language. To do this, create a SQL Server database project in Visual Studio. Then, through Solution Explorer, add a user-defined CLR function.
Now we can proceed with implementing the algorithm itself (Listing 2)
Listing 2. Algorithm implementation in C#
Before comparing the two algorithms, the integration of the CLR function into the MS SQL environment needs to be set up. For this:
- Build the project in Visual Studio
- Configure the external assembly connection in your MS SQL database (Screenshots 1, 2)
Screenshot 1. Assemblies => New Assembly
Screenshot 2. In the Path field, enter the path to the DLL file in your project containing the CLR function
Note: If you are not working on a local server, you need to ensure that the database owner listed in master matches the owner listed in your database. Otherwise, use the following command (Listing 3).
Listing 3. Script to change the database owner
Now you can create the function itself (Listing 4).
Listing 4. Script for creating the function in MS SQL
Let’s test the functions with different data volumes. For convenience, all results will be recorded in a table (Table 1)
Table 1. Test results
| Number of input rows | Execution time of CLR function | Execution time of T-SQL function |
|---|---|---|
|
10000 |
00:00:02 |
00:00:14 |
|
100000 |
00:00:16 |
00:02:06 |
|
1000000 |
00:02:40 |
00:21:32 |
Thus, we can see that using SQL CLR technology can improve query performance and significantly speed up execution times. This is a considerable advantage when working with business logic, where it is essential to quickly process large volumes of critical information.
