SQL Server Execution Plan – Understanding Prepared Statements

execution-planprepared-statementsql server

I have a stored procedure in my database. As per my knowledge it's pre-compiled subroutine.

Question 1: If I execute following queries from Hibernate, will database create a separate execution plan for each query? In which case SQL Server update/change stored procedure Execution Plan?

EXECUTE myProcedure 'vicky.thakor', 'Routine Check Up'

and

EXECUTE myProcedure 'chirag.thakor', 'Routine Check Up'

Question 2: This is bit of programming question but you guys may explain me bit of it. In Java there is something called PreparedStatement. PreparedStatement is similar to pre-compiled query.

PreparedStatement generates query like following and put value in place of ?.

SELECT * FROM user_master WHERE user_name = ?

But database will receive the final query like 

SELECT * FROM user_master WHERE user_name = 'vicky.thakor'

then how database help to get best performance when using PreparedStatement.

Best Answer

First of all: stored procedures in SQL Server are NOT "pre-compiled" or anything.

Just like an ad-hoc SQL query, a stored procedure is analyzed when it's first used, an execution plan is determined and cached.

When the stored procedure is executed again, then that pre-existing execution plan is reused. Same applies to a properly parametrized SQL ad-hoc query.

The point is: as long as the actual SQL statement is IDENTICAL (down to the last comma or space), then a possibly pre-existing, cached execution plan is reused and the whole step of determining an execution plan can be skipped. If the SQL statement text varies - even by a single space - the whole process of analyzing and coming up with an execution plan for that query is run again.

I don't know about how Java handles it's prepared statements, but in C# using ADO.NET, when you run a query something like 

SELECT * FROM user_master WHERE user_name = @userName

and you supply a value for @userName (e.g. 'vicky.thakor'), then the SQL Server will NOT received what you seem to think it will receive - the parameter is NOT replaced in the query text - instead, SQL Server will execute this as

EXEC sp_executesql @stmt, N'@userName', @userName = 'vicki.thakor'

SQL Server will receive a parametrized query and a list of parameters and their values.

Therefore, if you call this same query again, with a different value for @userName, the actual SQL statement text is identical and the cached execution plan is reused - only the value of the parameter has changed.

Related Question