How to make my Oracle update/insert action through Java faster?

Question

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.

Answer 1

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.

If you are doing that from the Java application you can improve performance by doing it in the database in one round-trip instead. There are a couple of ways:

1) Use a SQL MERGE statement

2) Write a stored procedure to do the insert or update logic and just call that from Java.

Further explanation

I assume from what you said that at the moment you have Java logic that works like this:

// Pseudocode
execute SQL 'select count(*) from mytable where id=?'
if result = 0 then
    execute SQL 'insert into mytable (id,a,b,c) values (?,?,?,?)';
else
    execute SQL 'update mytable set a=?, b=?, c=? where id=?';
end if;

That means 2 separate round-trips to the database: one to check whether the record exists, and another to either insert or update as appropriate.

Alternatives are:

1) Use a SQL MERGE statement:

// Pseudocode
execute SQL 'merge into mytable t using (select ? id, ? a, ? b, ? c from dual) s
             on (t.id = s.id)
             when matched then update set t.a = s.a, t.b = s.b, t.c = s.c
             when not matched then insert (id, a, b, c)
                  values (s.id, s.a, s.b, s.c)';

The MERGE statement is a bit daunting at first, especially when like this you have to use Oracle's "dual" table.

2) Use a stored procedure:

// Pseudocode
execute SQL 'begin mytable_package.insert_or_update
              (p_id => ?, p_a => ?, p_b => ?, p_c => ?); end;'

The stored procedure, in a package called mytable_package, would look something like

procedure insert_or_update (p_id mytable.id%type
                           ,p_a  mytable.a%type
                           ,p_b  mytable.a%type
                           ,p_c  mytable.a%type
                           )
is
begin
    update mytable
    set    a = p_a, b = p_b, c = p_c
    where  id = p_id;
    if sql%rowcount = 0 then
        insert into mytable (id, a, b, c) values (p_id, p_a, p_b, p_c);
    end if;
end;

Answer 2

Check your indexes!!! Bad update performance can be the result of a foreign key constraint where the index on the foreign key is missing on the referencing table.

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.

Two things come to my mind:

• Do the UPDATE statement and check the result of ExecuteUpdate(); only if it is zero, do the INSERT. Saves you one SELECT statement.

• Always (possibly batch-)insert into an intermediary table, later use the MERGE statement to update your usage table.

5)Close resources (Connections, Statements, ResultSets) when finished

Keep the connection open as long as possible (i.e. permanently till shutdown of the server), prepare a PreparedStatement once and use it repeatedly.

---

Do a bit of aggregation before you write to the database. A cell phone user who generates a transaction now probably will generate another one within a few seconds. Use a hashtable to aggregate current usage and write it to the database after a minute or so.

Answer 3

Get hold of a copy of Professional Oracle Programming.

It seems a bit on the old side at 2005, but Oracle doesn't change drastically when it comes to optimising performance. I've got this book myself and have used it's advice to speed up seemingly intractable performance issues for many applications. Get it. Read it. Do it.

So what can you do while you wait for express delivery?

• Get the DBA on your side, - you'll need their help and their tools
• Get hold of TOAD and pay for the extra query analysis tools if necessary
• Check your indexes for every query that you run - you need to examine the execution plans carefully (AUTOTRACE and EXPLAIN PLAN are your friends here)
• Consider the type of index you're using (could a functional index do the trick?)
• Consider using transportable tablespaces
• Use the built-in Optimizer to gather information
• Obtain statistics so you can measure performance gains (irrespective of the pre-caching and suchlike)
• Consider stored outlines
• Consider materialized views to allow splitting your data to that which is needed immediately and that which can suffer a delay
• Consider table truncation to reduce the size of the overall tables as older data is farmed off

That should be enough to give you a firm grasp on what is failing and how to fix it.