Solved

JDBC: how to write high performance preparedstatement

Posted on 2011-03-18
4
532 Views
Last Modified: 2012-05-11
Hi: I have a Java application, which needs access database. So I write JDBC. The JDBC will create a connection(or take a connection from connection pool), then create Preparedstatement, execute it, then connection commit, and prepared statement close, and connection goes back to connection pool. I find this way there are many prepared statement execution in database trace log. Are there some way to create a Prepared statement for once and execute lots of times. Or are there introduction/web links for high performace JDBC programming?

0
Comment
Question by:JianJunShen
  • 2
4 Comments
 
LVL 92

Expert Comment

by:objects
ID: 35163827
0
 

Author Comment

by:JianJunShen
ID: 35163959
batch does not work at my case. Since for one subject, we have 7 sub tables, they are different prepared statement. It is multi thread application, one has to suppose thread might be dead. One thread picks up one subject and then store it. But how to save the preparement sql execution time.
0
 
LVL 47

Accepted Solution

by:
for_yan earned 400 total points
ID: 35163964
I don't expect any issue in interaction of PreparedStatement and ConnectionPooling.
PreparedStatemnt will give you some advantage if you need to execute multiple similar
SQL statements. As long as you execute them, you hold on to your connection.
If you release Connection, next time in the simplest case you'll have to
create PreparedStatement again. But if you execute sataements rarely enough that
it makes sense to release connection then creating PreparedStatement would not
take relatively much time and probably in that situation will not be very important to you.
Even though there is now some talk about persistence of PreparedStatement in case of pooling,
I guess in most common situations that does not seem to be crucial. Perhaps of course there are some
special cases.

In general, to tell the truth, in my personal experience, with all this talk about PreparedStatements
I never saw something really dramatic in performance improvement because of introducing PreparedStatement.
Where I really saw dramatic improvement is if you can do some part of your operations
exclusively on the Oracle server, rather than through Java client operation.
This is of course obvious thing, but these kind of changes gave me orders of magnitudes time savings.
With PreparedStatements never saw anything on the same level.

 
0
 
LVL 92

Assisted Solution

by:objects
objects earned 100 total points
ID: 35163971
if the prepared statements are different then theres not a lot more you can do.
The power of the PreparedStatement is the ability to use the same statement with different parameters which allows the driver to 'compile' the statement and just run it with different params.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
replace a word with other 1 44
tomcat administrtor 12 47
iterator/ListIterator approach 17 27
JAVA API design with micro service cloud in mind 1 18
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question