CODEWITHSUNDEEP
c#performancelinqlist
user1402214
user1402214

Reputation: 61

C# Linq Performance with a Large list

I have an application that essentially takes one list (submitted by the user) and returns a set of data for each item in the list. My primary issue is that I have a large Collection that I have stored in memory to avoid the cost of having to query the database each time but my queries are still taking nearly .5 seconds each and that is far too long.

The collection is US and CA postal codes and has nearly 1 Million records. The list from teh user is anywhere from a few dozen to a few thousand records and i need to do this for each so it gets very time consuming with large lists. Here is the query:

var resultList = PostalCodeList.Where(p => p.postalcode == userPostalCode).ToList();

The postal Code list has other relevant data as well (country, region, etc.) that needs to be returned to the user as well. 5 fields in all. All strings.

there is the possibility with the way the data is organized that there are 2 results for a given value, so i need to account for that.

Any ideas or suggestions would be great. Thanks!

Upvotes: 1

Views: 4565

Answers (1)

Steve Lillis
Steve Lillis

Reputation: 3256

Was querying the database hurting performance, or did you cache the data here out of fear of the DB? If the latter, you might find that individual queries are better suited to your needs and should optimize only when you run into a DB performance problem.

That said, you may be using ToList() unnecessarily. If all you're doing with the result is foreach, then what you've done is enumerate the collection twice (once for ToList(), second for foreach). If possible, remove ToList() and instead work straight with the result of Where(). If the iteration of your list is the major overhead then you should find this cuts your lookup time in about half.

Other than that, as others have stated in their comments on your original question, you might find that switching to a methodology that supports fast lookups (i.e. hashsets) is the only way to completely sidestep the O(n) lookup time you'll encounter as the collection grows.

Upvotes: 3

Related Questions