Performance Test: Linq to SQL vs Data Adapters/DataSet

LINQ to SQL is nifty. Because it is two-tiered, it's usefulness is limited. However, it's neat. The real question is: how does it perform?

A Runtime Query passes SQL to SQL Server. A Procedure Query accesses a Stored Procedure.

Basically, I accessed a simple SQL table (ID int identity primary key, Name varchar(50)) with 47k records, retrieving the one and only record with a specified name.

Results

Here's how it worked out.

Conclusion

The winner is? It's hard to say. Though runtime Linq is clearly not.

LINQ to SQL is pretty impressive. Considering the power of the runtime query function, the cost (which must be the construction of the query?) is close to other typical data access methods.

I ran it 40 times to make sure any SQL execution plan cache would benefit all techniques.

Note the Sleep(10) prevented 100% processor utilization. It made everything 1000 milliseconds longer - which is interesting, too.

Source Code

To execute this code, it will take some work. You will need to create the Context for LINQ to SQL and the typed DataSet for the data adapters. You will also need the database and the data.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using EntityContext;
using ConsoleApplication2.DataAccessDataSetTableAdapters;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;

namespace ConsoleApplication2
{
  class DataAccess
  {
    static string m_ConnStr = "";
    static public void Test()
    {
      string _Name = "Jason A. Clark";
      System.Diagnostics.Stopwatch _Stopwatch =
        new System.Diagnostics.Stopwatch();
      int _Repeat = 100;

      LinqProc(_Name, _Stopwatch, _Repeat);
      //LinqRuntime(_Name, _Stopwatch, _Repeat);
      //DataSetProc(_Name, _Stopwatch, _Repeat);
      //DataSetRuntime(_Name, _Stopwatch, _Repeat);
      //DataReaderX(_Name, _Stopwatch, _Repeat);
    }

    private static void DataSetRuntime(
     string _Name,
     System.Diagnostics.Stopwatch _Stopwatch,
     int _Repeat)
    {
      // dataset runtime query
      _Stopwatch.Reset();
      _Stopwatch.Start();
      for (int i = 0; i < _Repeat; i++)
      {
        TestTableTableAdapter _DataAdapter;
        _DataAdapter = new TestTableTableAdapter();
        DataAccessDataSet.TestTableDataTable _DataTable;
        _DataTable = _DataAdapter.GetDataByName(_Name);
        System.Diagnostics.Debug.Write(
          _DataTable.Rows[0]["Name"]);
        System.Threading.Thread.Sleep(10);
      }
      _Stopwatch.Stop();
      Console.Write(
        _Stopwatch.Elapsed.TotalMilliseconds.ToString() + ",");
    }

    private static void DataSetProc(
     string _Name,
     System.Diagnostics.Stopwatch _Stopwatch,
     int _Repeat)
    {
      // dataset proc query
      _Stopwatch.Reset();
      _Stopwatch.Start();
      for (int i = 0; i < _Repeat; i++)
      {
        TestTableTableAdapter _DataAdapter;
        _DataAdapter = new TestTableTableAdapter();
        DataAccessDataSet.TestTableDataTable _DataTable;
        _DataTable = _DataAdapter.GetDataByProc(_Name);
        System.Diagnostics.Debug.Write(
          _DataTable.Rows[0]["Name"]);
        System.Threading.Thread.Sleep(10);
      }
      _Stopwatch.Stop();
      Console.Write(
        _Stopwatch.Elapsed.TotalMilliseconds.ToString() + ",");
    }

    private static void LinqRuntime(
     string _Name,
     System.Diagnostics.Stopwatch _Stopwatch,
     int _Repeat)
    {
      // linq runtime query
      _Stopwatch.Reset();
      _Stopwatch.Start();
      for (int i = 0; i < _Repeat; i++)
      {
        DataAccessLinqDataContext _db;
        _db = new DataAccessLinqDataContext();
        var _Results = from item in _db.TestEntities
                where item.Name == _Name
                select item;
        System.Diagnostics.Debug.Write(
          _Results.First().Name);
        System.Threading.Thread.Sleep(10);
      }
      _Stopwatch.Stop();
      Console.Write(
        _Stopwatch.Elapsed.TotalMilliseconds.ToString() + ",");
    }

    private static void LinqProc(
     string _Name,
     System.Diagnostics.Stopwatch _Stopwatch,
     int _Repeat)
    {
      // linq procedure query
      _Stopwatch.Reset();
      _Stopwatch.Start();
      for (int i = 0; i < _Repeat; i++)
      {
        DataAccessLinqDataContext _db;
        _db = new DataAccessLinqDataContext();
        var _Results = _db.TestProcedure(_Name);
        System.Diagnostics.Debug.Write(
          _Results.First().Name);
        System.Threading.Thread.Sleep(10);
      }
      _Stopwatch.Stop();
      Console.Write(
        _Stopwatch.Elapsed.TotalMilliseconds.ToString() + ",");
    }

    private static void DataReaderX(
     string _Name,
     System.Diagnostics.Stopwatch _Stopwatch,
     int _Repeat)
    {
      // datareader runtime query
      _Stopwatch.Reset();
      _Stopwatch.Start();
      for (int i = 0; i < _Repeat; i++)
      {
        string _Sql;
        _Sql = string.Format(
          "select * from tb_Test where Name = '{0}'", _Name);

        using (SqlConnection _SqlConnection =
          new SqlConnection(m_ConnStr))
        {
          SqlCommand _SqlCommand;
          _SqlCommand = new SqlCommand(_Sql,
              _SqlConnection);
          _SqlConnection.Open();

          SqlDataReader _SqlDataReader;
          _SqlDataReader = _SqlCommand.ExecuteReader();

          _SqlDataReader.Read();
          System.Diagnostics.Debug.Write(
              _SqlDataReader["Name"]);
          _SqlDataReader.Close();
          _SqlConnection.Close();
        }
        System.Threading.Thread.Sleep(10);
      }
      _Stopwatch.Stop();
      Console.Write(
        _Stopwatch.Elapsed.TotalMilliseconds.ToString());
    }
  }
}