CODEWITHSUNDEEP
javaperformanceoracle-database
Dan
Dan

Reputation: 9801

what's the fastest way to get a large volume of data from an Oracle database into Java objects

What's the fastest way to get a large volume of data from an Oracle database into Java objects.

Are there any Oracle tricks as to the way the data should be organised?

I was thinking of using plain JDBC rather than any Hibernate style libraries?

Would it be better to get Oracle to produce a file and then read from the file - although this has to be done programatically.

All thoughts appreciated.

Upvotes: 3

Views: 6933

Answers (3)

RC.
RC.

Reputation: 28267

I am not a Java or JDBC expert, but if you plan on pulling a lot of rows down from a database, you will likely benefit by increasing the prefetch rows on the connection.

Connection conn = DriverManager.getConnection ("jdbc:oracle:","user","password"); 

//Set the default row prefetch setting for this connection 
((OracleConnection)conn).setDefaultRowPrefetch(100);

I believe the default for JDBC is to fetch one row at a time, so you're paying for a round trip to the database for each row fetched. (Note, I've seen documentation that suggests the default is 10 rows per round trip). Setting prefetch to a larger number will fetch more rows per round trip to the database. Speed increases can be dramatic depending on the number of rows and the performance of your network.

Upvotes: 4

Roger Cornejo
Roger Cornejo

Reputation: 1547

Whatever you wind up doing, design for/implement "lazy initialization" [really only applies for complex object hierarchies/networks; you said java objects (plural) so I'm imagining something more than just a single table that maps to a single object]. So basically, you are only reading in the objects that are needed at that time; when you run a getter method, then it does more db calls for just that data.

Another trick sometimes overlooked in the Java world is: if you have some complex sql coming from the code, you can rather create a view on the Oracle side, embedding that complexity there, then map your object to the view; so if you can flatten your object like the view, then you're in business.

Upvotes: 1

Emil L
Emil L

Reputation: 21111

Depending on how far you want to go with this I'd imagine dropping jdbc and writing a custom application residing on the same machine as the DB using Oracle Call API and JNI would be the fastest...

It's probably much simpler to just use a plain prepared statment using JDBC and then if that's not enough (and depending on where the bottle neck is) try making a stored procedure. The caching done by ORM's like Hibernate should not be discounted though, so I guess you'd have to do some benchmarks. Also if the bottle neck is the database and you write a stored procedure which improves the read performance, then you could still use Hibernate to marshal the data to java objects. See Using stored procedures for querying

Upvotes: 1

Related Questions