4. Try combining queries and batch updates.
Impossible to do this. Each request / insert / update depends on the database information. For example, we look at the database for information, if we cannot find its use, we insert the use into the database, otherwise we are updated.
If you do this from a Java application, you can improve performance by doing this in the database in one round. There are several ways:
1) Use the SQL MERGE statement
2) Write a stored procedure for the insert or update logic and just call it from Java.
Further explanation
I assume from what you said that at the moment you have Java logic that works as follows:
// 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;
This means that there are two separate routes to the database: one to check for a record, and the other to insert or update as needed.
Alternatives:
1) Use the 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 ta = sa, tb = sb, tc = sc when not matched then insert (id, a, b, c) values (s.id, sa, sb, sc)';
The MERGE operation is a bit complicated at first, especially when you need to use an Oracle “dual” table.
2) Use the stored procedure:
// Pseudocode execute SQL 'begin mytable_package.insert_or_update (p_id => ?, p_a => ?, p_b => ?, p_c => ?); end;'
The stored procedure in the mytable_package package will look something like this:
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;