1

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!

Improve this question
8
  • how about showing the query that you are using to populate and or return the data that the list will utilize.. also have you checked to see if the Database is optimized for that particular table(s) are there Indexes or missing Indexes that need to be added..etc...?? Commented Dec 22, 2014 at 17:44
  • I'd use a Dictionaryto store the information (instead of a list). Commented Dec 22, 2014 at 17:45
  • @DJKRAZE for what he says, the data the list uses is in memory so the program doesn't have to query the database for each element on the received list Commented Dec 22, 2014 at 17:45
  • 1
    use a dictionary or hashset, which are made for fast lookups Commented Dec 22, 2014 at 17:45
  • 8
    I think your basic assumption is incorrect. You're not necessarily speeding up processing by keeping everything in local memory. Lookups like this are what databases are designed to handle. Unless you're accessing the database using TCP/IP over pigeons I would just let the database do what it was designed to do. Commented Dec 22, 2014 at 17:48

1 Answer 1

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

And if they had found the database to be too slow, I'd wonder if they had suitable indices on the tables for the queries.
I wish that was the case but i have been at this from every angle i can think of. Every database option seems to be too slow for the task (almost 45 seconds per 1,000 records) even with indices. I will try the Hashset and see dwhat kind of progress we can make. Thanks!
When you profiled the execution plan for your SQL query, what was causing that timing? It seems unreasonably high.
it was the format of the data. becuase I have no direct control of the data structure, or the particular data that I need for this app I have to do 4 joins and 2 case statements to get the data the way i need. Its not ideal but its what I need. thats why i opted to load the whole list beforehand.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.