RaqForum 22 No.
4,437 View  • 
     ...

What Can We Do with the Slow JDBC?

 

A Java program connects databases and retrieves data through JDBC. The SQL query is simple, database load is light, and data size is over one hundred thousand rows. Yet the execution is slow. A dozen of seconds are needed. Is there any way to make it faster?

The fact is that JDBC performance is never high. Tests show that Oracle JDBC is several times slower for data retrieval than for text reading. As a built-in component provided by database vendors, there is nothing for our “outsiders” to do to enhance performance.

When the database load is light, we can increase speed using multi-CPU parallel retrieval. It’s easy for esProc SPL to implement the strategy, as shown below:

A

B

1

fork to(4)

=connect("ORACLE")

2

=B1.query@x("SELECT * FROM CUSTOMER WHERE MOD(C_CUSTKEY,4)=?",A1-1)

3

=A1.conj()

The four threads connect to the database respectively via JDBC and concatenate their retrieved data. The execution will be about 4 times faster with a light load database.

 

esProc SPL also provides JDBC for being called by Java. We save the above script as a script, say load.dfx, and call it from Java as we call a stored procedure:

…
Class.forName("com.esproc.jdbc.InternalDriver");
con = DriverManager.getConnection("jdbc:esproc:local://");
st = con.prepareCall("call load()");
st.execute();
ResultSet rs = st.getResultSet();
…

Now enjoy the high-speed parallel processing.

 
 •  4,437 View  •