Hi,

I'm using LLBLGen Pro v4.0 with self servicing classes.

We have a performance consideration: we have a table (Documents) with a lot of relations to other tables with lookups. For example: DocStatus, SecurityLevel, DocStyle, DocType, Person, Project and lots more.

In the Document-grid we display a lot of these related fields and users can sort and filter on that columns. So we have to add the relations from the Documents-table to the other tables, which we do.

When we execute the query via GetMulti(predicateExpress, 1000, sort, relations, prefetchpath) we get the result but this takes a while. We also add the total amount of records with GetDbCount(rpbp.PredicateExpression, rpbp.Relations) without a maxlNumberOfItemsToReturn.

Some databases contains more than 2.000.000 records and because of all the relations the GetDbCounts takes about 10 seconds. When i remove al the relations the query takes 1.5 seconds, but than we get errors when a user filter on a related field.

Is there a possibility that the relation-collection (which we will add to the GetMulti() method) can be purged on the basis of the predicate-expression? For example: When the user filters only on a field in the related-table DocStatus, only that relations needs to be present in the relation-collection. I know it is possible (via a HelperClass of helpermethod) to remove (cleanup) unnecessary items in the RelationCollection, but how can i determine which relations a need on the basis of the predicate-expression?

Best regards, Sander.

SanderF wrote:

Is there a possibility that the relation-collection (which we will add to the GetMulti() method) can be purged on the basis of the predicate-expression? For example: When the user filters only on a field in the related-table DocStatus, only that relations needs to be present in the relation-collection. I know it is possible (via a HelperClass of helpermethod) to remove (cleanup) unnecessary items in the RelationCollection, but how can i determine which relations a need on the basis of the predicate-expression?

Hi Sander. I think that the most obvious part would be in the logic where you catch the filter and build the predicate:

// ...
if (!string.IsEmpty(txtCustomerName.Text)) 
{
     // add the filter

     // add the relation
}

// ...

DoTheFetch(filter, relations);

If that it's not possible, you could traverse the predicate expression tree, however it may be not trivial to know the relation (s) you have to add based on the 'expression' given in the expression tree.

If a query is slow with joins, like yours, try to add indices first, on the FK fields (as a whole, if you use compound FK/PKs) and the fields most filtered. Indices are key here to get a fast result. A count on 2million rows shouldn't take 1.5 seconds either. So check with the sql server profiler where the bottlenecks are in the database.

For lookups you don't need to fetch these with the entities. Simply fetch the lookups separately and cache these in your UI. Then, where the user can select from a lookup, use databinding to show the lookup list and through databinding you can set/get the fk field in the main entity (so the 'value' of the combobox representing the lookup list is the PK field of the lookup, and it is shown on the FK field of the entity related to the PK field of the lookup).

This way you don't need to fetch lookup entities all the time and can fetch only the main entity set. Also filters can work like that, you don't need to join with lookups for their PK value, the FK field in the main entity is the same value.

But above all, first profile and measure where the bottleneck is. Without that it's impossible to solve this.