JDBC: When Good Prepared Statements go Bad

I've been doing some performance comparisons of JDBC from Java with ADO.Net from Dot.net. One key thing that jumps out at me is JDBC tends to make more use of prepared statement, even to the point of abusing the poor thing. 

I see some mid-tier manufacturer making bland claims like "Our SQL is always perfectly optimised as its prepared". Oh very dear - wake up call - a Prepared statement like anything is something that is good in some scenarios and is bad in others, so using it for EVERTHING is folly.

ok, so what is a prepared statement in reality? Its a way for SQL Server to repeat the same SQL Statement multiple times without having to repeat the query plan and as importantly without having to send the statement  down for the second and subsequent iterations. To facilitate this internally the driver will we call the stored procedure "exec sp_prepexec" to prepare the statement and return a handle to the query and then for every execution of the query we call "exec sp_execute @handle" followed by the parameters of the statement (the data). You can actually do prepared statements manually by just calling "sp_prepexec" and "sp_unprepare" although these commands are largely undocumented.

From a performance point of view a prepared statement can get similar benefits as a stored procedure. This would be great, but the whole benefit of stored procedures isn't generally to do single atomic statements anyway!

The hidden cost of Prepared Statements

Not surprisingly the hidden cost of prepared statements versus stored procedures is the fact that they need to be prepared (doh!). From a SQL Server point of view we need to allocate a variable for every single parameter, figure out the cache plan, allocate a handle to the call, etc so the first call from SQL profiler will look something like this:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 nvarchar(4000),@P12 float,@P13 float,@P14 float,@P15 int',N'INSERT INTO Sales.SalesOrderHeader (OrderDate,DueDate,ShipDate,Status,OnlineOrderFlag,CustomerID,ContactID,TerritoryID,BillToAddressID,

ShipToAddressID,CreditCardID,CreditCardApprovalCode,SubTotal,TaxAmt,Freight,ShipMethodID)
VALUES (@P0,@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15)
                                                                                                                           select SCOPE_IDENTITY() AS GENERATED_KEYS',N'2001-07-01T00:00:00',N'2001-07-13T00:00:00',N'2001-07-08T00:00:00',5,0,676,378,5,985,985,16281,N'105041Vi84182',

24643.9362,1971.5148999999999,616.09839999999997,5
select @p1

when we are finished we need to close the pointer to the prepared statement using "exec sp_unprepare @handle"

Now I'm going to go out on a limb here and say that this is more expensive than just executing the statement in the normal way "INSERT INTO Table1 (C) VALUES (1)", for a single call. The real benefit is on multiple calls.

Think of a prepared statement as the milli-gun in doom, takes a bit longer to spin up but when it does happy days. Not the weapon of choice for every task!

What Does a Good use of a Prepared Statement Look like

To see if your application is making good use of prepared statement you can run SQL Profiler which is install with SQL Server performance Tools. Good use of a Prepared Statement from JDBC can be seen below. We prepare once and then reuse the statement object for subsequent calls.

What Does Bad use of a Prepared Statement look like

In this example we can see that the statement is prepared once per call to SQL Server resulting in more chatter to SQL Server an no real re-use of the prepared statement.

When to NOT use prepared statements

1) When you are closing the statement after execution.

2) When you are only executing the statement once.

In a lot of development houses it is considered good practice to develop coding standards that make methods "stateless", e.g. each method opens and closed the resources that it needs and keeps it own house clean.

This  renders the prepared statements useless as a tool to improve performance.

Like connection pooling we need to cache the statement object to get the benefit of re-using the prepared statement .