Yesterday in the Changing exec to sp_executesql doesn’t provide any benefit if you are not using parameters correctly post I showed you that sp_executesql is better than exec because you get plan reuse and the procedure cache doesn’t get bloated.
Today I will show you that sp_executesql is better than exec or ad hoc queries when you deal with conversions in execution plans
First create this table and populate it with some data
create table TestPerf( id int not null,
SomeCol1 nvarchar(20),
SomeValue smallint)
insert TestPerf values(1,'Test',1)
insert TestPerf values(2,'Aest',257)
insert TestPerf values(3,'Best',258)
insert TestPerf values(4,'Cest',259)
insert TestPerf values(5,'Dest',251)
insert TestPerf values(6,'Eest',252)
insert TestPerf values(7,'Fest',253)
insert TestPerf values(8,'Gest',254)
insert TestPerf values(9,'Hest',255)
Take a look this query
select * from TestPerf
where SomeCol1 = 'Test'
and SomeValue = 1
Run the query
Here is the execution plan
_
|–Table Scan(OBJECT:([testpage].[dbo].[TestPerf]),
WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=CONVERT_IMPLICIT(smallint,[@2],0)
AND [testpage].[dbo].[TestPerf].[SomeCol1]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)))_
As you can see the value 1 needs to be converted to smallint and ‘Test’ Needs to be converted to nvarchar. You can actually see what the value is converted to by using the SQL_VARIANT_PROPERTY function.
When you run the following query
SELECT SQL_VARIANT_PROPERTY(1, 'BaseType') AS [Base Type],
SQL_VARIANT_PROPERTY(1, 'Precision') AS [PRECISION],
SQL_VARIANT_PROPERTY(1, 'Scale') AS [Scale]
union all
SELECT SQL_VARIANT_PROPERTY('Test', 'BaseType') AS [Base Type],
SQL_VARIANT_PROPERTY('Test', 'Precision') AS [PRECISION],
SQL_VARIANT_PROPERTY('Test', 'Scale') AS [Scale]
you get this as output
Base Type PRECISION Scale
int 10 0
varchar 0 0
so the value 1 becomes an int and ‘test’ is varchar
Exec with dynamic SQL is not any better than ad hoc of course since the same query gets generated as with the ad hoc query
Here is such a query
declare @SomeValue smallint
declare @SomeCol1 nvarchar(20)
select @SomeValue = 1,@SomeCol1 = 'Test'
declare @SQL nvarchar(1000)
select @SQL = 'select * from dbo.TestPerf
where SomeCol1 = ''' + @SomeCol1 +'''
and SomeValue = ' + convert(nvarchar(10),@SomeValue)
exec (@SQL)
Here is the execution plan
|--Table Scan(OBJECT:([testpage].[dbo].[TestPerf]),
WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=CONVERT_IMPLICIT(smallint,[@2],0)
AND [testpage].[dbo].[TestPerf].[SomeCol1]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)))
Ideally you want a query with parameters; below is the quey
declare @SomeValue smallint
declare @SomeCol1 nvarchar(20)
select @SomeValue = 1,@SomeCol1 = 'Test'
select * from dbo.TestPerf
where SomeCol1 = @SomeCol1
and SomeValue = @SomeValue
Here is the execution plan
|--Table Scan(OBJECT:([testpage].[dbo].[TestPerf]), WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=[@SomeValue] AND [testpage].[dbo].[TestPerf].[SomeCol1]=[@SomeCol1]))
As you can see no conversions here
Now let’s take a look at how we can take the dynamic query from before and use sp_executesql to get rid of conversions
Run this query
declare @SomeValue smallint
declare @SomeCol1 nvarchar(20)
select @SomeValue = 1,@SomeCol1 = 'Test'
declare @SQL nvarchar(1000)
select @SQL = 'select * from dbo.TestPerf
where SomeCol1 = @InnerSomeCol1
and SomeValue = @InnerSomeValue'
declare @ParmDefinition nvarchar(500)
SET @ParmDefinition = N'@InnerSomeValue smallint ,@InnerSomeCol1 nvarchar(20)'
exec sp_executesql @SQL,@ParmDefinition,
@InnerSomeValue = @SomeValue,
@InnerSomeCol1 = @SomeCol1
Here is the execution plan
As you can see there were no conversions.
The thing that is important with a query like that is that the parameters inside the dynamic sql match the data types of the columns.
So for this part of the query
select @SQL = 'select * from dbo.TestPerf where SomeCol1 = @InnerSomeCol1 and SomeValue = @InnerSomeValue'
The SomeCol1 column and the @InnerSomeCol1 param/variable have to be of the same datatype,
the SomeValue column and the @InnerSomeValue param/variable also have to be of the same datatype in order to prevent conversions
Let’s look at something else
The query from before with parameters
declare @SomeValue smallint
declare @SomeCol1 nvarchar(20)
select @SomeValue = 1,@SomeCol1 = 'Test'
select * from dbo.TestPerf
where SomeCol1 = @SomeCol1
and SomeValue = @SomeValue
Here is the execution plan
|--Table Scan(OBJECT:([testpage].[dbo].[TestPerf]),
WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=[@SomeValue]
AND [testpage].[dbo].[TestPerf].[SomeCol1]=[@SomeCol1]))
what happens if you change the params to this
declare @SomeValue int
declare @SomeCol1 varchar(20)
select @SomeValue = 1,@SomeCol1 = 'Test'
select * from dbo.TestPerf
where SomeCol1 = @SomeCol1
and SomeValue = @SomeValue
Here is the execution plan
|--Table Scan(OBJECT:([testpage].[dbo].[TestPerf]),
WHERE:([testpage].[dbo].[TestPerf].[SomeValue]=[@SomeValue]
AND [testpage].[dbo].[TestPerf].[SomeCol1]=CONVERT_IMPLICIT(nvarchar(20),[@SomeCol1],0)))
We still get a conversion on the nvarchar SomeCol1 column
What happens if we change the outer parameter data types in the following dynamic query?
declare @SomeValue smallint
declare @SomeCol1 nvarchar(20)
select @SomeValue = 1,@SomeCol1 = 'Test'
declare @SQL nvarchar(1000)
select @SQL = 'select * from dbo.TestPerf
where SomeCol1 = @InnerSomeCol1
and SomeValue = @InnerSomeValue'
declare @ParmDefinition nvarchar(500)
SET @ParmDefinition = N'@InnerSomeValue smallint ,@InnerSomeCol1 nvarchar(20)'
exec sp_executesql @SQL,@ParmDefinition,
@InnerSomeValue = @SomeValue,
@InnerSomeCol1 = @SomeCol1
Here is the execution plan
Now we will change the parameters from smallint and nvarchar to int and varchar in the query below. This won't make any difference in the execution plan because the parameters used in the plan are still of the correct datatype since the inner parameters are used not the outer ones!!
/*changed from
declare @SomeValue smallint
declare @SomeCol1 nvarchar(20)*/
declare @SomeValue int
declare @SomeCol1 varchar(20)
select @SomeValue = 1,@SomeCol1 = 'Test'
declare @SQL nvarchar(1000)
select @SQL = 'select * from dbo.TestPerf
where SomeCol1 = @InnerSomeCol1
and SomeValue = @InnerSomeValue'
declare @ParmDefinition nvarchar(500)
SET @ParmDefinition = N'@InnerSomeValue smallint ,@InnerSomeCol1 nvarchar(20)'
exec sp_executesql @SQL,@ParmDefinition,
@InnerSomeValue = @SomeValue,
@InnerSomeCol1 = @SomeCol1
Here is the execution plan
That wraps up this blog post
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
