Spring + Hibernate persistence causing problem

[kkona]

We are developing an application system that will discover ‘devices’ on the network, interrogates the devices to get their characteristics and then persists these characteristics to a database. Specifically we use Spring for overall control of the application and use Hibernate as the OR mapping tool with a Firebird database. There are two tables on the database ‘Devices’ and ‘Settings’ with a FK relation between them. There are Primary Keys defined on both the tables besides a separate Unique Index on a Devices column that is essential for our business logic.

When we run our application against a network that has 16000 devices, we discover all of them and the related settings will number about 120000. Now because of the business requirements’ constraints placed on us, we need to discover one device at a time and then persist it to the database, a straight insert into the tables. We are using a thread pool of 200 threads with each thread discovering a device and persisting it.With the persistence to the database turned off we can discover the 16k devices in 25 minutes, but when we try to persist the data to an empty database the running time increases to 4 hrs and 30 min.

I know that persisting one device’s data at a time is inefficient and cost intensive, as Hibernate will open a session each time, create a transaction, persists the data to database, and then closes the transaction and session. And at the database level each insert is a transaction and will involve a rebuilding of all the related indexes. And it gets worse as the data in the tables grows.

Is there a way to accomplish this within the existing constraints? May be by leveraging a combination of Spring and Hibernate features?

I have thought of using Hibernate’s II level Query Cache that will span sessions but am not sure it will make much difference as we are dealing with straight inserts.

Any suggestions/pointers on making this work better are most welcome. Thanks in advance for any help.

[manifoldronin]

Perhaps consider the producer-consumer pattern? In other words, there can be some "discoverer" threads discover devices and put them on a queue, while a "persister" thread takes them off the queue and inserts them into the DB. This way, the persister can buffer up the devices and use either hibernate's batch processing or raw sql to batch insert.

[wexwarez]

if you absolutely can't batch these inserts you might find that hibernate is not ideal for this piece of your application, cause you aren't really taking much advantage of it and so might just be dealing with the overhead of using it.

[Costin Leau]

You can use small caches so that you'll insert around 5 or 10 devices at a time if you find them in 30 seconds or so for example. Otherwise you can use plain jdbc (which you can mix with Hibernate) or one of Hibernate 3.1 features like Stateless session or just plain session but with the cache disabled.

[kkona]

Guys,

Thanks for the replies.

Option I and III are possibilities for our application. But there is a catch there too. We could have two of the discoveries running against the same network simultaneously and we don't want to discover a device that was already discovered by another process but has not yet been persisted to the database. There are ways to get around that but we are going to put checking those options off until we are absolutely sure that we cannot do it any other way.So, right now I am checking to see if holding the data in second-level cache waiting for batch insert makes a difference while letting Hibernate decide if a device was already discovered and doesn't need to be persisted to db.This is just a variation of the III option.

Pls do let me know if you guys can think of anything else.

Thanks