String concatenation with max types stops plan caching
Erland Sommarskog (http://www.sommarskog.se/) was recently
investigating delimited strings and was finding odd behaviour, which he wanted
verifying. The behaviour was that he was getting recompiles with certain
functions he was using for string splitting.
I had a look and found exactly the same behaviour.
I started dissecting the code and found the issue was with string
concatenation and use of tables. These two sps identify the problem
create procedure usp_CollationBug
@param nvarchar(max)
as
SELECT top 1
@param +N',' AS output
FROM sys.objects
go
create procedure usp_CollationBugTest
as
dbcc
freeproccache
exec usp_CollationBug N'1,2,3'
SELECT o.name,*
FROM sys.dm_exec_cached_plans
cp
OUTER APPLY (sys.dm_exec_query_plan(cp.plan_handle) qp
LEFT
JOIN sys.objects o ON qp.objectid = o.object_id)
WHERE qp.dbid =
db_id()
and o.name = 'usp_CollationBug'
ORDER BY o.name
If you execute usp_CollationBugTest
then you will find that there is no record returned in the last query, i.e.
there isn't a cached query plan.
So what is causing it. Well if you take off the string concatenation (+ N',')
or the FROM sysobjects you will get a query plan cached
alter procedure usp_CollationBug
@param nvarchar(max)
as
SELECT
@param +N','
AS output
go
exec
usp_CollationBugTest
go
alter procedure usp_CollationBug
@param nvarchar(max)
as
SELECT
top 1 @param AS output
from
sys.objects
go
exec
usp_CollationBugTest
go
Both of these should return a cached plan.
So I thought about using a CTE, using CHAR to append the string, or
using varchar and not Nvarchar
alter procedure usp_CollationBug
@param nvarchar(max)
as
;with param as(select @param +
',' value)
SELECT
top 1 param.value AS
output
from
sys.objects
cross
join param
go
exec
usp_CollationBugTest
go
alter procedure usp_CollationBug
@param nvarchar(max)
as
SELECT
top 1 @param +
char(44) AS output
from
sys.objects
go
exec
usp_CollationBugTest
go
alter procedure
usp_CollationBug
@param varchar(max)
as
SELECT top 1 @param +',' AS
output
from sys.objects
go
exec usp_CollationBugTest
go
But unfortunately none of these work.
So far I've found two solutions, one is to force the collation of the
expression and the second is to not use MAX datatypes
alter procedure usp_CollationBug
@param nvarchar(max)
as
SELECT
top 1 @param +
N',' COLLATE
SQL_Latin1_General_CP1_CI_AS AS output
from
sys.objects
go
exec
usp_CollationBugTest
go
alter procedure usp_CollationBug
@param nvarchar(4000)
as
SELECT
top 1 @param +
N',' COLLATE
SQL_Latin1_General_CP1_CI_AS AS output
from
sys.objects
go
exec
usp_CollationBugTest
go
I thought about this more and thought about using STUFF, I came up with the
following solution that does result in a cached plan
alter procedure usp_CollationBug
@param nvarchar(max)
as
SELECT
top 1 stuff(@param,LEN(@param),1, right(@param,1) + N',') AS output
from
sys.objects
go
exec
usp_CollationBugTest
go
I then thought about my post when
is varchar(max) not a varchar(max) This highlights that you have
to make sure your input variables are of a max type to get a max type
out. So I tried the following, and found it worked.
alter procedure
usp_CollationBug
@param nvarchar(max)
as
SELECT
top 1 @param +
cast(N',' as nvarchar(max)) AS
output
from
sys.objects
go
exec usp_CollationBugTest
go
Many people use MAX data types as an easy opt out, DONT as it will lead to
this problem if you are concatenating
The forcing of the collation should not be a problem as you have to specify
it for your server, database and columns
So now you know the solution, why is it important. Query plans are essential
for the performance of queries that are regularly executed, to avoid the compile
time, this is especially true in OLTP systems.
If you do have a query that is executed 100s of times per second , avoiding
compilation will more than likely improve performance
-
