What is the cost of a Statement

Question

If I only ever use one Statement at a time would it be worthwhile cacheing it along with my Connection. I already cache the connection so I could cache the statement at almost no cost at all.

Essentially I suppose I am asking if there is any cost/overhead to creating a statement. I fully understand the benefits of creating prepared statements. I am talking specifically about Connection.createStatement() here.

Answer 1

The cost of a Statement cannot be quantified independently of other factors; e.g. the database, the JDBC drivers, the SQL in the statement and so on.

You can be sure that there will be an overhead in creating a Statement (or PreparedStatement) and in executing it for the first time. However, there's a good chance that it won't be significant to the overall application's performance. And if it isn't, then implementing the caching code will simply be wasted effort.

You shouldn't be guessing whether this is (or isn't) going to be a worthwhile optimization. What you should be doing is get your program working, THEN profiling it, THEN using the profiling data to determine what needs to be optimized. If a significant amount of time is spent executing the same queries, then maybe caching will help, and maybe not. Try it out and see if it makes a measurable difference to performance.