Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

We are trying to figure out the best practice for working with very large DBs in Java.

What we do is a kind of BI (business Intelligence), i.e analyzing very large DBs, and using them to create intermediate DBs that represent intelligent knowledge of the DBs.

We are currently using JDBC, and just preforming queries using a ResultSet.

As more and more data is being created, we are wondering whether more appropriate ways exist for parsing and manipulating these large DBs:

  1. We need to support 'chunk' manipulation and not an entire DB at once(e.g. limit in JDBC, very poor performance)
  2. We do not need to be constantly connected since we are just pulling results and creating new tables of our own.
  3. We want to understand JDBC alternatives, with respect to advantages and disadvantages.
  4. Whether you think JDBC is the way to go or not, what are the best practices to go by depending on context (e.g. for large DBs queried in chunks)?
share|improve this question
    
Have you looked at JPA yet? –  user1249 Mar 2 '11 at 13:09
2  
When talking about JDBC performance, I believe one cannot avoid entering into specifics. I'd therefore be interested in some additional detail: you did not mention the type of databases (just oracle or also mysql postgres informix db2...). Also some metrics. For instance order of magnitude of the data size (eg > 1TB), number of tables (eg > 1000), columns (eg > 20000) and number of records for large tables ( > 10 millions). For Oracle, use of partitioning... etc... –  Alain Pannetier Mar 2 '11 at 13:33
1  
I'm not entirely clear on what you're asking. Sounds like you actually would be better off doing the data manipulation completely in SQL? –  Brian Knoblauch Mar 2 '11 at 14:14
    
@Thorbjorn - yes, I have started to look into it, but am afraid this is quite an uncharted territory for me. I would love to hear from your experience about the pitfalls and advantages. –  user19000 Mar 2 '11 at 14:24
2  
I think I would hire a database specialist who has experience in high performance systems (espcially if you are going to ORacle) who will analyze the performance specifically for your current design. If you did not start with this person, it is likely your design is massively flawed for high performance. This isnot a subject we can fix with INternet questions, it requires a person with the right specialty information who is part of your company. –  HLGEM Mar 2 '11 at 16:06

4 Answers 4

Don't do this. If you are analysing lots of data, do it in the database.

Stored procedures, temporary tables, etc.

It is data, and that is what a database is good at. Use java to submit the requests and read out the results. Let the DBMS manage the data, since it is a Data-Base Management System.

share|improve this answer
    
Note that Oracle supports stored procedures written in Java. –  user1249 Mar 5 '11 at 12:00

Ok. I'll elaborate.

Let me guess you pull data from the DB, stick it into java objects, edit the java objects, then save back to the database? This is OK to a certain extent.... but for large amounts of data it is not. Lets say you want to disable all users living in the state of Maryland. You might pull ALL of their information that is not even used into the java object (firstname, birthdate, etc) and updated EVERY field of that user even if it was not edited. This is OK for single record edits, not for massive million row batch processing. Instead consider [update employee set status='disabled' where state='maryland'].

create a sample table, fill it with 10 million rows of fake data. Compare performance of loading stuff into java objects versus simple set-based SQL updates.

share|improve this answer

Yes if your datebase is large ,You can use partitioning for storing this data.And as above said do not fire single query for fetching data for small comparison or analysis operations.

Divide your logic such way that easy filtering criteria handled by stored procedures and query itself and only complex algorithm which may be not supported by sql query or supported procedure should be done with java after record fetching.

share|improve this answer

Enterprise tools like IBM InfoSphere do exactly what you did with JDBC connection. I touched their IBM DataStage studio for a while, I saw that.

My advice for you is designing the schema of the big source data so that when you do intermediate data transformation you could write down the progress (by using some column), so the the big task can be divided into smaller tasks based on values of the progress column. Say fetch one fetches 20000 rows, marking the offset for fetches 2, etc...

I would do as much in java as possible because of the plethora of ways in which you can log, debug when somethings go wrong. If you rely too much on the DB, I don't think debugging and log-reading would be that comfortable.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.