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.