1
\$\begingroup\$

Intro

I am trying to populate a Multi-column Combo-box with a large amount of records.Depending on the selection the user has taken there can be 1 - 50000 items in the Combo-box (I may not display all these items at once, but i still have to process them all).

I am using entity framework to get a list of objects and i am then creating an anonymous type with a few columns from the database but i am also adding a calculated column which decreases performance. This calculated column is an implementation of the Levenshtein distance for comparing some user input to a column in the entity object.

Doing this using a single thread can take around 120 seconds, so i decided to use the Task parallel library to split up the data and perform the calculation in chunks and then join the results together.

The Code

using (Models.CmdpEntities entity = new Models.CmdpEntities())
    {
        IOrderedQueryable<Models.Establishment> establishments = entity.Establishments
            .Where(establishment =>
            establishment.country == "IT" &&
            establishment.source == "ITRACK").OrderBy(e => e.Estab_key);

        var finalList = establishments.Take(1).ToList().Select(

            establishment =>
                    new
                    {
                        establishment.Estab_key,
                        establishment.iTrackId,
                        establishment.Institution,
                        establishment.address_1,
                        establishment.city,
                        establishment.postcode,
                        establishment.country,
                        establishment.province,
                        Levenshtein = DMS.Matching.Levenshtein.Compare(establishment.Institution, "Test institution").ToString()
                    }
            ).ToList();

        finalList.Clear();

        int count = establishments.Count();

        int divided = count / 10;
        int remainder = count % 10;

        Task[] tasks = new Task[10];

        for (int i = 0; i < 10; i++)
        {
            int numberToSkip = i * divided;
            int numberToTake = divided;

            if (i == 9)
                numberToTake += remainder;

           tasks[i] = Task.Factory.StartNew(() =>
            {
                finalList.AddRange(
                establishments.Skip(numberToSkip).Take(numberToTake).ToList().Select(establishment =>
                    new
                    {
                        establishment.Estab_key,
                        establishment.iTrackId,
                        establishment.Institution,
                        establishment.address_1,
                        establishment.city,
                        establishment.postcode,
                        establishment.country,
                        establishment.province,
                        Levenshtein = DMS.Matching.Levenshtein.Compare(establishment.Institution, "Test institution").ToString()
                    }).ToList()); 

            });
        }

        Task.WaitAll(tasks);
}

Code walkthrough

I wrap everything in a using statement for my entity context. I then create the "establishments" object with returns an ordered result of my entity based on the Where condition. This will have all the records i need but they wont have the correct selection as i am going to split the results into batches for different Tasks to use when calling "Select".

I next create the "finalList" variable and i set it to my anonymous type. I only take 1 as this is just a declaration of my anonymous type so that i can use it to add the results from each of my tasks in. I am not sure if this is a good way to declare a list for anonymous types but it was the easiest i could think of. Could there be better ways out there?

I then call "Clear" on the "finalList" as I only inserted data in there to begin with so it would recognize the variable as a List of my anonymous type.

I then get the number of items i will process in each task ("divided" variable) and the remainder so i know how many extra items the last task should process.

Next i have my for loop that creates a Task object for each batch of the data i need to process and then i make the selection including a call to my "Compare" method that using Levenshteins distance to return a value. I am splitting it into 10 tasks however i am unsure if this is a efficient amount.

I have to call "ToList" before making the selection as else my Compare method will fail as its not recognized in linq-to-entities.

Finally i wait for all tasks to finish. A few tests should some good improvement (down around 30 seconds to process 45 thousand records) however im not sure if there is anything i can do to improve it further.

Conclusion

I have a few concerns with if i am handling anonymous types and using the TPL (task parallel library) efficiently and with best practices in mind. Such as if i am using the right amount of Tasks?

I am not sure if i am going about this the right way any feedback would be much appreciated.

\$\endgroup\$
  • \$\begingroup\$ This comment is going to be more fitting on UX.StackExchange, but ....what use is a ComboBox with 50K items in it? \$\endgroup\$ – Mathieu Guindon May 29 '14 at 16:32
  • \$\begingroup\$ This comment on an old unanswered SO question provides an interesting work-around. \$\endgroup\$ – Mathieu Guindon May 29 '14 at 16:34
  • \$\begingroup\$ Also EF isn't the fastest at materializing results - you might want to take a look at Daper .net. I presume part of the bottleneck is materialization, I'd give Daper a try. \$\endgroup\$ – Mathieu Guindon May 29 '14 at 16:37
  • 1
    \$\begingroup\$ @Mat'sMug Thanks for the feedback, I should of been clearer but the Combobox won't show all items at once, not final yet but it may show just the top 1000 based on the Levehstein string compare value and perhaps we will add the ability to load more. \$\endgroup\$ – user2945722 May 29 '14 at 17:42
  • \$\begingroup\$ If your SQL Server has CLR/SQL enabled, I'd try to maybe make DMS.Matching.Levenshtein.Compare available on the database backend to perform the filtering before returning the records.. but that could also mean calling a stored procedure from EF instead of using Linq-to-Entities (which I don't see as a problem). If you make the SP return column names that exactly match those of Models.Establishment, all you need is a [ComplexType] attribute on your model and EF maps it automagically, calling the SP gives you a IEnumerable<Models.Establishment> to play with :) \$\endgroup\$ – Mathieu Guindon May 29 '14 at 18:41
1
\$\begingroup\$

One of the reasons behind var appearing in the C# language at the same time as LINQ, was specifically to avoid this:

IOrderedQueryable<Models.Establishment> establishments = entity.Establishments
    .Where(establishment =>
    establishment.country == "IT" &&
    establishment.source == "ITRACK").OrderBy(e => e.Estab_key);

Consider this instead (misaligned indentation only to prevent horizontal scrolling):

var establishments = entity.Establishments
                           .Where(establishment => 
                                establishment.country == "IT" &&
                                establishment.source == "ITRACK").OrderBy(e => e.Estab_key);

You're sending the establishments query over to the SQL backend quite a few times:

  • var finalList = establishments.Take(1).ToList()
  • int count = establishments.Count();
  • and then in a loop: establishments.Skip(numberToSkip).Take(numberToTake).ToList()

I'd try to hit the database server only once:

var establishments = entity.Establishments
                           .Where(establishment => 
                                establishment.country == "IT" &&
                                establishment.source == "ITRACK").OrderBy(e => e.Estab_key)
                           .ToList();

Then int count = establishments.Count(); becomes int count = establishments.Count;, you no longer resend the query over, and you're not iterating it to get a count.

Multiple iterations of an IEnumerable<T> is a bad thing, ReSharper recommends calling .ToList() if multiple iterations are needed.


Spawning 10 tasks seems like an arbitrary number, which may or may not be appropriate.

I think I'd try to use some PLINQ instead (given establishments is List<Model.Establishment>):

var finalList = establishments.AsParallel()
                              .Select(e => new { /* anonymous type */ })
                              .ToList();

Also, List<T> not being thread-safe, I would avoid adding to a list on a background thread the way you're doing it.

\$\endgroup\$
  • \$\begingroup\$ Thanks for the advice. Calling toList at the start had a significant improvement. Calling AsParallel instead of spawning tasks is probably the safer way to go however i didn't see any performance gains over spawning 10 tasks. 10 was a random number i picked, still investigating how to pick the most efficient number. \$\endgroup\$ – user2945722 May 30 '14 at 11:51
  • \$\begingroup\$ If there's no performance gain (and no loss), but there's a readability gain, I'd go with the more readable/shorter code - thanks for the checkmark! :) \$\endgroup\$ – Mathieu Guindon May 30 '14 at 12:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.