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

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.

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;

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.

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.

First of all you need the DBA's on your side to tell you where the time is actually spent. You can be fast as lightning in your client and still have long transaction times until a crucial index is set up.

It's been eight years since I worked with Java+Oracle but I did then not find the oci driver (using a native driver in a DLL) to be significantly faster than then thin driver (all written in Java).

A quick work around to give you breathing space could be to generate a text file with 1000 or 10000 transactions at a time and let "SQLLDR" inject the batch into the database. Perhaps even more. Properly invoked SQLLDR is the fastest thing there is, and it will buy you time to do it properly.

Are you launching new instances of the JVM for each execution? Can you elaborate on the nature of your application (i.e. how it is invoked, what triggers invocation, etc.). From your description, it doesn't really sound like a Java and DB issue. It sounds like maybe some kind of database index problem or other design problem. What is the nature of the SQL commands you are using? Have you timed or profiled these calls to see if some are taking longer than others?

Can you divide your workload to use multiple sessions to the database? There are lots of round trips to the database causing latency. If your tables are structured correctly they can handle multiple concurrent inserts/updates without problems (check INITRANS property of the heavy inserted tables to be the same as the number of concurrent sessions doing the insert). I think this would be the easiest way to win performance for your app. What version of the database is in use? Can you get ADDM reports? They can tell you in no time what - if any - the problem in the database is. Has the app server enough cpu resources ? If not, it's an extra reason to split the load over multiple sessions, in that case divided over multiple app servers. Without a statspack report or - preferably - a ADDM report it is hard to tell where the problem is.

I hope this helps, Ronald.

If you want to keep the DBA's involvement at a minimum, you could at least ask for a login to access the Oracle Enterprise Manager running on a test server. In OEM, you can actually see what operations are taking a long time. OEM will also try to help you by suggesting ways to improve performance, like adding indexes or changing the structures of your queries. Hopefully OEM could at least give you solid reasons for asking for further involvement by the DBAs.