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

 

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

posted @ 2014-05-15 14:55  princessd8251  阅读(234)  评论(0)    收藏  举报