• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

PreparedStatement - Performance

 
Clarice Doe
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • 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?
 
Sripathi Krishnamurthy
Ranch Hand
Posts: 232
  • Mark post as helpful
  • send pies
  • 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
  • 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 ]
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • 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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34863
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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
  • 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?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • 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
Marshal
Posts: 34863
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic