LINQ is Double Slower than traditional DATA Adapter - Correct ME

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Today, after watching the presentation by Amanda Silver 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
 
Chakravarthy said:
Today, after watching the presentation by Amanda Silver 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
-------------------

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
Click to expand...

Did you execute Linq first or second? THe first query hitting the DB
will 'warm up' the table and the caches of the RDBMS, so it will be
slower.

Also, Linq to Sql will have to emit IL at runtime first (once) to
operate, which can be a performance hit.

And as last: a dataadapter filling a datatable isn't really a
comparison with a fetcher which fetches entities. The thing is that the
dataadapter internally simply does something like:
object[] values = new object[reader.FieldCount];
while(reader.Read())
{
reader.GetValues(values);
table.Rows.Add(values);
}

(pseudo code, you get the idea).

This is very fast, as there's little to no overhead: the values read
are simply becoming the datarow of the table. For lists this might be
OK, however if you want to work with entities in your program, having
to deal with datarows which are tied to a datatable will limit your
freedom to structure your software in such a way that it will feel
natural to you: you can't think in entities, you're always stuck with a
row in a table which is always there.

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#)
------------------------------------------------------------------------
 
Frans,

Did you see that the Linq method uses a join while the SQL uses a Where, I
never have taken any time to see if that is important for speed. (As I never
look to it, maintanance in much more important for me) however maybe do you
know that.

Cor

Frans Bouma said:
Chakravarthy said:
Today, after watching the presentation by Amanda Silver 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
-------------------

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
Click to expand...

Did you execute Linq first or second? THe first query hitting the DB
will 'warm up' the table and the caches of the RDBMS, so it will be
slower.

Also, Linq to Sql will have to emit IL at runtime first (once) to
operate, which can be a performance hit.

And as last: a dataadapter filling a datatable isn't really a
comparison with a fetcher which fetches entities. The thing is that the
dataadapter internally simply does something like:
object[] values = new object[reader.FieldCount];
while(reader.Read())
{
reader.GetValues(values);
table.Rows.Add(values);
}

(pseudo code, you get the idea).

This is very fast, as there's little to no overhead: the values read
are simply becoming the datarow of the table. For lists this might be
OK, however if you want to work with entities in your program, having
to deal with datarows which are tied to a datatable will limit your
freedom to structure your software in such a way that it will feel
natural to you: you can't think in entities, you're always stuck with a
row in a table which is always there.

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#)
------------------------------------------------------------------------
Click to expand...
 
Back
Top