Ticket Q558707
Visible to All Users

How do I prefetch related details data to increase performance for calculated fields

created 11 years ago

Dear support,
I’m working on a XAF project which is using Domain Components.
I have an order with details, I also have a few fields in order that displays sum fields from details.Something like this:

C#
[Calculated("Details.Sum(ToSend)")] int ToSend { get; }

Until now I was under the impression that if you use servermode for a listview and also use calculated fields, that everything is calculated on the server side.                    I found out with the profiler that a calculated field produced an extra 4 queries and that it’s much faster to use XPQuery to get the data in the get_field function.
E.g.

C#
public int Get_ToSend(IOrder instance, IObjectSpace space) { XPQuery<IDetail> q = new XPQuery<IDetail>(((XPObjectSpace)space).Session); _toSend = (from x in q where x.Order == instance select x).Sum(y => y.ToSend); }

This gives a very neat query with a select sum() and is faster also. But this still produces queries for every order row retrieved.
Is it correct that Calculated not always works on the server side? Or does it only works with Domain Objects?Is there another solution, so I can get one query for every order?I also try using Session.Prefetch but that doesn't make any difference.
Regards and happy xmas.

Comments (1)
Dennis Garavsky (DevExpress) 11 years ago

    Hello Rob,
    Domain Components is an abstraction layer above XPO, so there should not be any differences in behavior with regular persistent objects.
    Calculated properties configured via the Calculated or PersistentAlias attribute are always calculated on the database server side. I would need some additional time to research the exact queries produced by this code, though. Please bear with me.

    Answers approved by DevExpress Support

    created 11 years ago (modified 4 years ago)

    Calculated properties declared with an expression (using PersistentAliasAttributeCalculatedAttribute, or IModelMember.Expression) are evaluated on the database server side only when they are used in criteria that specifies a filter or sorting in data loading methods. When a calculated property of a business object is accessed directly, the property getter evaluates the expression in memory by loading related objects.

    In v14.1+, a List View can operate in the DataView Mode. In this mode, only data for declared columns is loaded from the database. If a column is mapped to a calculated property with an expression, the value is calculated on the database server side. See the List View Data Access Modes Overview topic for details.

    In previous versions, you can pre-fetch required details data using the following ViewControllers. This way, no extra queries will be sent to the database for each loaded record.

    WinForms:

    C#
    using System; using System.Linq; using System.Collections; using DevExpress.ExpressApp; using System.ComponentModel; using DevExpress.ExpressApp.Xpo; using System.Collections.Generic; using DevExpress.ExpressApp.Win.Editors; namespace YourSolutionName.Module { public partial class ViewController1 : ViewController<ListView> { public ViewController1() { TargetObjectType = typeof(YourSolutionName.Module.BusinessObjects.Master); } protected override void OnActivated() { base.OnActivated(); GridListEditor listEditor = ((ListView)View).Editor as GridListEditor; listEditor.GridDataSourceChanging += ListEditor_GridDataSourceChanging; } private void ListEditor_GridDataSourceChanging(object sender, EventArgs e) { PrefetchCollections(); } private void PrefetchCollections() { DevExpress.Xpo.Session session = ((XPObjectSpace)ObjectSpace).Session; IEnumerable toPrefetch = View.CollectionSource.Collection as IEnumerable; if(View.CollectionSource.Collection is IListSource) { IList list = ((IListSource)View.CollectionSource.Collection).GetList(); List<object> temp = new List<object>(); for(int i = 0; i < list.Count; i++) { temp.Add(list[i]); } toPrefetch = temp; } if(toPrefetch != null) { session.PreFetch(session.GetClassInfo(View.ObjectTypeInfo.Type), toPrefetch, "Details"); } } } }

    Web:

    C#
    using System; using System.Linq; using System.Collections; using DevExpress.ExpressApp; using System.ComponentModel; using DevExpress.ExpressApp.Xpo; using System.Collections.Generic; namespace YourSolutionName.Module { public partial class ViewController1 : ViewController<ListView> { public ViewController1() { TargetObjectType = typeof(YourSolutionName.Module.BusinessObjects.Master); } protected override void OnActivated() { base.OnActivated(); PrefetchCollections(); } private void PrefetchCollections() { DevExpress.Xpo.Session session = ((XPObjectSpace)ObjectSpace).Session; IEnumerable toPrefetch = View.CollectionSource.Collection as IEnumerable; if(View.CollectionSource.Collection is IListSource) { IList list = ((IListSource)View.CollectionSource.Collection).GetList(); List<object> temp = new List<object>(); for(int i = 0; i < list.Count; i++) { temp.Add(list[i]); } toPrefetch = temp; } if(toPrefetch != null) { session.PreFetch(session.GetClassInfo(View.ObjectTypeInfo.Type), toPrefetch, "Details"); } } } }

    Take special note that this code affects only initial loading and we do not have a good solution for the scenario when the ListView is refreshed via an Action or in code in an XAF Web application.

      Show previous comments (3)
      Dennis Garavsky (DevExpress) 8 years ago

        @Matthieu Penant: I've created a separate ticket on your behalf (T523799: Questions about XPO queries for related details data). It has been placed in our processing queue and will be answered shortly.

        FS FS
        Fabio Scarvaglieri 7 years ago

          Dear Dennis,

          we improved the loading performance for our application using Prefetch similar as described here.
          You mentioned here: "Take special note that this code affects only initial loading and we do not have a good solution for the scenario when the ListView is refreshed via an Action or in code in an XAF Web application."

          So that worked well for people using the windows application using UIType SingleWindowSDI, because each load is an initial load and Reload is not used.
          However we have now the case, that people want to use the TabbedMDI mode and then they are using the Refresh Action.
          While inital loading for example was improved to 6 seconds loading time, the Refresh takes about 30 seconds or longer.
          We did analyse the queries using XpoProfiler and put a breakpoint into the PreFetch code.
          PreFetch code is executed, only if the ListView is newly opened from the NavigationItem.
          Refresh iterates trough all shown shown records and executes by far more queries resulting in the slow loading time.

          So the question would be, could we provide a custom Refresh for some ListViews, so that it would use the same loading, as happening inital from the NavigationItem?

          You find attached a screen recording, where we first click on the Orders NavigationItem for inital load, which takes 6 seconds, then we click Refresh, which takes till the end of the screen recording (much longer).
          If we first remove the filter, then Refresh and after that we add the filter back, the total loading time is the same like the Refresh.
          It is fast only with the loading from NavigationItem.

          We also attached the logs from XpoProfiler for both cases.

          Thank you very much ahead

          Dennis Garavsky (DevExpress) 7 years ago

            @Fabio Scarvaglieri:
            I've created a separate ticket on your behalf (T657646: Custom Refresh Action to improve performance through prefetching related object details). It has been placed in our processing queue and will be answered shortly.

            Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

            Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.