Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

JDBC: how to write high performance preparedstatement

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to get all the API from website? 11 104
couple of eclipse 5 45
ejb example issues 3 20
servlet and mdb, jms error 1 30
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

809 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