My "Ah-Ha!" Moment With LINQ

I'm currently working on a set of web services that will be consumed by iPhone and Android devices. Given how often the web services will be called in a relatively short period of time, the data access for the web services has proven to be a very important aspect of the project. In choosing the technology stack for implementation, I opted for LINQ to SQL as it was something I had dabbled with in the past and wanted to learn more about in a real environment. The query optimization happening behind the scenes was something that I had an appreciation for.

Once I completed the implementation of the web services we noticed a performance bottleneck on one of the calls. LINQ to SQL was doing all it could to help us in this situation but the nature of the required response was simply too heavy on the database to expect any real performance from any query. We ended up being able to cache the data we were querying against which would help tremendously.

What I was expecting was a large re-write of code to accommodate my new source of data. Instead, using LINQ, I was able to simply change the from statement in my LINQ query and get the results I needed. Essentially, when building the cache, I create a List collection for the objects I'll need to query against and subsequently return to the mobile device. The results of that general query are stored in cache. When I need to perform a query against that cached collection, I have a LINQ query, which I used previously to query the database tables, that now uses the cached collection as it's data source. Which is really a fancy way of saying the "from" statement in the LINQ query. There were no ugly nested loops with various comparison statements needed.

Going from querying a database to querying a local in-memory collection was beyond trivial with LINQ. After getting it up and working, I fell back into my chair in amazement.

3 Comments

  • I love LINQ in general!
    I Also use the same technique as you :D
    And LOT'S of cache

  • "LINQ to SQL was doing all it could to help us in this situation but the nature of the required response was simply too heavy on the database to expect any real performance from any query."

    I know it is not the point of this post, but could you please post the query (or some sanitized, generalized version of it) that was causing the performance problem?

  • RussellH,

    I should elaborate on that quote.

    What we're doing is taking a list of contacts (multiple emails and phone numbers for each, first and last names) and attempting to find any matches in the database. Ideally, I could have appended to the LINQ to SQL query wrapping each block of logic in an OR statement. So, logically, (where a = 'a' and b = 'b' or c in c or d in d) OR (where...), etc. I could not find a solution to doing that with LINQ to SQL. So instead, I was running a query for each contact sent, and that was the source of the performance problem.

    We scaled back our original query to only search against phone numbers and email addresses. This allows me to generate a single query to the database. While still time consuming, it's far better than what I resorted to previously.

Comments have been disabled for this content.