Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

We need to load 5 to 10 million records every day, can any one suggest the best way to do this(preferably with Hibernate)..

Our group come up with a solution:
We have two tables, t1, t2, so we created two schemas s1, s2 and created those two tables exactly same structure(t1 and t2)
in both schemas, the idea is while loader is loading data to s1 tables, our front end code access data from s2 tables.. and to let front end know from which schema it should read data, we created status table in our main schema s0, so to access data, we created 3 sessionfactories, sf1, sf2 and sf0, to access data first we create a session from sf0 and query the status table, if status == s1, then we use sf1 session factory, otherwise we use sf2 session factory to load data from tables t1 and t2, do you guys think this is the best possible solution(I am not convinced, but my boss says it is the best)?, if not can any one sujjest a better way of loading bulk data? and what is your comment on the above solution? good? bad?
Thanks in advance,

Hi,

I just finish a project doing just that, 50 tables and 5 gig of data each day dispatched on 2 different systems. Crash proof, generic and configurable (Spring), Thread pool configurable...

First, I dont think Hibernate is design to do this kind of data transfert. It doesn't mean you cannot build your persistence layer with it for your apps, but you may need something else for your data transfert, it will be too slow. This kind of process you are talking is named ETL : Extract Transform and Load.

http://www.computerworld.com/databasetopics/businessintelligence/datawarehouse/story/0,10801,89534,00.html

The JDBC way with batch insert is probably a ok way to do this. If your jdbc driver/database is compatible with batch update. For the jdbc and transaction handling, I would use the Spring JdbcTemplate (+RowCallbackHandler) to load your data.

But for 10M records, you will need speed.

If you are using Sybase, for more speed, you can use the BCP-IN loader to upload your data. This is what we choose. Check if you need to drop the index before insering if you need more speed.

You seems to say that yours apps cannot read the table while loading... do you plan to transfert all data each time? then you need to drop connection, drop index, drop content of table, load, create index... reconnect. You also have to drop/create the foreign keys... If you load partialy the data, you need to have a strategy for loading only the part that you need (with SQL). Also, dont forget to validate your data before opening your connection, using some kind of sql query...

We used 2 databases and the Hibernate layer knows when to get its connection from data-sourceA and data-sourceB using a control data-base/table.

Also, your app need to be crash proof. If your loading fail (and it will) you will probably want to restart the loading where it crash... You will probably need to extract and load in a multi-thread way... if you have many table to load...

Regards,

Etienne.

Good luck.