-
Notifications
You must be signed in to change notification settings - Fork 32
Procedures using dynamic SQL without sp_executesql
From http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/avoid-conversions-in-execution-plans-by:
sp_executesql is better than exec or ad hoc queries when you deal with conversions in execution plans.
Example:
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 at this query:
select * from TestPerf
where SomeCol1 = 'Test'
and SomeValue = 1
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
If you look at the execution plan here, 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
Again, you get an execution plan with no conversions.
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
This produces another plan with no conversions.