SQLTeam.com | Weblogs | Forums

What is sp_prepare for?


We had a 3rd party app here calling sp_prepexec. And SQL Server 2008 was choosing a poor execution path.

I understand this combines sp_prepare and sp_execute. I'm not familiar with sp_prepare - MSDN says: Prepares a parameterized Transact-SQL statement. Not exactly helpful.

What is the purpose of sp_prepare/when would we want to use it?


I don't think (maybe wrong ...) that I've ever seen an APP use sp_prepare, but rather always the APP's "language/Dev Environment"

The principle is that sp_prepare returns a handle for the query, and then that handle can be reused, but what I see (e.g. in SQL Profiler) is some SQL being passed by sp_prepare then the Handle ID used ONCE, and never used again ... so I figure its some way for the App/Dev Library to parse or parametrise a query and then run it with some parameters but not actually reuse the handle in practice. Every time I see that I wonder what the benefit is (given that the handle does not appear to be reused) and whether actually just squirting the SQL at the server (ideally using sp_ExeuteSQL but even EXEC) would be better.


On Transact SQL language the sp_prepare is part of Database Engine Stored Procedures and prepares a parameterized statement and returns a statement handle for execution.


I read some things here:

The only thing I'm wondering. If you use sp_executesql, I thought SQL Server will reuse existing executions plans. If that's the case, then what is the point of doing prepare/executes?


I've often thought that. I see various APPs doing sp_prepare and kinda made the assumption that they must be doing so for legacy reasons. sp_ExecuteSQL would be a lot neater, I reckon. AMybe sp_prepare "fits" better with a Compiler / Interpreter parsing the original code?


Usually sp_prepare is used (internally) from client code, e.g., java code. If you have a java code segment such as this (copied from wiki)

java.sql.PreparedStatement stmt = connection.prepareStatement(
               "SELECT * FROM users WHERE USERNAME = ? AND ROOM = ?");
stmt.setString(1, username);
stmt.setInt(2, roomNumber);

That then internally calls sp_prepare. See here. Very rarely have I seen calls to sp_prepare in raw T-SQL code.


Seems to me the concept for this precedes SQL being able to cache the query plan (for a parametrised query). This is the Client APP storing a Handle for a specific parametrised query, to encourage it to be reused. Whereas in fact SQL, nowadays, is going to reuse the query plan for "SELECT * FROM users WHERE USERNAME = ? AND ROOM = ?" every time it sees it, from whichever Client - provided that the query plan is still in cache.

Thus I can't see any benefit for it, any more.

Given that Java originates from Oracle I'm curious to know if Oracle requires Clients to cache queries, to encourage / benefit from reuse, or does it, like MS SQL, cache query plans for previously-seen parametrised SQL statements and reuse them automatically, and without any specific Client code to assist in reuse (other than parametrising the query)