Why is Linq To Sql databinding to gridview much slower than pass-through SQL?
data-binding, linq-to-sql, sql
Solution
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.
Problem
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(); } ```