With the latest jdbc driver for SQL Server 2005/2008, how do prepared statements, views and stored procedures compare for performance?
With the latest jdbc driver from Microsoft for SQL Server 2005/2008, how do prepared statements, views, and stored procedures compare for performance?
If I have a simple old select statement with some dynamics, where in the articles will I see the benefits of going from straight SQL to prepared statement to view or even stored procedure?
To be more specific, 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>%'
source to share
You probably won't see a significant performance difference with SP caching plans, as execution plans are also cached in SQL Server 2005 and even special SQL.
In the case where the sniffing parameter can negatively affect performance due to erroneous power estimates for certain parameter values, you can use the RECOMPILE indicators.
The benefits of views and stored procedures will be in the security and maintenance areas, where the benefits are likely too numerous to be comprehensively covered here, but include the possibilities:
Restrict protected data from being read from SELECT statements without having to assign separate column permissions to the underlying tables.
Reuse parameterized SPs from different SQL code and multiple locations in your application.
Tool, log, profile, and configure application SPs as named components of your system's database interface without affecting or redeploying application code.
Refactor the database without changing or redeploying application code.
Provide a layer of abstraction and a contract for the interface with the database that gives good visibility into the database services that are required and provided by the system, which may include the ability to automatically generate metadata about the interface, a separate layer that can have automated tests and that can also be used as a demarcation point for portable portability.
source to share
You will never get good performance from such a statement, no matter how you represent it as a wildcard character, since the first character automatically means that no indices are used for that field. To be honest, we have always insisted that our users have to type at least the first character to search. If your data is such that it won't know the first character, you are likely to have poor design and a normalization problem (storing multiple bits of information in the same field). There is nothing good about redesigning the database.
source to share
depends on sql and your application. I don't believe there is a complete answer and this is probably driver independent.
if you have a choice between writing a query that returns a lot of data back to the middle tier to perform an operation that the database can easily perform, then I would recommend that the database does the computation and just return the result.
source to share