high performance (low level) jdbc access routines

[dkuehner]

Does anyone know of any low level jdbc access routines?

I am hoping that java.sql is an object layer sitting above a lower level - but still OS independent - layer.

My need is to be able to do high speed data access. I'm looking for something 2 orders of magnitude faster than what is available with the typical use of java.sql (connection, preparedstatement, resultset and so on). This is not a typical web app issue (single object gui). I am working with a process that is initiated from the gui, but can take 20 minutes to run. This process reads 50,000 to 500,000 records from several tables, performs some magic and then has to write or update about 13,000 records. I'd like to be able to have the process run in less than a minute to meet user expectations.

Several years ago while using visual basic and odbc, I had to use direct calls to the odbc32.dll functions for data access to get the performance I wanted rather than the ado and rdo stuff. I am wondering what is available with jdbc - perhaps the routines that are used when writing jdbc drivers themselves. I don't know if there is an equivalent "gateway" that all jdbc drivers use (like the odbc32.dll that is used with all odbc drivers in windows)

Any comments would be appreciated. Even just hints as to where I might find this kind of information.

[triathlon98]

This is all database specific and depends on the type of driver. For example a type 4 jdbc driver directly converts/maps the jdbc api to the wire protocol to talk with the database.

Joachim

[dkuehner]

One of my next steps was to upgrade to the latest of everything (java, jboss, etc.) I'll also look for more recent jdbc drivers. Does "type 4" imply better performance or is there somewhere where I can get faster drivers? I've used the oracle odbc drivers (and ensured that they were the right ones for the database versions) I also tried the datadirect drivers, but there was no real performance boost.

By the way, I've been using the "Extensible Java Profiler" to look for bottlenecks. So I ran it again last night and it is able to show me the procedures called within the jdbc driver. It looks like the next level of API calls is database dependent so I guess I've answered my initial question. There is no lower level API available.

[fbiaggi]

hello,
I think that your solution maybe the use of stored procedures...

[dkuehner]

> I think that your solution maybe the use of stored procedures...

Thank you - its being considered.

The only problem is that this is a commercial app and our clients can use any relational db they want. As most of them use oracle or sql server, we may just go wuith stored procedures anway.