JDBC-ODBC Bridge performance
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   AviD_iR
Posted On:   Tuesday, March 25, 2003 06:31 AM

Hey all, I'm running a Java app that extracts an entire table (set of tables) from Oracle and copies them into Access. The easiest implementation has been to use a DSN-less Type 1 JDBC-ODBC bridge connection to Access, but unfortunately the inserts are taking too long. I have tried both Statement and PreparedStatement approaches, with both single row and batch updates. Some of the performance times, relative to the computer I am on (PIII 1 gHz with 256 RAM), are as follows: Via Batch, about 8min, 15 seconds per 10,000 rows. Via Single Row updates/inserts, about 52 minutes for 45,000 rows. I've tried alot of varieties with   More>>

Hey all, I'm running a Java app that extracts an entire
table (set of tables) from Oracle and copies them into
Access. The easiest implementation has been to use a
DSN-less Type 1 JDBC-ODBC bridge connection to Access,
but unfortunately the inserts are taking too long.


I have tried both Statement and PreparedStatement
approaches, with both single row and batch updates.


Some of the performance times, relative to the computer I
am on (PIII 1 gHz with 256 RAM), are as follows:


Via Batch, about 8min, 15 seconds per 10,000 rows.

Via Single Row updates/inserts, about 52 minutes for 45,000 rows.


I've tried alot of varieties with batch, but 10,000 seemed to be the best. The largest table is only 45,000 rows (right now), but has the potential to grow much larger (obviously).
This application needs to backup 4 databases, each with N tables (current N=7, but will expand). I'm trying to knock down the times and increase performance, but am not sure what is "reasonable" for Type 1 connections with JDBC.
I've seen third party drivers, Type 3, for Access...but don't want to run the middle tier server for filtering request through. I'd rather use Type 4, but can't seem to find any for Access.


Any suggestions? Recommendations? Let me know! Thanks all! :)

   <<Less

Re: JDBC-ODBC Bridge performance

Posted By:   Benoit_Quintin  
Posted On:   Tuesday, March 25, 2003 02:08 PM

Any suggestions? Recommendations?

I suppose that telling you not to use Access isn't an acceptable suggestion for you? Because Access is NOT a Database, if you ask me... It's more like excel on steroids... Try MySQL if it needs to be free, or try SQL Server if you have to work (or are stuck) with Micro$oft's products...

About | Sitemap | Contact