Select Page

Get Better Performance Using New T-SQL Functions

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:

Group Categories
1 Lodging, Meals, Travel
2 Clothing, Communication, Electronics, Entertainment, Groceries, Home Supplies, Misc.

 

 

 

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.

 

 

 

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017
sharepoint ideas

9 Awesome Things You Can Do with SharePoint

This blog post discusses out-of-the-box uses for SharePoint that you can execute in just a few clicks without using any code.

Amol Gharat | May 23, 2017

Tips for Upgrading From SQL 2008 to 2012 or 2014

It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.

Andy McDermid | April 8, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS