[SOLVED] How to evaluate a condition on a large number of database records

Issue

I have a datamodel in Entity Framework CodeFirst. This datamodel contains a Contracts entity of which there are about half a million records present in SQL Server. A Contract entity is related to other entities, directly of indirectly.

I now have a backend job that needs to check all contracts for a condition and if that condition evaluates to true, it must do some action on the contract. The problem is that the condition is not that simple that it can be put in some where clause. It’s evaluation for a Contract requires us to check the state of a couple of objects in the Contract hierarchy. The condition evaluates to true for a very small fraction of the total number of Contracts in the database.

That means that I only need to load a small number of Contracts in memory, but to determine which ones, I need to have all the Contracts evaluated, so if I don’t want to evaluate the condition in the database (for example, in a stored procedure), it seems that I need to load all the Contracts in memory.

So, it seems that there are 2 very suboptimal solutions:
1) Determine the contract ids of the contracts that meet the condition in a stored procedure and then from code only fetch those contracts. This would mean that we put logic in our database which seems to go against the whole philosophy of code first.
2) Fetch the contracts all in memory (part by part, for example in samples of 500) and evaluate the condition in code. This is of course performance wise not very good.

My question is, which alternative ways would there be to solve this problem?

Solution

For a given class ComplexClass have a corresponding ComplexClassInfo. The Info class contains key/important properties of ComplexClass. It typically has members for child objects as well. We put in enough properties to give the Info classes some general usefulness. I.E. we don’t have Info classes tailored for specific data queries.

Do an initial DB fetch, which may be filtered, for ComplexClassInfo data. Then iterate that ComplexClassInfo collection applying our complex rule. Using the resulting set we query the DB to instantiate the individual ComplexClass objects.

Answered By – radarbob

Answer Checked By – David Goodson (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *