Design Questions

mr_lasseter posted on Thursday, November 16, 2006

I have an Editable Root object that contains an Editable Collection.  The Editable Collection might consist of over a couple thousand records all of which might need to be saved to the database.  This can take a considerable amount of time to save.  Which leads me to 2 questions.

1.  Is there any way to send all the save commands to the database at the same time rather than have 1000 individual calls to the database? 
2.  How are people displaying the progess of saving a large number of items to the user?

Thanks in advance.
Mike

ajj3085 replied on Friday, November 17, 2006

Needing that many children to be saved at once is rare.

You should only make an object a child if it absolutely cannot be saved independantly of its root object.  That is, if you change the child, it must be saved within the context of its root.

This is actually a pretty rare situation.  I would first rethink your design to and evaluate whether there is really any requirement that you can't save a child business object seperate from the root.

If you really find your case to be valid, you will have to come up with a way to batch the commands.

For example, I once created an app which could generate a million rows of data at a time, all which needed to be saved.  Saving was taking a long time, so I looked into a way to speed things.  Basically what I ended up doing was creating a way to get the Sql string to be executed, without actually executing the command.  I would add the command to a StringBuild, and every 3000 or so, I would send the entire batch built so far.  This created less network traffic, and significately sped the saving.

The downside though is that you would need to check the results of each command, and have to give that information back to the Bo's so they could call MarkOld and such (unless your save becomes destructive).

Alternately, and probably a better solution, would be to setup remoting.  Put the remoting host right on the database server.  If you're using Sql 2005 you can connect through 'direct memory.'  This would be one network call (your objects get serialized and sent), the db updates would happen very quickly, and the objects would be sent back over the network.

If you can't do that, setup a remoting host on another computer, and make sure you have a gigabyte connection directly between database and application server. 

HTH
Andy

mr_lasseter replied on Friday, November 17, 2006

Andy,

Thanks for the response.  I am working on a billing application, so I have the following hierarchy Master Invoice -> Sub Invoice -> Sub Invoice Detail.  Having a Sub Invoice Detail item doesn't make sense without having a parent or grand parent in my case.  In the system most bills are imorted without any user interaction, but there are cases where the users need to manually enter the bills.  To do this we create a copy of last months bill for the user to edit.  Usually these bills don't consist of more than 100 records at the most, so generally speaking we won't be saving more than 100 records at a time.

We are using Oracle so the direct connection is not an option.  I don't mind that the save will take 1 minute or so to complete, but i would like to give the user a status on how the save is doing.  We are not using remoting nor is there any intention to do so in the future.  So I was thinking we could just raise an event when saving form the invoice class.  Would there be any negative performance impacts of this?

Also in your solution when you built 3000 sql statements to send at once how did you check the results?  I will also need to get the id of the detail object being saved, so I don't think this approach would work for me, but I would like to know for the future. 

Thanks,
Mike

 

ajj3085 replied on Friday, November 17, 2006

mr_lasseter:

Thanks for the response.  I am working on a billing application, so I have the following hierarchy Master Invoice -> Sub Invoice -> Sub Invoice Detail.  Having a Sub Invoice Detail item doesn't make sense without having a parent or grand parent in my case.

It doesn't make sense to have a sub invoice detail item without an invoice, I agree. However, it may make sense to edit a sub invoice detail outside the context of a sub invoice. Does that make sense? In other words, you may create a sub invoice detail by passing a sub invoice to the factory creation method.

mr_lasseter:

We are using Oracle so the direct connection is not an option.

Even still, the connection would be to a local machine, and thus nothing would go across the network.

mr_lasseter:

I don't mind that the save will take 1 minute or so to complete, but i would like to give the user a status on how the save is doing.

This is a tough one, because I'm not sure of any method that would allow you to use remoting in the future. Never say never. At some point you may have to switch to remoting to scale your application. Raising an event would slow things, as the event has to process before your code continues, unless you're on different threads and raise the event asynchronisly... which I'm not sure is possible?

mr_lasseter:

Also in your solution when you built 3000 sql statements to send at once how did you check the results? I will also need to get the id of the detail object being saved, so I don't think this approach would work for me, but I would like to know for the future.

As I said, it would be a lot of work. We weren't doing Csla, and our BOs were more data objects than real business objects.. for this we also threw away the objects once the save was completed, because the code ended up running in a service. You'd have to figure out a way to process updates once you got everything back. It wouldn't be fun..