LINQ and other performance issues

What I Found

So I have spent a lot of time with LINQ and performaing queries against both large and small amounts of data recently. I had the opportunity to do some performance tuning and found some very interesting things.

LINQ is a convienence would be my first finding. So, I originally wrote some code that went to the database for 1 million rows to reduce round trips to the db for a loop that would iterate over maybe 500K or less of the 1 million. I thought that by reducing db load (originally we had load issues) that I could use LINQ to do the queries and increase my performance. Well this worked for awhile, until we got our databases up to snuff. Then we started having issues with the CA being able to take our load. They just can't handle the load over the time period that we are trying to push it. So I started getting creative and looking for unique ways to get the load out to CA before our WCF services just give up and time out. I was looking for everything including the little micro-seconds I could shave off our side to get the data put together faster. Well, I found it. In the LINQ query where we searched 1+ million records, it would take 2 seconds to search for the 12 unique rows belonging to an individual product. So I played a bunch here and found out it was simply faster to just do database calls for each row. I removed LINQ and did lamda expressions on List objects and even went back to a for loop, but no luck. I will say that the for loop saw a little improvement.

Summary

I found the limit for an efficient Lambda expression or LINQ query was about 100K records in my case. So I have written some other test code with a larger number of column data and a smaller amount of column data. There doesn't seem to be much of a difference. So my summary is that using these new 3.5 features is a great convenience and can save some programming time and energy when you have about 100K records. Once you get over this limit, you should consider strongley why you are returning so many records. Consider paging at the database or only retrieving the records you need at run-time.