SQL Server uses the following mechanisms for caching queries and procedures:
ad-hoc batch caching - saves query plans automatically
The text of the subsequent batches must exactly match the first batch (exact textual matches - data and data type must be the same)
SELECT * FROM Products WHERE unitprice = $12.5 SELECT * FROM Products WHERE unitprice = $12.5 -- the same query plan as the previous batch SELECT * FROM Products WHERE unitprice = 12.5 -- another query plan
auto-parametrization - saves query plans automatically SQL Server tries to create a template on the basis of SQL statements, where constants are parameters
-- The following queries will have the same query plan SELECT * FROM Products WHERE ProductID = 7890 SELECT * FROM Products WHERE ProductID = 1234 SELECT * FROM Products WHERE ProductID = 7889
sp_executesql - SQL Server uses ad-hoc batch caching (requires persistent object management)
-- The following queries will have the same query plan EXEC sp_execute ('SELECT * FROM Members WHERE id=@v1', '@v1 INT', @v1 = 700) EXEC sp_execute ('SELECT * FROM Members WHERE id=@v1', '@v1 INT', @v1 = 231) EXEC sp_execute ('SELECT * FROM Members WHERE id=@v1', '@v1 INT', @v1 = 897)
sp_prepare and sp_execute
sp_executesql - requires the full text of the batch for each execution
sp_prepare / sp_execute - require only parameter passing
Stored procedures sp_executesql and sp_prepare/sp_execute are typically called from client applications.
PARSE
RESOLVE
OPTIMIZE
COMPILE
EXECUTE
Performance implications of using NOT IN, EXISTS, and LEFT OUTER JOIN
It seems that the NOT IN statement offers really poor performance. That's why EXISTS or LEFT OUTER JOIN should be used instead. There is a debate which is better – EXISTS or LEFT OUTER JOIN.
NOT EXISTS should be used because of the following reasons: