Design help on bulk insertion of entity beans..

[Rajasekar Elango]

Hi,

We have a database table and a entity bean to represent that table.

While creating a bean (ie inserting a new row into table), I want the data to be inserted only if that data doesn't exist already in table which can be indentified by combination of 3 columns in that table.

So I think within ejbCreate() method I can call a finder method to check for existence and create only if finder method returns null.

But will this be efficient..?

Because I we have session bean method that will insert bulk of data(around 500 records) and So we will be calling entity bean's create method multiple times (once for each record) within a loop.

So I think doing a ejbFind for every create will impact the performance..

Can anybody suggest me good design for this..? We are using CMP beans..

I'm new to EJB, So correct me if I am wrong..

Thanks,
Raja

[David Harkness]

If you go this route, the session bean should do the query for existing beans -- not the entity bean.

However, another option is to create a unique key in your database on the three columns. If any bean being created matches an existing one in the database, an exception will be thrown which you can catch and ignore (you're expecting it).

There are two performance differences that I see. First is that the second method results in only one query (an insert) per bean whether or not there is one in the DB already. The second is that performing an insert that fails should be faster than querying for a bean, finding one, and loading it into the container, only to be ignored (the bean's values, anyway).

As well, if this process is being run in multiple threads/applications simultaneously, doing the query before creating the bean leaves a very small but non-zero window of error.

For example, say two threads are both about to create the bean with values { A, B, C }.

Thread 1 queries for bean; none found

Thread 2 queries for bean; none found

Thread 1 creates bean

Thread 2 creates bean; exception, bean already there (from thread 1)

If only one process is creating these beans at any given time, this is not an issue.
[ January 11, 2005: Message edited by: David Harkness ]

[Rajasekar Elango]

Thanks David,

I understand that the session bean method should check for existing before calling create on entity bean.

We have a primary column for the table, but that is a auto-generated sequence number using which I can't find out the existence..

So I think I wont be able to do the exception approach..

The session method wont be run by multiple theards...

Thanks,
Raja

[David Harkness]

You wouldn't use the primary key to check for duplicates. Instead, you'd add a unique key or unique index on the three columns you want to enforce being unique. Many databases support this. The primary key is just a unique key designated to be the "primary" unique key.

[Rajasekar Elango]

David,

Thanks very much.. We could do that..

Thanks,
Raja.