This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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.
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.
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.
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.