Get Better Performance Using New T-SQL Functions
Author: Rajnikant Tandel | | February 14, 2019
I did a lot of programming in the beginning of my carrier and always preferred encapsulating code wherever possible. For instance, whenever there is a requirement to write the SQL query to concatenate string values by a separator within a group, I’ve always favored user defined function.
Though user defined function works fine in this kind of requirement, it sometimes has a negative impact on performance. Thankfully, SQL Server 2016 and 2017 came up with several new T-SQL functions.
With the previous versions of SQL Server, developers had to write lengthy and complex logic for the string manipulations. This article illustrates the basic functionality of new T-SQL function STRING_AGG and demonstrates how this new function can be used to concatenate the string values and get better performance over user defined functions or any other alternatives.
STRING_AGG:
The numeric aggregate functions like SUM, AVG, COUNT, MIN, MAX have been in use since ANSI SQL came in. Like the numeric aggregate functions, STRING_AGG function concatenates string values and places separators between them. It also allows you to organize the data within a group.
Example:
| CREATETABLE dbo.category(
category_id intIDENTITY(1,1)NOTNULL, category_group_code tinyint, category_desc varchar(20))
insert dbo.category(category_group_code, category_desc)select 1,’Travel’ insert dbo.category(category_group_code, category_desc)select 1,’Meals’ insert dbo.category(category_group_code, category_desc)select 1,’Lodging’ insert dbo.category(category_group_code, category_desc)select 2,’Entertainment’ insert dbo.category(category_group_code, category_desc)select 2,’Clothing’ insert dbo.category(category_group_code, category_desc)select 2,’Electronics’ insert dbo.category(category_group_code, category_desc)select 2,’Communication’ insert dbo.category(category_group_code, category_desc)select 2,’Groceries’ insert dbo.category(category_group_code, category_desc)select 2,’Home Supplies’ insert dbo.category(category_group_code, category_desc)select 2,’Misc’
select category_group_code as [Group],STRING_AGG(category_desc,’, ‘)withingroup (orderby category_desc) Categories from category groupby category_group_code
Output:
|
Let’s see how this function can be used to improve the query performance. I’m going to demonstrate examples of using this function over two old techniques to show the performance differences.
The following T-SQL script will create and populate two tables, Employee and Department.
| CREATETABLE dbo.Employee(
EmpId intprimarykey, EmpName varchar(50), DeptId smallint)
CREATETABLE dbo.Department( DeptId smallintprimarykey, DeptName varchar(50))
CreateIndex IX_DeptId on Employee(DeptId)
SETNOCOUNTON DECLARE @cnt INT SET @cnt = 1 DECLARE @rnd FLOAT, @EmpName varchar(50), @DeptName varchar(50) WHILE @cnt <= 1000000 BEGIN select @rnd =ROUND((RAND()* 10000),2) select @EmpName =’Emp’+trim(str(@cnt)) INSERTINTO dbo.Employee(EmpId, EmpName, DeptId)VALUES (@cnt, @EmpName, @rnd) if @cnt <= 10000 begin select @DeptName =’Department’+trim(str(@cnt)) insertinto Department(DeptId, DeptName)values (@cnt, @DeptName) end SET @cnt = @cnt + 1 END
|
Let’s write a query to get all employee names separated by commas for each department.
For this kind of requirement, the usual approach is to write a user defined function like below to get all employees separated by commas for a department. I’ve passed DeptId as a parameter in UDF and the query in the UDF concatenates all employee names separated by commas for that department. Here, COALESCE function has been used to concatenate employee names.
| — create user defined function
createoralterfunction dbo.udf_comma_delimited(@DeptId int) returnsvarchar(8000) as begin declare @return_values varchar(8000) SELECT @return_values =COALESCE(@return_values +’, ‘,”)+ EmpName FROM Employee WHERE DeptId = @DeptId orderby EmpName return @return_values end go
dbcc freeproccache dbcc dropcleanbuffers withno_infomsgs; go — Passing DeptId in user defined function to get all employee names separated by comma for the DeptId select DeptId, dbo.udf_comma_delimited(DeptId) Employees from Department orderby DeptId
Statistics Time: SQL Server Execution Times: CPU time = 4468 ms, elapsed time = 4850 ms.
|
The above query with the user defined function takes 5 seconds. Several times, we see that user defined function is the culprit and could be the reason for the slowness of the query. To eliminate the user defined function from the query, let’s try another popular approach of using FOR XML query to concatenate the string with a separator.
| select d.DeptId,
(selectstuff((select’, ‘+ EmpName as [text()] from Employee where DeptId = d.DeptId orderby EmpName forxmlpath(”)),1,1,”) ) Employees from Department d orderby d.DeptId
Statistics Time: SQL Server Execution Times: CPU time = 4984 ms, elapsed time = 5341 ms.
|
The time taken by both the queries (using UDF or FOR XML) is around five seconds.
Let’s try the STRING_AGG function now.
| select DeptId,string_agg(EmpName,’, ‘)withingroup (orderby EmpName) Employees
from Employee groupby DeptId orderby DeptId
Statistics Time: SQL Server Execution Times: CPU time = 4375 ms, elapsed time = 1030 ms.
|
The query with the STRING_AGG takes 1 second compared to 5 seconds taken by other two approaches.
In the three different scenarios tested, STRING_AGG function performed significantly better than the two other older methods. Thus, it is advisable to replace old techniques of user defined function or FOR XML method with the new STRING_AGG function.
Want to learn more performance tuning techniques? Download the slide from the “Optimize SQL Server: Query Tuning Techniques” presentation.
Blog Author
Related Posts
How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified
The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.
Data Types: The Importance of Choosing the Correct Data Type
Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.
How to Recover a Table from an Oracle 12c RMAN Backup
Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.