BuyLog In
Ticket T119562
Visible to All Users

XpoDataSource in server mode and Prefetch

created 11 years ago

I've read a lot about prefetch and now I'm facing a problem. I've got calculated properties in my object that ABSOLUTELY need to be used in a ASPxGridView bound to a XpoDataSource. I can't make it a persistent calculated property because it would require me to recalculate it for a a 2.000.000 records database. I didn't have performance problems when the database was small, but now that the database is growing and growing, the exatraneous, SELECT N+1 queries that XPO make to the database for each row just to read the calculated properties (which reference a details collection) is just adding up and making the page unresponsive in first load and each grid pagination.

The calculated property issues a COUNT against a detail colllection, so, for each row displayed on the page, it makes an extra call to the database to count how many detail records there are for that specific row so that the column can be shown.

I was trying to call PreFecth as we do with XPCollection, but there I've found nothing of the sort to "tell" the Session/Xpo that it needs to prefetch that specific collection, so that the calculated property won't require an extra hit to the database…

How can I achieve that?

Sign in to comment on this post

Answers approved by DevExpress Support

created 11 years ago

Hello Felipe,

Thank you for describing your scenario in detail. We can offer you two good technical solutions, which are free of the shortcomings of your current approach:

  1. Use the LinqServerModeDataSource  with an XPQuery that  will retrieve required data including your count. This way there will be a single query to the database and no details collections will ever be loaded.
  2. Subscribe to the Session.ObjectsLoaded event and fetch details collections of the required persistent objects.
    Sign in to comment on this post

    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.