I have compared two queries which fetch some fairly large data from a database table. For query one I used Linq To Sql and for the other I use passthrough SQL via ADO.NET.
I know that Linq To Sql has to do a lot of work behind the scenes, but what is it actually doing? The two queries fetches the same amount of data but the Linq To Sql query is more than 5 seconds slower and uses 150mb more RAM!
Here is my test code:
Using Linq To Sql :
public void MakeList()
{
int start = Environment.TickCount;
var document = from d in _dm.tDokuments select d;
List<tDokument> documentList = document.ToList();
int end = Environment.TickCount;
GridView1.DataSource = documentList;
GridView1.DataBind();
Label1.Text = (end - start).ToString();
}
Passthrough SQL + ADO.NET:
public void MakeList()
{
int start = Environment.TickCount;
SqlCommand sqlCommand = new SqlCommand("SELECT * FROM tDokument", _connection);
SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
DataSet ds = new DataSet();
da.Fill(ds);
int end = Environment.TickCount;
GridView1.DataSource = ds;
GridView1.DataBind();
Label1.Text = (end - start).ToString();
}
Best Answer
Linq2Sql is returning strongly typed objects where as the dataset is getting populated with what essentially amounts to a hash table.
In Linq, the population of the data and the binding of that data to a GridView uses a lot of reflection to generate the desired results.
In the second piece of code, the data is being loaded into a dataset and binded to a GridView. This is essentially loading a hashtable with data and lookups to bind.
Hashtable operations are always going to be faster than reflection. With a small amount of data, there is not going to be a noticeable difference but for lots of data, you will see the impact of reflection in Linq.