• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

Statement vs. PreparedStatement

 
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 67637
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Where all the women are strong, all the men are good looking and all the tiny ads are above average:
ScroogeXHTML - fast RTF to HTML and XHTML conversion
https://coderanch.com/t/749563/ScroogeXHTML-fast-RTF-HTML-XHTML
reply
    Bookmark Topic Watch Topic
  • New Topic