Using NVL vs. Using Case

A Friend of mine (thanks Tal) brought my attention to a problem that needed to be solved.

Lets take a look at these SELECT statements (based on scott sample scheme with an addition of a foriegn key of emp_id to department as manager_id):

SELECT priQuery.ename,
nvl(mgr,
(select manager_id
from dept subQuery
where subQuery.deptno = priQuery.deptno)) as MGR
FROM emp priQuery;

SELECT priQuery.ename,
CASE WHEN priQuery.mgr is null THEN
(select manager_id
from dept subQuery
where subQuery.deptno = priQuery.deptno)
ELSE
priQuery.mgr
END as MGR
FROM emp priQuery;

Both of these statments implements the logic of retrieving the manager id from department table in case there is no manager at the emp table (mgr is null).

I Know thre's also an option of using a join, but in my scheme, most of the employees have manager.

So, Which is better?
Well, when I first thought of this I immediately said the CASE is better because there is no PL/SQL context switch - anyway, it was an intereting question so I made a test case to check.

I've create a counter implemented as a table:

create table LOG_TABLE (counter number);
insert into log_table values (0);


I've also created a function (so i can call it from my SELECT) that increase counter count

function counter return number
is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update log_table
set counter = counter + 1;
commit;
return 0;
end;

The results for the default data in the table (just one emp has missing manager id) were as the following:

The NVL function executed the select for each deptno (althogh it runs for each dept one time - the function is determenistic) while the CASE executed the SELECT just once (and it's also determenistic) ...

in this CASE :) using the case will be smarter (and for the pl/sql context switching - oracle 11g is making it quite transparent).

JDBC Batching using Oracle Driver

There's lots of reasons why using java when developing oracle based applications - especially after Oracle bought sun company. One of the main imporvements in compare to the .NET ODP is that JDBC gives us is the ability to use statement batching at a very simple development cost. No need to be familiar with Array binding or bulk operations.

Let's see how its done...

Oracle Side:

CREATE TABLE TEST (ID NUMBER, STR_VALUE VARCHAR2(10));

Java Side:

import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

...
Connection connection;
OracleDataSource ods = new OracleDataSource();

ods.setDriverType("thin");
ods.setServerName("SERVER");
ods.setDatabaseName("SID");
ods.setPortNumber(1521);
ods.setUser("USER"); ods.setPassword("PASSWORD");
connection=ods.getConnection();

// Set the currect batch size - default value is 1
((OracleConnection)connection).setDefaultExecuteBatch(100);

PreparedStatement ps = connection.prepareStatement ("INSERT INTO test VALUES (?,?)");

ps.setInt (1,1);
ps.setString (2,"Oracle");
ps.executeUpdate(); // Nothing actually happens here

ps.setInt (1,2);
ps.setString (2,"SQL Server");
ps.executeUpdate(); // Nothing actually happens here either - until we get to the batch size

int total_rows = ((OraclePreparedStatement)ps).sendBatch (); // Or we can just tell JDBC we're done using this statement :)

Checking the shared pool will show us just one execution.

I Like Java - it keeps the things simple!

Using NHibernate Batching with Oracle 11g

I was supposed to find a solution for NHibernate's low performance with massive inserts.

Searching the web didn't help much and although java's version of hibernate clearly used batching efficiently, I Had to struggle the .NET Version to use the batching mode.

Basicly, an average insertion of a record took 1 mili-second (0.972113 to be precise) - Using the batching mode i've made it last just 0.06 mili-second that's a 1600% improvement!

The most crucial difference between Hibernate and NHibernate is the fact that Hibernate is able to re-order the commands once we flushed the data and set the configuration right - While NHibernate has this facility missing, not to worry - a simple solution is avilable - reorganise the savings.

So first, you should set "ado.batch_size" to the desired value, then take your list of entites and save all the low-level properties (nhibernate bags for example), then save the entity itself.

I would recommend you to turn on the NHibernate logging, and check that the similar SQL commands are tied to each other.

The final check should be at the oracle side, look for your insert at the shared pool (v$sql) and check the execution count each run - that's should do the work!