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

<p>I have an XAF app with a parent class called <code>MyParent</code>.</p>

<p>There are 230 records in <code>MyParent</code>.</p>

<p><code>MyParent</code> has a child class called <code>MyChild</code>.</p>

<p>There are 49,000 records in <code>MyChild</code>.</p>

<p>I have an association defined between <code>MyParent</code> and <code>MyChild</code> in the standard way:</p>

<p>In <code>MyChild</code>:</p>

<pre><code>// MyChild (many) and MyParent (one)
[Association("MyChild-MyParent")]
public MyParent MyParent;
</code></pre>

<p>And in <code>MyParent</code>:</p>

<pre><code>[Association("MyChild-MyParent", typeof(MyChild))]
public XPCollection&lt;MyCHild&gt; MyCHildren
{
     get { return GetCollection&lt;MyCHild&gt;("MyCHildren"); }
}
</code></pre>

<p>There's a specific <code>MyParent</code> record called <code>MyParent1</code>.</p>

<p>For <code>MyParent1</code>, there are 630 <code>MyChild</code> records.</p>

<p>I have a DetailView for a class called <code>MyUI</code>.</p>

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

<p>The user chooses <code>MyParent1</code> in the first drop-down.</p>

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

<p>Here is the code for the property:</p>

<pre><code>[NonPersistent]
public XPCollection&lt;MyChild&gt; DisplayedValues
{
    get
    {
        Session theSession;
        MyParent theParentValue;
        XPCollection&lt;MyCHild&gt; 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
    }
</code></pre>

<p>I marked the <code>DisplayedValues</code> property as <code>NonPersistent</code> 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.</p>

<p>The problem is that it takes 45 seconds to call <code>theParentValue = this.DropDownOne</code>.</p>

<p>Specs:</p>

<ul>
<li>Vista Business</li>
<li>8 GB of RAM</li>
<li>2.33 GHz E6550 processor</li>
<li>SQL Server Express 2005</li>
</ul>

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

<p>I took the time to sketch out the business case because I have two questions:</p>

<ol>
<li><p>How can I make the associated values load faster?</p></li>
<li><p>Is there another (simple) way to program the drop-downs and DetailView that runs much faster?</p></li>
</ol>

<p>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.</p>

<p>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.</p>

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

[{'answer_id': 78123, 'author': 'Tim Jarvis', 'author_id': 10387, 'author_profile': 'https://Stackoverflow.com/users/10387', 'pm_score': 3, 'selected': True, 'text': '<p>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.</p>\n\n<p>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.</p>\n'}, {'answer_id': 91582, 'author': 'MindModel', 'author_id': 6783, 'author_profile': 'https://Stackoverflow.com/users/6783', 'pm_score': 0, 'selected': False, 'text': "<p>Thanks for the answer.  I created a separate solution and was able to get good performance, as you suggest.</p>\n\n<p>My SQL connection is OK and works with other features in the app.</p>\n\n<p>Given that I'm using XAF and not doing anything extra/fancy, aren't my sessions managed by XAF?</p>\n\n<p>The session I use is read from the DetailView.</p>\n"}, {'answer_id': 1169654, 'author': 'Steven Evers', 'author_id': 48553, 'author_profile': 'https://Stackoverflow.com/users/48553', 'pm_score': 1, 'selected': False, 'text': '<p>I\'m unsure why you would be doing it the way you are. If you\'ve created an association like this:</p>\n\n<pre><code>public class A : XPObject\n{\n    [Association("a&lt;b", typeof(b))]\n    public XPCollection&lt;b&gt; bs { get { GetCollection("bs"); } }\n}\n\npublic class B : XPObject\n{\n    [Association("a&lt;b") Persistent("Aid")]\n    public A a { get; set; }\n}\n</code></pre>\n\n<p>then when you want to populate a dropdown (like a lookupEdit control)</p>\n\n<pre><code>A myA = GetSomeParticularA();\nlupAsBs.Properties.DataSource = myA.Bs;\nlupAsBs.Properties.DisplayMember = "WhateverPropertyName";\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 3474668, 'author': 'Tien Do', 'author_id': 108616, 'author_profile': 'https://Stackoverflow.com/users/108616', 'pm_score': 0, 'selected': False, 'text': "<p>I'm not sure about your case, just want to share some my experiences with XAF.</p>\n\n<p>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.</p>\n"}]
['https://Stackoverflow.com/questions/62436', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/6783/']