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

Question

Hello,

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

Answer 1

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

In this case I suspect it's how the client is handling it (DataSet vs List) that is causing the difference, but I'm nowhere near a c# expert.

Answer 2

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

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

Answer 3

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.

Answer 4

Capture and analyze the SQL statement(s) that are being sent over the wire in your Linq To Sql example. SQL Profiler will do the trick.

Run both those statements from Example 1 and 2 directly against your SQL Server using Management Studio. Likely you won't see ANY substantial difference in the query plan.

I think the majority of time is spent in constructing the C# objects (Jason's answer nails it, I think).

Answer 5

Linq to Sql has to figure out from your code what kind of query to run against the database then it has to translate the results of the query into strongly typed objects, which means casting will occur. That's two things your DataSet version doesn't do.

If you're interested in Linq to Sql performance, there is the option of using compiled queries which removes the need for interpretation at runtime.