Technical: Microsoft – SQL Server – Query – Like Clause – Optimization – Using Prepared Statements

Technical: Microsoft – SQL Server – Query – Like Clause  – Optimization – Using Prepared Statements

Introduction

SQL Server Like Clause – Another path to helping SQL Server use our Index.

Background

What are Prepared Statements?

Prepared Statements are SQL Code where beforehand you specify the SQL Statement, but rather than specify the actual argument values, you replace them with place holders.

Later on during execution, you just send in the actual values.

In Transact SQL, these two steps are combined into one, but because the @stmt and @params argument stay the same, SQL Server is able to re-use the cached statement.

Prepared Statement (sp_executesql) – Syntax

 
exec sp_executesql
     @stmt = @statement
    ,@params = @parameterDefinition
    ,@param1 = @paramValue1
    ,@param2 = @paramValue2
    ,@paramN = @paramValueN

Prepared Statement (sp_executesql) – Example

use tempdb
go

set nocount on
go

if object_id('dbo.newscaster') is null
begin

	create table dbo.newscaster
	(
	          id         bigint not null identity(1,1)
		, lastname   nvarchar(80)
		, firstname  nvarchar(80)
		, network    nvarchar(30)
	)	

end

declare @statement nvarchar(4000)
declare @parameterDefinition nvarchar(4000)
declare @paramValueLastName nvarchar(80)
declare @paramValueFirstName nvarchar(80)

set @statement =    'insert into dbo.newscaster '
                  + ' ( lastname, firstname, network) '
                  + ' values '
                  + ' (@lname, @fname, @network) '

--print @statement

set @parameterDefinition = 
    '@lname varchar(40), @fname varchar(40), @network varchar(30)'

exec sp_executesql
     @stmt = @statement
    ,@params = @parameterDefinition
    ,@fname = 'jennings'
    ,@lname = 'peter'
    ,@network = 'abc'
;

 exec sp_executesql
     @stmt = @statement
    ,@params = @parameterDefinition
    ,@fname = 'koppel'
    ,@lname = 'ted'
    ,@network = 'ABC'

;

select *
from   dbo.newscaster

SQL

Here is a quick use-case for Prepared Statements (sp_executesql):



declare  @zipcode varchar(10) = '191818%'

select *
from   dbo.student
where  zipcode like '191818%'

select *
from   dbo.student
where  zipcode like @zipcode

--DECLARE @zipcode varchar(10);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string */
SET @SQLString = N'select * from dbo.student where  zipcode like @zipcode'
SET @ParmDefinition = N'@zipcode varchar(10)';
SET @zipcode = '191818%';

EXECUTE sp_executesql
		   @SQLString
		 , @ParmDefinition
	 	 , @zipcode = @zipcode
	 ;

Execution Plan

sp_executesql

Statistics I/O



Table 'student'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'student'. Scan count 5, logical reads 19912, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'student'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Statistics

Query Type Degree of Parallelism Estimated # of Rows Estimated Subtree Cost Scan Count Logical Reads
Hard coded values 1 75.8797 0.249319 1 22
Variable/Argument 4 96680.3 20.3826 5 19912
Prepared Statements 1 75.8964 0.249373 1 22

Observation

Using so called prepared statements help, as well.

And, this opens up a bit of other thoughts.

The like clause throws everything away.  The Estimated # of rows is 96680.3.  Its estimated Subtree cost is 20.3826 and thus it proceeds down the Parallelism track thinking that it has a lot of work to do.

In reality it has quite a bit of Work.  

Parallelism is often taken to tackle queries with High IO requirements.  

As it is using the primary key; which is unhelpful in this case; since it is not doing an Index Seek, but a “Clustered Index Scan”.  

The Clustered Index Scan is almost as bad as a Table Scan.

It appears that Stored Procedures and Prepared Statements proceed down a different and more rigorous optimization path as compared to table value functions, views, and Ad-hoc queries.

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s