Technical: Microsoft – SQL Server – Query – Like Clause – Optimization – Using Prepared Statements
SQL Server Like Clause – Another path to helping SQL Server use our Index.
- Microsoft – SQL Server – Query Like
- Microsoft – SQL Server – Query Like Clause Using SQLCmd
- Microsoft -SQL Server – Using Query Hint
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
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 ;
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.
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.