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:
link
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);
stmt.executeQuery();
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)