Nested Insert/Updates in DAOs

[Andres Delrotti]

Hi everyone hows it going?

We have an application that generates dynamic reports. It could also generate different report templates. For every type of report, there are sections, for every section there are items, for every times there are sub-items and other fields.

So a create/update report template method in our business object class looks like this:





public void createReportTemplate(ReportData data){

Connection con = datasourceProxy.getConnection()


..dao.savedata(data)

List list1 = data.getSections()

..iterate list1
{
Section section = (Section)list.get(i)
dao.saveSection(section)

List list2 = section.getItems()
...iterate list2
{
Item item = (Item)list2.get(i)
dao.saveItem(item)
}


con.commit();


}


The same pattern basically happens in the items and other sub-items/sections as it goes into the lower sub classification of parts. This causes performance issues when there are a lot of users trying to do this action. Would you suggest we do it in one big complex sql or divide each into separate DAOs like what we did here? All daos use the same con boject in the business method.

I'd appreciate any form of help. Thanks

[Herman Schelti]

hi Andres,

do you close your connections? (I don't see in in your code)

Herman

[Andres Delrotti]

Yes. I just forgot to write it after con.commit();

[Herman Schelti]

Anres,

in general:
-1 update statement is faster than more update-statements
-if you do need more statements: using addBatch() is faster than using executeUpdate() for every statement
-PreparedStatement is faster than Statement

Do you use a connectionpool to get a connection
(if yes: how are the settings of the pool)

I don't see any exception-handling in your code: make sure you always close your connection (in a finally clause)

And then there's you database. It's possible that your database locks a whole table instead of a few rows. A DBA should be able help you with that.

I'm curious: how many concurrent users and how many sql-statements per user do you have when there are "a lot of users"?

Herman