re: Loving LINQ

Bart Czernicki — Thu, 01 Nov 2007 00:40:43 GMT

Nice intro article. However, ur paragraph on the bottom, couldn't be further from the truth about large collections :)

My tests show 100x improvements when working with LINQ in memory with larger collection sets (> 200,000 rows), without any optimizations!!!

- add in custom indexes (i.e. Hash indexes)

http://blog.magenic.com/blogs/aarone/archive/2007/04/09/Introducing-i4o.aspx

- PLINQ (coming soon to a developer near u, some people aready have the bits)

http://spellcoder.com/blogs/bashmohandes/archive/2007/10/14/8530.aspx

- make ur own custom Expression trees optimized

You get even faster results! PLINQ and hash indexes are just sick on performance. Check out the SQL 2005 Optimizations book on how SQL handles Hash joins and how they are preferred for large data sets and u can do the same for LINQ!!!

I have queries that take 6 seconds to run in SQL Server coming back in 27ms...(no I am not missing a zero)

re: Loving LINQ

simonince — Fri, 02 Nov 2007 13:35:22 GMT

Yes, I should probably have qualified that comment a bit further! I take your points - and I have to say the indexing ideas are very interesting, and potentially fantastically useful in some situations - but I'd still advise caution.

My main concern is in multi-user scenarios - e.g. ASP.NET. The idea of many users pulling back 200,000 objects each, joining them in memory concurrently, etc, etc... watch that scalability plunge! I'm keen that people understand what LINQ is doing - when it is optimising a SQL query, and when it is just operating in memory, as misunderstanding this could leave you with a headache.

On the other hand, database servers (although incredibly advanced) are not magical - the fact is they have to do a lot of processing that can be simulated in .NET code... so if you write something approaching the same algorithms, but don't have the overhead of network or cross process communication, serialization, and so on, I can completely believe that in some scenarios you can beat a SQL server.

We also shouldn't forget that there is no like-for-like comparison here. SQL can balance reads with writes, can scale up massively, has availability and recovery options, etc, etc... so as usual I suppose it is a case of picking your approach according to the best fit at the time!