When using the latest jdbc driver for SQL Server 2005/2008 how do prepared statements, views, and stored procedures compare regarding performance?

Question

When using the latest Microsoft jdbc driver for SQL Server 2005/2008 how do prepared statements, views, and stored procedures compare regarding performance?

If I have a plain old select statement with some dynamic where clauses will I see benefits from moving from straight SQL in a prepared statement to a view or even stored procedure?

To be more specific, what about something like this:

select foo.name, bar.name, baz.name, belch.burp
from foo 
inner join bar on foo.id=bar.fooID
inner join baz on bar.id=baz.barID
inner join belch on baz.id = belch.bazID
where foo.name like '%<USERINPUT>%' and bar.name like '%<USERINPUT>%'

Answer 1

depends on the sql and your application. i don't believe there's a blanket answer, and it probably doesn't depend on the driver.

if you have a choice between writing a query that brings a lot of data back to the middle tier to do an operation that the database can easily do, i'd recommend letting the database do the calculation and simply returning the result.

Answer 2

You are not likely to see a significant performance difference due to the cached execution plans for SPs, as execution plans are also cached in SQL Server 2005 and up even for ad hoc SQL.

In the case where parameter sniffing can adversely affect performance due to erroneous cardinality estimates for certain values of parameters, you can use WITH RECOMPILE indicators.

The benefits of views and stored procedures are going to be in the areas of security and maintenance, where the benefits are probably too numerous to exhaustively cover here but include abilities to:

Restrict protected data from being read from SELECT statements without having to assign individual column permissions on underlying tables.

Re-use parameterized SPs from other SQL code and multiple places within the application.

Instrument, log, profile and tune the application SPs as named database interface components of your system without affecting or re-deploying the application code.

Refactor the database without affecting or re-deploying the application code.

Provide an abstraction layer and interface contract with the database which gives good visibility into the database services required and provided by the system, which may include the possibility of automatic generation of metadata about the interface, a separate layer which can have automatic tests, and which can also be used as a point of demarcation for backend portability.

Answer 3

You will never get good performance out of a statement like that no matter what way you present it as a like statement with a wildcard as the first character automatically means the indexes are not used for that field. Frankly we have always insisted our users must type at least the first character to do a search. If your data is such that they won't know the first character then you probably have a bad design and a normalization problem (storing multiple bits of information in one field). There is no good fix for this short of redesigning the databse.