Once again I’m going to refer to previous posts here as this is actually an extension of my previous two posts.
This was something I came across recently when working on a tuning project… the original code was very slow and this was quickly traced to a good old function.
Anyone who’s read my posts before probably knows I generally hate functions, and this situation hasn’t helped that. But at least there was a way out of this one, and one which reduced the duration and reads drastically in the process.
Obviously I can’t use the exact example I was dealing with so I’m going to invent a function to work with. It’s basically a very weird username generator.
I’m going to use AdventureWorks2012 and for each person registered in the system I’m going to generate them a username for a new system based on their first name, first 3 characters of their last name, then (bear with me) the last 5 digits of the product number ordered where the salesOrderDetailID equals their businessEntityID, and the floored unitPrice of said salesOrderDetailID.
I did tell you it was weird, but I wanted something that would be an in-line function yet would access multiple sets of data and involve temp tables etc (not strictly necessary, but I’m trying to over complicate it on purpose).
So here’s the function I came up with:
use AdventureWorks2012
go
if object_id('userNameGenerate') is not null drop function userNameGenerate
go
create function userNameGenerate(@personID int)
returns varchar(50)
begin
return
(
select p.FirstName + left(p.LastName, 3) +
right(pp.ProductNumber, 5) +
convert(varchar, convert(int, floor(d.unitPrice)))
from Person.Person p
join sales.SalesOrderDetail d
on p.BusinessEntityID = d.SalesOrderDetailID
join Production.Product pp
on d.ProductID = pp.ProductID
where BusinessEntityID = @personID
)
end
go
Let’s run the following query just to see our output and the duration and reads that this function causes:
select BusinessEntityID, firstName, lastName, dbo.userNameGenerate(businessEntityID)
from person.person
go
So what’s the alternative?
Well, firstly this is effectively running on a row by row basis and SQL Server hates that. Therefore we need to change the code to run on a set basis. BUT we need this code to be centralized and re-usable, like a function, so that editing would be simple and only need doing once.
This could be done by placing the code into a table valued function, but those are just horrific (Google TVFs are evil and you’ll see plenty of hits… likely including some from this website).
Also you have the problem that there are restrictions on what can and can’t go into a TVF and with us trying to re-code a little to make the process more set based, this could get messy. Not in this example, but you can easily imagine other, more complex scenarios making this impossible.
So what’s the answer? Well… Use a stored procedure with a table type parameter.
We could re-write the function into the following set based stored procedure:
use AdventureWorks2012
go
if object_id('userNameGenerate') is not null drop procedure userNameGenerate
go
if exists
(
select name
from sys.table_types
where name = 'userListType'
)
begin
drop type userListType
end
create type userListType as table
(
userID varchar(100)
)
go
create procedure userNameGenerate
(
@userList userListType readonly
)
as
select p.FirstName + left(p.LastName, 3) +
right(pp.ProductNumber, 5) +
convert(varchar, convert(int, floor(d.unitPrice)))
from Person.Person p
join @userList u
on p.BusinessEntityID = u.userID
join sales.SalesOrderDetail d
on p.BusinessEntityID = d.SalesOrderDetailID
join Production.Product pp
on d.ProductID = pp.ProductID
go
And now we can call it as follows:
declare @users userListType
insert into @users
select BusinessEntityID
from person.Person
exec userNameGenerate @users
go
We get exactly the same output, but let’s have a look at the duration and reads now?
Massively improved. And now we have re-usable, centralized code which is fast, efficient, and, unlike a function, will not prohibit parallelism if required.
I would definitely recommend that you look this trying this out in your own environments if you can.