Language Integrated Query (LINQ) was introduced in VS 2008 and is a great way to query data. It is a standard query syntax that essentially provides a common way of querying data regardless of whether the data source in SQL, XML or in-memory objects.
Prior to .NET 3.5 developers would typically use ADO to query relational databases, specifically, if you were querying SQL Server you would be using the SQL ADO libraries. If you wanted to retrieve the data as quickly as possible then you used DataReaders, but it was the developer's responsibility to maintain the data. If performance was not an issue then developers would typically opt for Datasets, a more feature rich mechanism for retrieving and storing the data.
But how performant (I know it's not a word but ...) is LINQ to SQL? I decided to put it to the test.
I wrote a console application to test the performance of LINQ to SQL, ADO Datasets and ADO DataReaders. The application consists of 3 methods one to run LINQ to SQL, one for Datasets and the last for DataReaders. This code is shown in Listing 1. In order to level the playing field the DataReader method populates a custom object, MyCustomer. The code for this class is also included in the listing. In each case we are querying the customer table (91 rows), 10,000 times.
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Module Module1
Sub Main()
Dim start As DateTime
Dim [end] As DateTime
start = DateTime.Now
GetUsingLINQ()
[end] = DateTime.Now
ElapsedTime(start, [end], "Using LINQ")
start = DateTime.Now
GetUsingDataset()
[end] = DateTime.Now
ElapsedTime(start, [end], "Using ADO Dataset")
start = DateTime.Now
GetUsingDataReader()
[end] = DateTime.Now
ElapsedTime(start, [end], "Using ADO DataReader")
System.Console.ReadLine()
End Sub
Private Sub ElapsedTime(ByVal s As DateTime, ByVal e As DateTime, ByVal msg As String)
System.Console.WriteLine(msg + ": " + ((e.Ticks - s.Ticks) / 10000000.0).ToString + " secs")
End Sub
Private Sub GetUsingLINQ()
Dim db As New NorthwindDataContext()
Dim query = From c In db.Customers
Dim i As Integer
For i = 0 To 10000
query.ToList()
Next
End Sub
Private Sub GetUsingDataset()
Dim cstrcol As ConnectionStringSettingsCollection = ConfigurationManager.ConnectionStrings
Dim cs As String = cstrcol("LINQ2SQLvsADO.My.MySettings.NorthwindConnectionString").ToString
Dim cn As New SqlConnection(cs)
Dim query As String = _
"select [CustomerID], [CompanyName], [ContactName], [ContactTitle], " + _
"[Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] " + _
"from [dbo].[Customers]"
Dim cmd As New SqlCommand(query, cn)
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet
Dim i As Integer
For i = 0 To 10000
da.Fill(ds)
Next
End Sub
Private Sub GetUsingDataReader()
Dim cstrcol As ConnectionStringSettingsCollection = ConfigurationManager.ConnectionStrings
Dim cs As String = cstrcol("LINQ2SQLvsADO.My.MySettings.NorthwindConnectionString").ToString
Dim cn As New SqlConnection(cs)
Dim query As String = _
"select [CustomerID], [CompanyName], [ContactName], [ContactTitle], " + _
"[Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] " + _
"from [dbo].[Customers]"
Dim cmd As New SqlCommand(query, cn)
Dim dr As SqlDataReader
cn.Open()
Dim mc As New MyCustomer
Dim i As Integer
For i = 0 To 10000
dr = cmd.ExecuteReader
While dr.Read
mc.CustomerID = dr("CustomerID").ToString
mc.CompanyName = dr("CompanyName").ToString
mc.ContactName = dr("ContactName").ToString
mc.ContactTitle = dr("ContactTitle").ToString
mc.Address = dr("Address").ToString
mc.City = dr("City").ToString
mc.PostalCode = dr("PostalCode").ToString
mc.Country = dr("Country").ToString
mc.Phone = dr("Phone").ToString
mc.Fax = dr("Fax").ToString
mc.Region = dr("Region").ToString
End While
dr.Close()
Next
End Sub
End Module
Public Class MyCustomer
Private _CustomerID As String
Public Property CustomerID() As String
Get
Return _CustomerID
End Get
Set(ByVal value As String)
_CustomerID = value
End Set
End Property
Private _CompanyName As String
Public Property CompanyName() As String
Get
Return _CompanyName
End Get
Set(ByVal value As String)
_CompanyName = value
End Set
End Property
Private _ContactName As String
Public Property ContactName() As String
Get
Return _ContactName
End Get
Set(ByVal value As String)
_ContactName = value
End Set
End Property
Private _ContactTitle As String
Public Property ContactTitle() As String
Get
Return _ContactTitle
End Get
Set(ByVal value As String)
_ContactTitle = value
End Set
End Property
Private _Address As String
Public Property Address() As String
Get
Return _Address
End Get
Set(ByVal value As String)
_Address = value
End Set
End Property
Private _City As String
Public Property City() As String
Get
Return _City
End Get
Set(ByVal value As String)
_City = value
End Set
End Property
Private _Region As String
Public Property Region() As String
Get
Return _Region
End Get
Set(ByVal value As String)
_Region = value
End Set
End Property
Private _PostalCode As String
Public Property PostalCode() As String
Get
Return _PostalCode
End Get
Set(ByVal value As String)
_PostalCode = value
End Set
End Property
Private _Country As String
Public Property Country() As String
Get
Return _Country
End Get
Set(ByVal value As String)
_Country = value
End Set
End Property
Private _Phone As String
Public Property Phone() As String
Get
Return _Phone
End Get
Set(ByVal value As String)
_Phone = value
End Set
End Property
Private _Fax As String
Public Property Fax() As String
Get
Return _Fax
End Get
Set(ByVal value As String)
_Fax = value
End Set
End Property
End Class
If you would like to try this out for yourself then cut paste the code above and add it to a module file. In addition you will need to add a LINQ to SQL Class and make sure you call it Northwind.dbml. The only table that is required is the Customers table
Capricorn 15's. Born 2244. Enter the Carousel. This is the time of renewal.