Linq-to-SQL Data Rate Comparison

Question

Can someone tell me why Linq-to-SQL retrieves data so fast?

I conducted performance tests with Linq-to-SQL , EF4 , DataSet and MyORM .

Linq-to-SQL always faster, see the table below:

• Linq-to-SQL = 570 milliseconds to retrieve 50,000 records
• DataSet = 2.100 milliseconds to retrieve 50,000 records
• EF4 = 1,200 milliseconds to retrieve 50,000 records
• MyORM = 700 milliseconds to retrieve 50,000 records

I used SQL Server 2005 (local) and one table for analysis.

MyORM was implemented by me. It is a simple method that retrieves data using DataReader and Fasterflect to populate the properties of IList of T

What is the Linq-to-SQL strategy for this performance?

Answer 1

LINQ to SQL performs a number of tricks. If your own ORM uses reflection, you need to cache it, but I suspect that the difference may be that you are not caching ordinals.

eg. instead

 while(dataReader.Read()) { ... myNewObj.SomeProperty = dataReader.GetInt32(dataReader.GetOrdinal("SomeField")); ... } 

create a variable for each ordinal and refer to it inside your loop:

 var someFieldIdx = dataReader.GetOrdinal("SomeField"); ... while(dataReader.Read()) { ... myNewObj.SomeProperty = dataReader.GetInt32(someFieldIdx); ... } 

It would be even better to cache them on some static variables.

If you are already doing this, you may want to publish part of your ORM code that creates the objects.