We have an object which has a collection of 300 childs. The Child state property causes the master object state change. We use the aggregate attribute but when we open the master object and save all childs are selected one by one, not 1 query which selects all. So we generate a performance hit on sql. How can we prevent this.
Validation performance - PersistenceValidationController and the Aggregated attribute cause selecting child records one by one on saving the master object
Answers approved by DevExpress Support
The default behavior of the Validation Module is to validate all aggregated objects when the current object in the detail view is being validated. So, it loads all aggregated collections when the view is saved for the first time.
1. You can remove the Aggregated attribute from your associated collection property.
2. You can manually specify which aggregated objects should be validated. To do this, use the PersistenceValidationController.CustomGetAggregatedObjectsToValidate event. Here is an example of how to validate only modified objects from the Contact.PhoneNumbers collection:
C#public class ViewController123 : ViewController {
public ViewController123() {
TargetViewId = "Contact_DetailView";
}
protected override void OnActivated() {
base.OnActivated();
Frame.GetController<PersistenceValidationController>().CustomGetAggregatedObjectsToValidate += ViewController123_CustomGetAggregatedObjectsToValidate;
}
void ViewController123_CustomGetAggregatedObjectsToValidate(object sender, CustomGetAggregatedObjectsToValidateEventArgs e) {
if (e.OwnerObject is Contact) {
foreach (PhoneNumber number in ((Contact)e.OwnerObject).PhoneNumbers) {
if (ObjectSpace.IsObjectToSave(number)) {
e.AggregatedObjects.Add(number);
}
}
}
e.Handled = true;
}
protected override void OnDeactivated() {
base.OnDeactivated();
Frame.GetController<PersistenceValidationController>().CustomGetAggregatedObjectsToValidate -= ViewController123_CustomGetAggregatedObjectsToValidate;
}
}
3. You can skip a certain rule using the RuleSet.CustomNeedToValidateRule event. See an example in the RuleSet.CustomNeedToValidateRule Event topic.
See Also related articles
Validation - Rules for aggregated child objects are checked when saving a master object in a Detail View
Validation - Validate rules' aggregated objects if their master object is changed when ListView has MasterDetailMode = ListViewAndDetailView
Normally, these records should not be selected in separate queries. Probably, you meant the UPDATE command. If not, please provide the code of your business classes and the queries log for research.
We have an "Order" object and it has several "Activity" objects which are aggregated. When we use ObjectSpace.ReloadObject(order), it queries all the activities 1 by 1 rather than reloading the entire collection in 1 query.
Yes, the XPObjectSpace.ReloadObject method reloads all objects from all aggregated collections. Please clarify whether you are calling this method manually, or this is done by XAF in some event. If former, clarify the purpose of calling this method in this case and describe the expected result.
I understand, but why isn't this done in 1 query? Why does it query every single activity individually. When we have 1300 activities it queries top 1 1300 times. Why not just "select * from activities where orderid = '{orderid}'? When the collection hasn't been loaded before it does query in that manner, but when it has already been loaded it queries them individually. This seems inefficient and it is hurting our performance at the moment.
In this case, records are already cached and thus it is necessary to reload each object individually to replace cached values with values from the database. Please describe your scenario in greater detail so that we can suggest you an alternative solution.
Hi, Anatol.
I observe similar behavior.
My scenario: open a detailview, make changes in the properties of an object, click the Save button.
None of the object from the aggregated collection I do not change.
Is it possible to improve the performance of the save operation if an aggregated collection are exists?
I cannot reproduce this one-by-one aggregated record selection on the Save button click in my tests. I suppose that it may occur if there is code that loads the aggregated collection immediately after the parent object is loaded, something like this:
protected override void OnLoaded() { base.OnLoaded(); int count = PhoneNumbers.Count; }
In this case, describe what task this code implements so that we can find an alternative solution. Otherwise, provide a sample project where the issue can be reproduced.
As I see it, I found the reason. For one of the fields (for example, OrderNumber) of child object attribute is used:
[RuleUniqueValue("", DefaultContexts.Save, CriteriaEvaluationBehavior = CriteriaEvaluationBehavior.BeforeTransaction)]
As a consequence, when parent object changes are saving in detailview, sql queries generated for each child like this:
exec sp_executesql N'select top 1 count(*) from "dbo"."Order" N0
where (N0."GCRecord" is null and (N0."OrderNumber" = @p0) and not ((N0."Oid" = @p1)))',N'@p0 nvarchar(4000),@p1 uniqueidentifier',@p0=N'158719',@p1='13104D28-2392-40D4-8D02-A308981ADF27'
Is it possible to improve performance by retaining an attribute?