Solved

JDBC: how to write high performance preparedstatement

Posted on 2011-03-18
4
536 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
[X]
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
  • 2
4 Comments
 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

636 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