When should use Statement over PreparedStatement

[Tejas Jain]

Does PreparedStatement always give better performance than Statement?

[Jeanne Boyarsky]

Originally posted by Todd Jain:
Does PreparedStatement always give better performance than Statement?

In the long run, yes. If you are only running it once, no - but then it doesn't matter because you are only running it once.

In practice you should always start with a PreparedStatement and only downgrade to a Statement if you have proven a performance problem.

[David O'Meara]

PreparedStatements also protect against SQL injection and promote cleaner code. There is a (reasonably rare) situation where a specific Statement can probably out-perform a generic PreparedStatement, but if you ever find out what it is and find find it makes a measurable difference to your app, I'll give you a dollar

[David O'Meara]

Actually I won't give you a dollar

[Glen Cai]

When you have a table that contains a millenium rows of data and you have a Index set up for the table, your PreparedStatement can not take the advantage of the Index for queries. Statement will out-perform PreparedStatement.

SQL Injection should be handled in Business tier before reaching JDBC calls...

[Jeanne Boyarsky]

Originally posted by Glen Cai:
When you have a table that contains a millenium rows of data and you have a Index set up for the table, your PreparedStatement can not take the advantage of the Index for queries.

I disagree. I've seen PreparedStatements use indexes on tables with hundreds of thousands of rows. The column names are defined at the time a PreparedStatement is compiled. This makes it available for the execution plan.

SQL Injection should be handled in Business tier before reaching JDBC calls...

According to OWASP, parametrized queries should
be used for this very reason.

[Rene Larsen]

In some - if not all - databases, the PreparedStatement's "execution plan" is stored/cached, and therefore each time this PreparedStatement is executed the database doesn't need to use time for calculation of the best "execution plan" - it has it already.

With a normal Statement the database has to find the best "execution plan" each time.

[Glen Cai]

Try the SQL command Explain Plan on the Statement and PreparedStatement that do the same job. You may find the execution plan for PreparedStatement is scanning the whole table while the one for Statement uses Index.

[Rene Larsen]

The following say it all - you need a certain amount of 'statements' before the PreparedStatement has an advantage.

So for the 'normal' development use a Statement is the right on - but in a production environment a PreparedStatement is the right on.

Statement Versus PreparedStatement

There's a popular belief that using a PreparedStatement object is faster than using a Statement object. After all, a prepared statement has to verify its metadata against the database only once, while a statement has to do it every time. So how could it be any other way? Well, the truth of the matter is that it takes about 65 iterations of a prepared statement before its total time for execution catches up with a statement. This has performance implications for your application, and exploring these issues is what this section is all about.

When it comes to which SQL statement object performs better under typical use, a Statement or a PreparedStatement, the truth is that the Statement object yields the best performance. When you consider how SQL statements are typically used in an application--1 or 2 here, maybe 10-20 (rarely more) per transaction--you realize that a Statement object will perform them in less time than a PreparedStatement object. In the next two sections, we'll look at this performance issue with respect to both the OCI driver and the Thin driver.

Java Programming with Oracle JDBC

[Paul Clapham]

Note that the book you linked to was published in 2001. Performance advice written eight years ago is very likely to be obsolete.

Anyway the better reasons for using PreparedStatement (avoidance of escaping issues, protection against SQL injection) have already been discussed in this thread.

And using Statement in development but rewriting to use PreparedStatement for production is just a bad idea. (But maybe you didn't mean that and I misinterpreted you.) You should always develop using the code that you are going to put into production.

[Rene Larsen]

I always use PreparedStatement in both development and production.

What I ment was that if you only have a few statements, then it seams to be faster using a Statement rather than using a PreparedStatement.

I am not the one that one need to convince about the use of PreparedStatement rather than Statement.
Personally I can not see what a Statement is good for - other then discussing the different between the two.

[Paul Clapham]

Yes, I thought I must be misinterpreting you.

Sure, I can see that for the occasional quick and dirty one-time program you might use a Statement. But I don't think I would. Unless the query was a constant with no parameters, in which case I might.

[Vlad ee]

As Rene Larsen said

PreparedStatement's "execution plan" is stored/cached

Oracle tends to choose worst execution plan for "LIKE ?" prepared-statements, whenever the FIRST value of "?" passed to server
is in '%something' format. When next value of "?" is 'prefix%' - same ineffective execution plan is used due to caches.

Recommendations from benchmarks of PreparedStatement vs Statement are still valid for latest versions of Oracle DB.

[Joseph Smithern]

avoidance of escaping issues

This has been mentioned and I think it is a big plus to using PreparedStatement. Especially helpful with Oracle apostrophe escape and other issues. I cant see why someone would want to use Statement, unless they enjoy writing extra escape code in the database or client side or server side.
[ October 07, 2008: Message edited by: Joseph Smithern ]

[Prakash Pasumarthy]

No Doubt Prepared Statement has good performance/advantages than Statement Object.
when can we use of Statement Object ? or can we think like Statement Object as a deprecated method or API.
Is there any Rule like use prepared statements EVERYWHERE. Use STATEMENTS almost no where.