Is LINQ consumes double the time of Traditional Data Connection?

[Guest]

Today, after watching the presentation by Amanda Silver at
http://channel9.msdn.com/Showpost.aspx?postid=335058 , from Channel 9,
started exploring the LINQ features. Surprisingly, few facts unearthed to
prove that LINQ is slower, much to double the time consumed by the usage of
traditional Data Adapter. Correct me if you find these facts can be proved
fault

HW Scenario :
----------------------------------------------------------------------------
System OS - XP Prof with SP2
System Processor - AMD 3000+ with 2GHz
RAM - 1 GB
DB Server - LocalHost on SQL Server 2005
Implemented with Orcas Beta 2
----------------------------------------------------------------------------

Source Code copied from Amanda's presentation

Code Snippet for LINQ
----------------------------------------------------------------------------
Dim t1, t2 As Double

t1 = Date.Now.TimeOfDay.TotalMilliseconds

Dim db As New exLinqDataContext(My.Settings.NorthwindConnectionString)

Dim query = From cust In db.Customers _

Join sup In db.Suppliers _

On cust.City Equals sup.City _

Select cust.CompanyName, sup.ContactName, cust.City

Me.DataGridView1.DataSource = query

t2 = Date.Now.TimeOfDay.TotalMilliseconds

Dim diffT As Double

diffT = t2 - t1

MessageBox.Show(diffT)
----------------------------------------------------------------------------


Source Code using Traditional Data Adapter
----------------------------------------------------------------------------
Dim t1, t2 As Double

t1 = Date.Now.TimeOfDay.TotalMilliseconds

