Performance comparison of queries in SQL Server, ADO.NET and LINQ-to-SQL

Question

I'm wondering about the performance of the SQL queries executed in SQL Server and used in ADO.NET and LINQ-to-SQL.

I use the AdventureWorks database with extended Sales.SalesOrderDetailEnlarged table which has almost 5 million rows. Executing a query

select * 
from Sales.SalesOrderDetailEnlarged

lasts about 37 seconds in SQL Server, but execution of the same query in ADO.NET is about 21 seconds. To measure execution time I use Stopwatch and SQL Server Profiler.

// this is how I perform command execution in ADO.NET
using (SqlConnection sqlConnection = new SqlConnection(GetConnectionString()))
{
    sqlConnection.Open();
    DataSet table = new DataSet();

    using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(commandQuery, sqlConnection))
    {
        //stopwatch start
        sqlDataAdapter.Fill(table);
        //stopwatch stop
    }
}

In case of Linq-to-SQL, I have such a query which is equivalent to query used in ADO.NET. Execution time of query using Linq-to-SQL lasts about 12-13 seconds

var query = from salesOrderDetail in dataContext.SalesOrderDetailEnlargeds
            select salesOrderDetail;

//stopwatch start
query.ToList();
//stopwatch stop

Could someone explain:

Shouldn't query execution in SQL Server be faster than ADO.NET?
How to compare time execution of this query for ADO.NET and Linq-to-SQL? Linq-to-SQL is actually a layer on top of ADO.NET, so why is it faster than ADO.NET (or maybe I try to measure it in the wrong way)?

In which order do you execute the queries? Are you considering that there is some kind of caching Sql Server side? — Steve, Oct 06 '19 at 17:00
I executed the queries in different order, but the results are still the same. I'm not aware of any caching available on the server side. — mikemon, Oct 06 '19 at 17:12
Are you are saying you traced execution of the code in your question with Profiler and saw that Profiler reported a duration of 37 seconds and the stopwatch reported 21 seconds? I've only seen SQL trace report a shorter duration. — Dan Guzman, Oct 06 '19 at 17:19
No, I've just used two methods to check how long the query execution last. So SQL execution in the SQL server lasts about 37seconds, same query in the ADO.NET 21 seconds (for both: Profiler and stopwatch) :) — mikemon, Oct 06 '19 at 17:22
The SSMS query displays the results, which takes a considerable amount of time. The other two don't. Creating a datatable also takes time. In other words, you're comparing 3 quite different processes. — Gert Arnold, Oct 06 '19 at 17:37
@GertArnold Is there any possibility to compare mentioned query between ADO.NET and Linq-to-sql? — mikemon, Oct 06 '19 at 17:45
@mikemon, running a SQL query with SSMS is not "in SQL Server". SSMS is an ADO.NET client application like any other and the duration reported by Profiler will include time it takes for the application to consume large results. I think if you run the query in SSMS with the discard results option, you'll see similar timings as your ADO.NET code that doesn't include the rendering of the large result set in the time. — Dan Guzman, Oct 06 '19 at 18:19
Oh, thank you @DanGuzman for an explanation :) I will try without result option. — mikemon, Oct 06 '19 at 18:30

score 2 · Accepted Answer · answered Oct 06 '19 at 21:52

Linq-to-SQL is actually a layer on top of ADO.NET, so why is it faster than ADO.NET

ADO.NET has two layers. The DataReader is the lower level, and is used by both your ADO.NET code and your L2S/EF code. The higher level in ADO.NET is the DataSet/DataTable/DataAdapter, which is a set of classes for loading query results into memory. That is not used by L2S/EF.

To measure just the query processing and tranmission of results to the client, .Read() through the rows in the DataReader, but don't do anything with the data.

eg

int rows = 0;
using (var dr = cmd.ExecuteReader())
{
  while (dr.Read())
  {
    rows+=1;
  }
}

score 0 · Answer 2 · answered Oct 07 '19 at 13:19

To add to the previous answer, also keep in mind that Sql server keeps caches for query plans and data.

When you want to compare different requests, you should clear thoses cases before each measure with the following commands or by other means (restarting the server, using alter database clear procedure_cache ... see How can I clear the SQL Server query cache?)

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

If you do not do that, the second measure may give much better -but wrong- results because data or plans may be in thoses cache.

HTH

Performance comparison of queries in SQL Server, ADO.NET and LINQ-to-SQL

2 Answers2