• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

When should use Statement over PreparedStatement

 
Tejas Jain
Ranch Hand
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does PreparedStatement always give better performance than Statement?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually I won't give you a dollar
 
Glen Cai
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1179
Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1179
Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21583
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1179
Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21583
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 89
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic