How to make my Oracle update/insert action through Java faster?
- by gunbuster363
I am facing a problem in my company that is - our program's speed is not fast enough. To be more specific, we are telecommunication company and this program handle call/internet serfing transaction made by every mobile phone users in our city. Because the amount of download content made by the iphone users is just too much, our program cannot handle them fast enough.
The situation is, the amount of transaction made by users are double of the transaction processed by our program. Most of the running time of the program are dominated by DB transactions.
I've search through the internet and browsed some sites ( for example: http://www.javaperformancetuning.com/tips/rawtips.shtml ) talking about Java performace in DB, but I cannot find a suggestion suitable for us.
These advices are not applicable/already used, for instance:
1. Use prepared statements. Use parameterized SQL
Already used prepared statement. Each time will use different parameter by clear parameters and set parameters.
2. Tune the SQL to minimize the data returned (e.g. not 'SELECT *').
Sure, already used.
3. Use connection pooling.
We hold a single connection during the program's execution. And I doubt that pooling cannot solve the problem because our program act as 1 user, so there are no problem for concurrent access to DB. If anyone of you think pooling is good, please tell me why. Thanks.
4. Try to combine queries and batch updates.
Cannot do it. Every query/insert/update is depend on the database's information. For example, we look up the DB for the client's information, if we cannot find his usage, we insert the usage into DB, otherwise we do update.
5. Close resources (Connections, Statements, ResultSets) when finished
Sure.
6. Select the fastest JDBC driver.
I don't know. I've search on the internet about the type of driver available and I am very confused. We use oracle.jdbc.driver.OracleDriver and we use thin instead of oci, that's all I know. In addition, our program is a two-tier way ( java <- oracle )
7. Turn off auto-commit
already done that.
Looking forwards to any help.
