How to deal with a heavy database retrieve

In our application we need to assign a supplier to a dossier. For a assigning a dossier to a supplier we are calculating which supplier should be assigned to a a specific dossier. For this we are using assignment rules. In the assignment rules categories, subcategories and other objects apply (reference sets). For each reference set we retrieve all objects and after retrieving the last reference set we do the database retrieve. For calculation we need to retrieve a list of all dossiers from the database already assigned to the supplier and applicable to the assignment rule. For the retrieve we are using a complex Xpath. Like: [Dossier.Dossier_Category = $AssignmentRule/Dossier.Applicables_Category] [Dossier.Dossier_SubCategory = $AssignmentRule/Dossier.Applicables_SubCategory_STP] [Dossier.Dossier_ObjectType = $AssignmentRule/Dossier.Applicables_ObjectType_STP] [Dossier.Dossier_CommissionerItem=$ComissionarItem] [Dossier.Dossier_EstimatedDamage/Dossier.EstimatedDamage/Dossier.EstimatedDamage_CausePrimary=$PrimaryCauseItem] [AmountForAutoAssignment <= $AssignmentRule/ScaleMaxAmount and AmountForAutoAssignment >= $AssignmentRule/ScaleMinAmount] [Dossier.Dossier_AssignedExpert/Dossier.AssignExpert/Dossier.AssignExpert_Relation = $Relation] [Status != 'Aangevraagd' and Status != 'Concept'] [RequestDate >= $AssignmentRule/SourceDate] The database retrieve is pretty heavy for the server and consumes 100% of the processor! How should we model such a heavy retrieve? Thanks!
2 answers

This doesn't look like a complex XPath at all, are you certain this is the problematic retrieve?

Some general tricks:

  • Constain first at attributes of the entity and afterwards on joins (as generally, joins are more expensive)
  • Set indexes on the attributes you use to constrain.

Your query would then look like (and set indexes e.g. at Status, RequestDate and AmountForAssignment):

[Status != 'Aangevraagd']

[Status != 'Concept']

[RequestDate >= $AssignmentRule/SourceDate]

[AmountForAutoAssignment <= $AssignmentRule/ScaleMaxAmount]

[AmountForAutoAssignment >= $AssignmentRule/ScaleMinAmount]

[Dossier.DossierCategory = $AssignmentRule/Dossier.ApplicablesCategory]

[Dossier.DossierSubCategory = $AssignmentRule/Dossier.ApplicablesSubCategorySTP]

[Dossier.DossierObjectType = $AssignmentRule/Dossier.ApplicablesObjectTypeSTP]



[Dossier.DossierAssignedExpert/Dossier.AssignExpert/Dossier.AssignExpert_Relation = $Relation]


How many statusses are there? Maybe use (Status='a' or Status='b' or...) instead of useing !=, so the index can be used.

Is RequestDate indexed? With second attribute AmountForAutoAssignment and third status?

Which entities contain a lot of data?