File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes PreparedStatement - Performance Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement - Performance" Watch "PreparedStatement - Performance" New topic
Author

PreparedStatement - Performance

Clarice Doe
Greenhorn

Joined: Dec 24, 2004
Posts: 21
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

Joined: Mar 07, 2005
Posts: 232
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

Joined: Dec 24, 2004
Posts: 21
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

Joined: Mar 06, 2001
Posts: 13459

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

Joined: May 26, 2003
Posts: 31079
    
163

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Sripathi Krishnamurthy
Ranch Hand

Joined: Mar 07, 2005
Posts: 232
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

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31079
    
163

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PreparedStatement - Performance