Wednesday, April 28, 2010

Performance issues with an in-memory Datatable select clauses

Very often, we cache .NET Datatable objects in memory. This could be for lookup tables or for other frequently accessed data. The following article on MSDN points out that using 'select' on a Datatable can be very very slow.

http://msdn.microsoft.com/en-us/library/dd364983.aspx

Snippet from the article:
"Select takes arbitrary criteria and returns an array of DataRows. Essentially, DataTable.Select has to walk the entire table and compare every record to the criteria that you passed in. "

The performance stats for LINQ in the article look very impressive. Looks like Microsoft has made a lot of optimizations on LINQ. It would be great to know (see source code) of what happens behind the scenes and why LINQ is so damn fast!