DevExpress eXpressApp Framework (XAF) and eXpress Persistent Objects (XPO): how do I speed up the loading time of associations?

Question

I am having a problem with the speed of accessing an association property with a large number of records.

I have an XAF app with a parent class called MyParent.

There are 230 records in MyParent.

MyParent has a child class called MyChild.

There are 49,000 records in MyChild.

I have an association defined between MyParent and MyChild in the standard way:

In MyChild:

// MyChild (many) and MyParent (one)
[Association("MyChild-MyParent")]
public MyParent MyParent;

And in MyParent:

[Association("MyChild-MyParent", typeof(MyChild))]
public XPCollection<MyCHild> MyCHildren
{
     get { return GetCollection<MyCHild>("MyCHildren"); }
}

There's a specific MyParent record called MyParent1.

For MyParent1, there are 630 MyChild records.

I have a DetailView for a class called MyUI.

The user chooses an item in one drop-down in the MyUI DetailView, and my code has to fill another drop-down with MyChild objects.

The user chooses MyParent1 in the first drop-down.

I created a property in MyUI to return the collection of MyChild objects for the selected value in the first drop-down.

Here is the code for the property:

[NonPersistent]
public XPCollection<MyChild> DisplayedValues
{
    get
    {
        Session theSession;
        MyParent theParentValue;
        XPCollection<MyCHild> theChildren;

        theParentValue = this.DropDownOne;
        // get the parent value

        if theValue == null)
        {
            // if none

            return null;
            // return null
        }

        theChildren = theParentValue.MyChildren;
        // get the child values for the parent

        return theChildren;
        // return it
    }

I marked the DisplayedValues property as NonPersistent because it is only needed for the UI of the DetailVIew. I don't think that persisting it will speed up the creation of the collection the first time, and after it's used to fill the drop-down, I don't need it, so I don't want to spend time storing it.

The problem is that it takes 45 seconds to call theParentValue = this.DropDownOne.

Specs:

• Vista Business
• 8 GB of RAM
• 2.33 GHz E6550 processor
• SQL Server Express 2005

This is too long for users to wait for one of many drop-downs in the DetailView.

I took the time to sketch out the business case because I have two questions:

1. How can I make the associated values load faster?

2. Is there another (simple) way to program the drop-downs and DetailView that runs much faster?

Yes, you can say that 630 is too many items to display in a drop-down, but this code is taking so long I suspect that the speed is proportional to the 49,000 and not to the 630. 100 items in the drop-down would not be too many for my app.

I need quite a few of these drop-downs in my app, so it's not appropriate to force the user to enter more complicated filtering criteria for each one. The user needs to pick one value and see the related values.

I would understand if finding a large number of records was slow, but finding a few hundred shouldn't take that long.

Answer 1

Firstly you are right to be sceptical that this operation should take this long, XPO on read operations should add only between 30 - 70% overhead, and on this tiny amount of data we should be talking milli-seconds not seconds.

Some general perf tips are available in the DevExpress forums, and centre around object caching, lazy vs deep loads etc, but I think in your case the issue is something else, unfortunately its very hard to second guess whats going on from your question, only to say, its highly unlikely to be a problem with XPO much more likely to be something else, I would be inclined to look at your session creation (this also creates your object cache) and SQL connection code (the IDataStore stuff), Connections are often slow if hosts cannot not be resolved cleanly and if you are not pooling / re-using connections this problem can be exacerbated.

Answer 2

I'm unsure why you would be doing it the way you are. If you've created an association like this:

public class A : XPObject
{
    [Association("a<b", typeof(b))]
    public XPCollection<b> bs { get { GetCollection("bs"); } }
}

public class B : XPObject
{
    [Association("a<b") Persistent("Aid")]
    public A a { get; set; }
}

then when you want to populate a dropdown (like a lookupEdit control)

A myA = GetSomeParticularA();
lupAsBs.Properties.DataSource = myA.Bs;
lupAsBs.Properties.DisplayMember = "WhateverPropertyName";

You don't have to load A's children, XPO will load them as they're needed, and there's no session management necessary for this at all.

Answer 3

I'm not sure about your case, just want to share some my experiences with XAF.

The first time you click on a dropdown (lookup list) control (in a detail view), there will be two queries sent to the database to populate the list. In my tests, sometimes entire object is loaded into the source collection, not just ID and Name properties as we thought so depends on your objects you may want to use lighter ones for lists. You can also turn on Server Mode of the list then only 128 objects are loaded each time.

Answer 4

Thanks for the answer. I created a separate solution and was able to get good performance, as you suggest.

My SQL connection is OK and works with other features in the app.

Given that I'm using XAF and not doing anything extra/fancy, aren't my sessions managed by XAF?

The session I use is read from the DetailView.