Sql – Why is Linq To Sql databinding to gridview much slower than pass-through SQL

data-bindinglinq-to-sqlsql

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.

Related Topic