• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

PreparedStatement - Performance

 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Say, I want to insert a row with two values into the database using a PreparedStatement. If I'm sure that one of the value is always going to be the same, I have 2 options






Is there a performance difference between these two approaches? My friend says that PreparedStatement works better, if and only if all the values are bind at the runtime. Even if we fail to specify a single value as a bind variable, it will work as if it is a normal Statement.

I find it difficult to buy this argument. Will it really make a difference? If yes, why should it be?

I thought I will get some comments from the experts here before I try to measure the performance and find it out myself.

Any comments?
 
Ranch Hand
Posts: 232
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Clarice Doe:

I find it difficult to buy this argument. Will it really make a difference? If yes, why should it be?

I thought I will get some comments from the experts here before I try to measure the performance and find it out myself.

Any comments?



what you friend says is true.

PreparedStatement understanding from ServerSide.com
 
Clarice Doe
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the link. But that doesn't actually answer the question.
The examples given in that link, doesn't use any constant values. The where clause keeps changing all the time.

Let me give another example on the same line as mentioned in the link

Does code A perform better than code B or not.

Code A:


Code B:


I will try it and will let you know. But meanwhile, any guesses?
[ October 04, 2005: Message edited by: Clarice Doe ]
 
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
Depending on whether the database supports PreparedSataements, how intelligently it manages to optimise the queries and about 100 other things, 'B' will be faster than 'A' since what the database runs will be 'more optimised', but you may not be able to tell the difference. The difference may be so small that something like windows paging may cause your results to be invalidated. My advice: don't worry about it.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I agree with David that the performance impact is too small to worry about. You may have some clarity considerations though.

The driver sees if the SQL string is the same. So if you substitute in different values, the SQL is different and the statement will not be prepared. This is the unbounded value your friend was talking about. if the value is always the same, the SQL is the same and the statement is prepared once.
 
Sripathi Krishnamurthy
Ranch Hand
Posts: 232
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by David O'Meara:
My advice: don't worry about it.



suppose I am doing 10 million updates per day. I do the update evreyday.
I still dont have to worry about it?
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You'll only know if you are in that situation. I would suspect such a large amount of database activety will be influenced more by the setup of the database(s), both physical and tuning, than by how you have written your PreparedStatements. But then, I am not a DBA so this is only a hunch.
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Sripathi Krishnamurthy:


suppose I am doing 10 million updates per day. I do the update evreyday.
I still dont have to worry about it?


It's still a trivial difference compared to the network traffic and database activity. If you were doing 10 million updates, you would want to first find the bottleneck. It is unlikely to be this.
 
it's a teeny, tiny, wafer thin ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic