Why is linking Linq To Sql to gridview so much slower than pass-through SQL?

Question

I compared two queries that extract some pretty big data from a database table. For the query, I used Linq To Sql, and for the other, I use Passthrough SQL through ADO.NET.

I know that Linq To Sql should do a lot of work behind the scenes, but what does it actually do? Two queries retrieve the same amount of data, but the Linq To Sql query is more than 5 seconds slower and uses 150 MB of additional memory!

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(); } 

Answer 1

Linq2Sql returns strongly typed objects when the data set is populated with what essentially constitutes a hash table.

In Linq, aggregating data and linking this data to a GridView uses many reflections to produce the desired results.

In the second part of the code, the data is loaded into the data set and attached to the GridView. This essentially loads the hash table with data and searches for binding.

Hashtable operations will always be faster than reflection. With a small amount of data there will be no noticeable difference, but for a large amount of data you will see the effect of reflection in Linq.

Answer 2

Have you looked at the actual SQL sent to SQL Server with Profiler?

In this case, I suspect that the client is processing it (DataSet vs List), which makes a difference, but I'm nowhere near the C # expert.

Answer 3

Capture and parse the SQL statement that is sent through the explorer in the Linq To Sql example. SQL Profiler will do the trick.

Run both statements from Example 1 and 2 directly with your SQL Server using Management Studio. You probably won't see ANY significant difference in terms of request.

I think that most of the time is spent on creating C # objects ( Jason answers this, it seems to me).

Answer 4

Linq to Sql should find out from your code which query to execute for the database, then it should convert the query results to strongly typed objects, which means that casting will be performed. These are two things that your version of DataSet does not.

If you are interested in the performance of Linq to Sql, you can use compiled queries , which eliminates the need for interpretation at runtime.

Answer 5

Your measure may not be accurate, use the System.Diagnostics.StopWatch class to measure time:

 static void Main(string[] args) { var sw = Stopwatch.StartNew(); Thread.Sleep(100); Console.WriteLine(sw.Elapsed.ToString()); Console.Read(); }