Speed up read/write collection

[Chicagoan2016]

We have a use case where we retrieve thousands of records from a database using a stored procedure, with the total number ranging from 2,000 to 20,000.
We have a read/write collection that corresponds to those records. The Fetch operation is fine, but before we send those records back to the DB, we have to run almost ten business rules. Some of the business rules have to computer fees/taxes, etc.
This is making the processing time in minutes, anywhere from three to four minutes to 15 minutes. I think we are doing something wrong here :).
Any recommendations, please.

Regards

[StefanOssendorf]

Are there any dependencies between those records?

If not you could parallelize the computations to shorten the time.

[rockfordlhotka]

Also, is it possible that the object graph is bound to the UI while the rules are running, causing a flood of binding operations as the rules change values?

I've seen that before, and stopping that binding can save minutes of time.

How to do this varies by UI framework.

[Chicagoan2016]

@rockfordlhotka the read/write list is not bound to UI. After we are done processing, we save the list. The user could optionally open another form and look at the 'completed' list.

[Chicagoan2016]

@rockfordlhotka Our rules run on the client.

Our current scenario dictates the following.
1) Users initiate the process by clicking a button, and everything as outlined below happens in the background.
2) Get the list of records from the DB, which are coming from multiple tables using a stored proc, and each record corresponds to
a read/write object (which is part of a read/write collection).
3) In the step above, we are getting the objects in the Dataportal create method since we are going to insert the records in a new table
in the DB (for later review and also to print some documents).
4) We aren't explicitly calling the CheckRules method since we call the collection's Save method as the last step, and it takes care of all
business rules in the child objects.

[rockfordlhotka]

A couple questions then.

First, are you using BypassPropertyChecks during the creation process, so the rules don't accidentally run at that time?

Second, do you have an app server? If so, would it be better to use a command object and run the process on the server? That would eliminate flowing the data over the wire twice.

[Chicagoan2016]

@rockfordlhotka we are using ByPassPropertChecks in the creation process.
We don't have an app server.

The rules have to run for 'Insert' operation. That's our biggest concern. @hurcane mentioned moving rules for calculating fees etc. to sql server but we already have a ton of existing code (Csla) and would like to use that.

[hurcane]

You've been getting general advice. It may not apply to your specifics. You should use a performance monitoring tool to analyze where the majority of the time is being spent. Using the results, you can craft a change to reduce the bottleneck. Sometimes an existing design requires a major architectural change to be faster.

[Chicagoan2016]

I have noticed elsewhere that business rules run more than once if they accept more than one property.
Can we run a business rule only once, even if it has, say, four input properties?

[hurcane]

Can you do the calculations using a batch technique? For external validations, we sometimes write the validation logic in a separate class that supports a bulk operation by defining a list of criteria to be supplied. A business rule in the class is configured to use that class, but it is configured to not run in check rules. That rule class only adds one criteria instance to the list. In the data portal operations, we'll use the validation class directly, passing in a full list of criteria constructed from what was fetched from the database.

[Chicagoan2016]

@hurcane I am not sure if we could use a batch technique for calculations since every object will have different fees/taxes based on the values from the DB

[hurcane]

But don't the calculations all use the same set of parameters? Different values, yes, but same parameter structure, right? And the results also follow the same structure, do they not? With SQL Server, you can create a table type to match the parameters, use that as a parameter to a stored procedure, and then the stored procedure can do the calculations for the entire set of criteria and return multiple rows of results, which can then be transferred to the business objects.

This technique has given us dramatic performance improvements when users absolutely must edit thousands of objects in a single session.

[michaelcsikos]

Is it the validation rules consuming the time, or the database inserts? Do the validation rules do any round-trips to the database? If you're doing one round-trip for each insert, this would take a very long time for 20k records. You can generate a batch script in SQL for thousands of inserts and updates and fire it off in one round-trip, or maybe two if you need to retrieve the new concurrency stamp / rowversion / last changed date, etc.

[Chicagoan2016]

@michaelcsikos ,
You mentioned two points, and improving both will help us.

1. The business rules do hit databases for fees' calculations. We noticed some of the business rules are run more than once. They accept more than one property, and I am sure they run every time one of the properties changes.
2. We are using Clsa read/write collections. We haven't used batch scripts with collections before; we are using the standard 'update' functionality provided by Csla collections.

Any help regarding both points will be appreciated.
Regards

[michaelcsikos]

If the parent collection is the root object you will use DataPortal_Update(); if the root object is a BusinessBase<T> which then has a child collection, you'll use the [Update] method.

In any case, you can create a class to help set up your batch update. Pass it down to each child's Insert or Update method:

[InsertChild]
private void InsertChild(MyBatchProcessor processor)
{
   ...
}

[UpdateChild]
private void UpdateChild(MyBatchProcessor processor)
{
   ...
}

Each child can push its own data in the batch processor. This could be as simple as appending SQL statements.

I've shared some code about this idea previously:
#2828 (comment)

[Bowman74]

@rockfordlhotka hinted at what it sounds like the "right" solution. You are bulk updating thousands of records. An efficient way to do that is not going to be to fetch those records into thousands of objects and then running ten calculation methods against each of those objects and then saving them in what I suspect may be thousands of individual SQL update statements.

I suspect that's part of where Rocky was going with the command object suggestion. From a design perspective this should be treated like a bulk update operation because that's what it is. I know you want to preserve the logic you already wrote and sure you can do some tricks with multi threading and similar to make it better. But you will never be able to fully get around the reality that the base design pattern used will never be an efficient one to handle bulk update operations.

I hate to say it but the "performant" answer may be a command object running SQL statements that do bulk updating of data assuming you are using a database server.

[Chicagoan2016]

The Fetch operation is not 'bad', but when we insert the records back into the DB, we have to run rules, and that's taking minutes.

[Bowman74]

@Chicagoan2016 Yes, that is when the design starts falling down.

[Chicagoan2016]

So we ran a few tests; if we move updates to 'bulk' SQL, we get improvements. Now we will try a separate non-Csla class for fees/taxes business rules, or move everything to T-sql.