Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Statement vs. PreparedStatement

 
rasit fidanov
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have read about PreparedStatement and Statement's performances too.

and i wanted to see the difference byself.
So in oracle db i've created a table having 2 numbers and 1 varchar column.

And i ran an insert sql into test table in a loop of 5000 times.
and i measured the time with System.currentTimeMillis();

before and after the loop to demonstrate PreparedStatement had better performance.

I tested it several times.

At first Statement finished jobs with the 89 secs.

PreparedStatement finished with 49 seconds.

as i told i tested it several times.

then more and more Statement closed the difference of time
between PreparedStatement.

Can any body explain this stuation?

My friend That he doesnt understand any java but he knows oracle very well explained me that with "oracle forms" if an sql statement sent to db then it compiles it only for once and executes the compiled sql if asked for again...

Does oracle do some trick at db side to reduce the compilation time?

I wonder whether the execution sql sent is in same format and only taking the different paramaters, Oracle may understand it and pulls it from sql string and embeds to precompiled sql and executes it?

Can anybody explain me why Statement reduced the difference between PreparedStatement?
[ July 07, 2005: Message edited by: rasit fidanov ]
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When you inserted values, did you use a large variation or keep inserting the same values?

The reason I ask is that in general a PreparedStatement will outperform a Statement, but there are cases in which a Statement is faster, and you may have created this situation. The main advantage to a PreparedStatement is that it does not need to be compiled on the database every time, the database compiles it once and prepares itself as well as it can given that it doesn't know everything about the query. It may be possible to add more performance tweaks, but the database just can't be sure.

When it comes to a Statement with no unbound variables, the dtaabae is free to optimise to its full extent. The individual query will be faster, but the down side is that you need to do the database compilation all the time, and this is worse than the benefit of the faster query.

Unless the same Statement gets run over and over, in which case it will be cached too and behave like a better optimised PreparedStatement. In practice I've found the difference is not actually worth worrying about. Even if a PreparedStatement is 'no better than' a Statement, it's flexibility is much nicer.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65340
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Even if a PreparedStatement is 'no better than' a Statement, it's flexibility is much nicer.


Indeed! And also, read this.
 
Pawel Veselov
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I think that, to be fair, you need to measure prepared statement being prepared (prepareStatement()) and executed (executeUpdate()).

Oracle does have distinct server side operations for 'compile', 'execute' and 'fetch'. In case of prepared statement, the 'compile' portion is executed only once. You can set the trace on the session, and check out the log files, that should back up how you used prepared and regular statements, as trace will log the amount of times a query was compiled, executed and fetched.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic