Solved

JDBC: how to write high performance preparedstatement

Posted on 2011-03-18
4
531 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

896 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now