Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Nested Insert/Updates in DAOs

 
Andres Delrotti
Ranch Hand
Posts: 172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 387
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi Andres,

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

Herman
 
Andres Delrotti
Ranch Hand
Posts: 172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes. I just forgot to write it after con.commit();
 
Herman Schelti
Ranch Hand
Posts: 387
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic