Best practice for large lookup tables in comboboxes

[Dennis Gundersen]

Hi

I have one lookup table with 7.000+ components that very rarely change. I use that table in a combobox in my pre-ORM version of a project. The combobox has filtering and first match turned on, so that when I start writing a name the fitting components are listed, making it easy to choose the right one.

The problem is that this is very slow. First of all, the whole list is loaded every time I add a new record that includes a component, and the filtering is quite slow as well.

Is there a better way to do this? I was thinking maybe I could load the lookup table into an enumeration or struct (never used either before as I'm new to OOP). I assume they would remain in memory so that I can reuse them for the next record, which should make things go faster.

But then, there will also be a webapplication GUI for this application, and since HTTP isn't continuously connected the enum would probably go out of memory, or at least the reference to the object will be lost when the page reloads.

How do good programmers solve this type of problems?

Re
Dennis

[Jordan]

Hello Dennis Gundersen,

About the slow filtering, it seems like in order to do the filtering you are using some LINQ query on a IQueryable instance and this results in a request to the database probably on every key stroke (depends on how the filtering is implemented). If this is the case it is no surprise that filtering is slow.

In order to have the filtering perform as expected, you will have to load the data in a collection (for example a list) only one time and then use this for the filtering functionality so that you do not have requests to the database while filtering. Keeping this list in memory should not be an issue with 7000 items.

Regarding the other issue that you mention, I am not sure that I completely understand what you mean with "the whole list is loaded every time I add a new record that includes a component". Could you please share more details about your case. A sample project that demonstrates the issue will be perfect.

I hope this helps. Do not hesitate to write again if you have more questions.

All the best,
Jordan
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Dennis Gundersen]

Hi

My first attempt at a website doesn't use LINQ. I copied the Telerik demo that uses tabs inside a gridview and added new gridviews to each tab. The "parent" gridview shows people and the different gridviews in tabs shows addresses, educations etc (it's a CV application mostly). The problem tab is a list of all the technical components each person has experience with and consists of start and end dates plus the combobox where I choose the relevant component. That combobox is connected to a table using an object datasource. It makes sense as you say that each keystroke starts a new search which would explain the slow responses.

I'm currently trying to learn OOP in order to improve the website (and I also want to make a windows app for managers with added functionality). I'll try to use a list in the new solution as you suggested.

My question was if I need to recreate that list every time the gridview adds a new row, but if I include ajax I guess the list will remain in scope as there is no page refresh. That should significatly improve the useability.

Thank you.

Re
Dennis

[sitefinitysteve]

Try this

Combo Box Definition:

    <telerik:RadComboBox ID="RadComboBox1" runat="server" EnableLoadOnDemand="true" EnableVirtualScrolling="true">
        <CollapseAnimation Type="None" />
        <ExpandAnimation Type="None" />
        <WebServiceSettings Path="~/DynamicPickerService.asmx" Method="GetData />
    </telerik:RadComboBox>

Now create an asmx webservice

[WebMethod(CacheDuration = 0)]

public RadComboBoxData GetData(RadComboBoxContext context) {

RadComboBoxData result = new RadComboBoxData();

IObjectScope scope = ObjectScopeProvider.GetNewObjectScope();

var rotations = from rotation in scope.Extent<Rotation>()

where rotation.RotationName.Contains(context.Text)

orderby rotation.RotationName

select new RadComboBoxItemData {

Text = rotation.RotationName,

Value = rotation.RotationID.ToString()

};

//Perform the paging

int numberOfItems = context.NumberOfItems;

List<RadComboBoxItemData> rotationArray = rotations.Skip(numberOfItems).Take(20).ToList<RadComboBoxItemData>();

//This will execute the database query and return the data as an array of RadComboBoxItemData objects

result.Items = rotationArray.ToArray<RadComboBoxItemData>();

//Insert Highlighting

if (!String.IsNullOrEmpty(context.Text)) {

foreach (RadComboBoxItemData item in result.Items) {

item.Text = this.Highlight(item.Text, context.Text);

}

}

int endOffset = numberOfItems + rotationArray.Count();

int totalCount = rotations.Count();

//Check if all items are populated (this is the last page)

if (endOffset == totalCount)

result.EndOfItems = true;

//Initialize the status message

result.Message = String.Format("Items <b>1</b>-<b>{0}</b> out of <b>{1}</b>",

endOffset, totalCount);

scope.Dispose();

return result;

}     public string Highlight(string Search_Str, string InputTxt) {         MatchEvaluator myEvaluator = new MatchEvaluator(this.ReplaceKeyWords);         // Setup the regular expression and add the Or operator.           //Regex RegExp = new Regex(Search_Str.Replace(" ", "|").Trim(), RegexOptions.IgnoreCase);         // Highlight keywords by calling the delegate each time a keyword is found.           Regex RegExp = new Regex(InputTxt, RegexOptions.IgnoreCase);         return RegExp.Replace(Search_Str, myEvaluator);         // Set the RegExp to null.       }     public string ReplaceKeyWords(Match m) {         return "<span class='hlt'>" + m.Value + "</span>";     }

OA Modified version of this ...I have quite a few items to pick in my table as well, so this lets the combo grow dynamically...and do a google-like search filter

This is cut\pasted from one of my projects so I hope it works without many modifications :)

[Jordan]

Hello Dennis,

I am sorry for the late reply, but as you probably know the Q1 2010 release is very close now and we are quite busy preparing it.
Anyway, Steve's solution is very good and will allow for fast loading of your page, because you will not have to pass all 7000 items when the page loads.
And if you want to make less connections to the database you can always cache the data. Of course if you choose to implement some form of caching you will also need to implement some cache update strategy so that your users work with the latest data.
You could look at OpenAccess' caching functionality; here are some links that may help:
http://blogs.telerik.com/openaccessteam/posts/10-01-21/openaccess_orm_second_level_cache_api.aspx
http://www.telerik.com/help/openaccess-orm/2nd-level-cache.html

Do not hesitate to write again if you have more questions.

Kind regards,
Jordan
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[sitefinitysteve]

Yeah I should mention I use OpenAccess to populate mine...