Dim cn As New SqlClient.SqlConnection("Data Source=localhost;Initial
Catalog=Northwind;Integrated Security=True")

Dim da As New SqlClient.SqlDataAdapter("Select
C.CompanyName,S.ContactName,C.City from Customers C,Suppliers S Where C.City
= S.City", cn)

Dim DS As New DataSet

da.Fill(DS)

GV.DataSource = DS.Tables(0)

t2 = Date.Now.TimeOfDay.TotalMilliseconds

Dim diffT As Double

diffT = t2 - t1

MessageBox.Show(diffT)
----------------------------------------------------------------------------

For your attention here comes the result for the first execution time of
each method



-------------------
LINQ DataAdapter
-------------------
171.875 93.750
171.875 93.750
156.250 78.125
156.250 78.125
156.250 78.125

 

[Jon Skeet [C# MVP]]

Can some one help me to understand to interpret the results and how LINQ is
advantageous than the tradtional way of using Data Adapter with Data
Connections

Have you looked at the SQL query being used? Those figures are pretty
odd... normally LINQ should be "about" as fast as traditional methods,
so long as the query is equivalent.

 

[Guest]

Jon,

Yes, what you said is correct from the point of statements given by the
representatives as well as by the company, Microsoft's official barriers. But
unfortunately, the figures doesn't lie. Am really surprised to see such kind
of figures specially using the latest framework version.

Secondly, i guess there is nothing wrong in the SQL Query. Correct me if am
found fault.

Anyhow, thanks for replying.
--
Every thing is perfect, as long as you share!!!

Don''t forget to rate the post

 

[Jon Skeet [C# MVP]]

Yes, what you said is correct from the point of statements given by the
representatives as well as by the company, Microsoft's official barriers. But
unfortunately, the figures doesn't lie.

Benchmarks can very, very easily be wrong. Benchmarking is really quite
tricky to get right.

Am really surprised to see such kind
of figures specially using the latest framework version.

Secondly, i guess there is nothing wrong in the SQL Query. Correct me if am
found fault.

You haven't told us what the query generated by LINQ is. That's what I
was asking before.

If you could produce a short but *complete* program, we could test it
for ourselves, of course.

 

[Jon Skeet [C# MVP]]

<snip>

For a more detailed analysis of LINQ performance see Rico's blog:

http://blogs.msdn.com/ricom/archive/2007/06/25/dlinq-linq-to-sql-
performance-part-1.aspx

http://blogs.msdn.com/ricom/archive/2007/06/25/dlinq-linq-to-sql-
performance-part-2.aspx

http://blogs.msdn.com/ricom/archive/2007/06/25/dlinq-linq-to-sql-
performance-part-3.aspx

http://blogs.msdn.com/ricom/archive/2007/06/25/dlinq-linq-to-sql-
performance-part-4.aspx

http://blogs.msdn.com/ricom/archive/2007/06/25/dlinq-linq-to-sql-
performance-part-5.aspx

 

[Frans Bouma [C# MVP]]

<snip>

For a more detailed analysis of LINQ performance see Rico's blog:

http://blogs.msdn.com/ricom/archive/2007/06/25/dlinq-linq-to-sql-
performance-part-1.aspx

http://blogs.msdn.com/ricom/archive/2007/06/25/dlinq-linq-to-sql-
performance-part-2.aspx

http://blogs.msdn.com/ricom/archive/2007/06/25/dlinq-linq-to-sql-
performance-part-3.aspx

http://blogs.msdn.com/ricom/archive/2007/06/25/dlinq-linq-to-sql-
performance-part-4.aspx

http://blogs.msdn.com/ricom/archive/2007/06/25/dlinq-linq-to-sql-
performance-part-5.aspx

These are pretty artificial. Try fetching a graph of 3 entities, and
see it crawl. The thing is: if you focus on a tiny area of the whole
process, it can look fast, however it comes at a price (no multi-table
inheritance for example) and overall it might be slow as it drops the
ball in other areas. THe same goes for datatables btw. They're fast for
filling a table with a bunch of rows. However these have downsides as
well, plus no inheritance support for entities.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Jon Skeet [C# MVP]]

On Sep 4, 9:03 am, "Frans Bouma [C# MVP]"

These are pretty artificial. Try fetching a graph of 3 entities, and
see it crawl.

Could you give an example? Do you mean a join across three tables?

Is the problem that the SQL generated is inappropriate, or is it the
process of transforming the results into entities (or populating the
DataTable, etc)?

Jon

 

[DSK Chakravarthy]

Jon,

Thanks a lot for your reply. Please follow the steps to reproduce the
*complete* program

Step1: Create a windows Application
Step2: Drag a GridView on the windows form
Step3: As mentioned by Amanda, copy the mentioned code onto the Click event
of GridView

Finally, you have the both code snippets. I've taken 2 different windows
applications to run tme individually.

Hope am clear at this, ping me back if am missing some thing.

 

[Jon Skeet [C# MVP]]

Thanks a lot for your reply. Please follow the steps to reproduce the
*complete* program

Step1: Create a windows Application
Step2: Drag a GridView on the windows form
Step3: As mentioned by Amanda, copy the mentioned code onto the Click event
of GridView

Finally, you have the both code snippets. I've taken 2 different windows
applications to run tme individually.

Hope am clear at this, ping me back if am missing some thing.

It would be far better if you'd just post some code we can compile and
run.

See http://pobox.com/~skeet/csharp/complete.html

Jon

 

[Jon Skeet [C# MVP]]

For your attention here comes the result for the first execution time of
each method

I've only just spotted the word "first" in here.

What about the next time you execute the query? On my box, that's much,
much faster. This makes sense - not only has LINQ got to connect to the
database (like the DataAdapter does) but it's also got to build an
assembly. That assembly can be reused for subsequent queries, however.
Try measuring the subsequent executions of the same query and I'm sure
you'll see a big difference.

How much do you care about losing a tenth of a second or so of
performance *once* (per run) compared with the *huge* benefit in
development effort that LINQ will bring?

 

[Frans Bouma [C# MVP]]

On Sep 4, 9:03 am, "Frans Bouma [C# MVP]"

These are pretty artificial. Try fetching a graph of 3
entities, and see it crawl.

Could you give an example? Do you mean a join across three tables?

Is the problem that the SQL generated is inappropriate, or is it the
process of transforming the results into entities (or populating the
DataTable, etc)?

Fetch with loadoptions, eagerloading: Customers + orders +
orderdetails.

You won't get 1 nor 3 queries but a lot of queries. With a dataset or
normal o/r mapper, it would take 3 queries tops, with linq to sql it
won't as they use a flawed 'join' approach.

Then I haven't even mentioned the possibility of specifying a filter
on orders and/or order details which is taken into account when
fetching the graph. (e.g. fetch all customers from UK and their last 10
orders from 2007)

I haven't tested the situation where 3 separate queries are formulated
and the context builds teh graph for you but I doubt it's there, as
otherwise they would have used it with LoadOptions as well.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Jon Skeet [C# MVP]]

Fetch with loadoptions, eagerloading: Customers + orders +
orderdetails.

You won't get 1 nor 3 queries but a lot of queries. With a dataset or
normal o/r mapper, it would take 3 queries tops, with linq to sql it
won't as they use a flawed 'join' approach.

Hmm... That does indeed sound a bit broken. Unrelated to the original
question from this thread, I suspect, but broken nonetheless.

Has this been acknowledged as far as you're aware? At least if the SQL
being generated is inappropriate, that could me easier to fix than
other performance issues.

Then I haven't even mentioned the possibility of specifying a filter
on orders and/or order details which is taken into account when
fetching the graph. (e.g. fetch all customers from UK and their last 10
orders from 2007)

Mmm, that's an interesting one. I can't remember how I'd have done
that in Hibernate - it's not something I tended to do, but I can see
the usefulness of it.

I haven't tested the situation where 3 separate queries are formulated
and the context builds teh graph for you but I doubt it's there, as
otherwise they would have used it with LoadOptions as well.

Yes, you'd think so.

Jon

 

[Frans Bouma [C# MVP]]

Hmm... That does indeed sound a bit broken. Unrelated to the original
question from this thread, I suspect, but broken nonetheless.

Has this been acknowledged as far as you're aware? At least if the SQL
being generated is inappropriate, that could me easier to fix than
other performance issues.

Yes, they've acknowledged it but with the remark that it's very late
in the release cycle so very likely they won't fix it.

Yes, you'd think so.

I tested it, no they won't do this.

So the merge code isn't there, so they'll very likely not fix the
error I mentioned earlier.

You'd think that they had enough time to think things through after
ObjectSpaces could do this already

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Jon Skeet [C# MVP]]

Yes, they've acknowledged it but with the remark that it's very late
in the release cycle so very likely they won't fix it.

Oh joy...

I tested it, no they won't do this.

So the merge code isn't there, so they'll very likely not fix the
error I mentioned earlier.

You'd think that they had enough time to think things through after
ObjectSpaces could do this already

Any idea if ADO.NET Entities will be better in this regard? I do
wonder whether LINQ to SQL is effectively just a stop-gap until
ADO.NET entities is released...

Jon

 

[Frans Bouma [C# MVP]]

Any idea if ADO.NET Entities will be better in this regard?

What I understood from Simmons' blog it indeed does this.

I do
wonder whether LINQ to SQL is effectively just a stop-gap until
ADO.NET entities is released...

Well, there are some fundamental design decisions made in linq to sql
which make it very hard to implement multiple-table inheritance, IMHO
without breaking a lot. This means that it will never get
multiple-table inheritance. Also, to create a provider for it (which is
possible in theory), you need to implement an interface which is
internal (wonder why... ) so it seems that indeed it looks like a
toolkit which is here for the moment but not for the long run.

What is particularly odd is that there's no real upgrade path from
ling to sql to entity framework.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------