Ruminations of J.net idle rants and ramblings of a code monkey

Linq Performance - Part I

.NET Stuff | Linq | Performance

Well, it’s been a while since I did my initial review of some simple Linq performance tests. Since then, I’ve done a bit more testing of Linq performance and I’d like to share that. The results are enlightening, to say the least. I did this because I’ve gotten a lot of questions regarding the performance of Linq and, in particular, Linq to Sql – something that is common whenever there is a new data-oriented API. Now, let me also say that performance isn’t the only consideration … there are also considerations of functionality and ease of use, as well as the overall functionality of the API and its applicability to a wide variety of scenarios. I used the same methodology that I detailed in this previous post.

Now, all of the tests were against the AdventureWorks sample database’s Person.Contact table with some 20,000 rows. Not the largest table in the world, but it’s also a good deal larger that the much-beloved Northwind database. I also decided to re-run all of the tests a second time on my home PC (rather than my laptop) as the client and one of my test servers as the database server. The specs are as follows:

ClientDB Server
AMD Athlon 64 X2 4400+AMD Athlon 64 X2 4200+
4 GB RAM2 GB RAM
Vista SP1 x64Windows Server 2008 Standard x64
Visual Studio 2008 SP1Sql Server 2008 x64

So, with that out of the way, let’s discuss the first test.

Simple Query

This is a simple “SELECT * FROM Person.Contact” query … nothing special or funky. From there, as with all of the tests, I loop through the results and assign them to temporary, local variables. An overview of the tests is below:

DataReaderIndexUses a data reader and access the values using the strongly-typed GetXXX methods (i.e. GetString(int ordinal)). With this set, the ordinal is looked up using GetOrdinal before entering the loop to go over the resultset. This is my preferred method of using a DataReader.
int firstName = rdr.GetOrdinal("FirstName");int lastName = rdr.GetOrdinal("LastName");while (rdr.Read())
{
string fullName = rdr.GetString(firstName) + rdr.GetString(lastName);
}
rdr.Close();
DataReaderHardCodedIndexThis is the same as TestDataReaderIndex with the exception that the ordinal is not looked up before entering the loop to go over the resultset but is hard-coded into the application.
while (rdr.Read())
{
string fullName = rdr.GetString(0) + rdr.GetString(1);
}
rdr.Close();
DataReaderNoIndexAgain, using a reader, but not using the strongly-typed GetXXX methods. Instead, this is using the indexer property, getting the data using the column name as an object. This is how I see a lot of folks using Data Readers.
while (rdr.Read())
{
string fullName = (string)rdr["FirstName"] + (string)rdr["LastName"];
}
rdr.Close();
LinqAnonTypeUses Linq with an anonymous type
var contactNames = from c in dc.Contactsselect new { c.FirstName, c.LastName };foreach (var contactName in contactNames)
{
string fullName = contactName.FirstName + contactName.LastName;
}
LinqClass_FieldAgain, uses Linq but this time it’s using a custom type. In this class the values are stored in public fields, rather than variables.

IQueryable<AdvWorksName> contactNames = from c in dc.Contactsselect new AdvWorksName()
{FirstName= c.FirstName, LastName= c.LastName };foreach (var contactName in contactNames)
{
string fullName = contactName.FirstName + contactName.LastName;
}
DataSetThis final test uses an untyped dataset. We won’t be doing a variation with a strongly-typed dataset for the select because they are significantly slower than untyped datasets. Also, the remoting format for the dataset is set to binary, which will help improve the performance for the dataset, especially as we get more records.
DataSet ds = new DataSet();
ds.RemotingFormat = SerializationFormat.Binary;SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(ds);foreach (DataRow dr in ds.Tables[0].Rows)
{
string fullName = dr.Field<String>("FirstName") + dr.Field<String>("LastName"); 
}
cnct.Close();
LinqClass_PropThis uses a custom Linq class with properties for the values.
IQueryable<AdvWorksNameProps> contactNames = from c in dc.Personsselect new AdvWorksNameProps() { FirstName = c.FirstName, LastName = c.LastName };foreach (var contactName in contactNames)
{
string fullName = contactName.FirstName + contactName.LastName;
}
LinqClass_CtorThis uses the same Linq class as above but initializes the class by calling the constructor rather than binding to the properties.
IQueryable<AdvWorksNameProps> contactNames = from c in dc.Personsselect new AdvWorksNameProps(c.FirstName,  c.LastName);foreach (var contactName in contactNames)
{
string fullName = contactName.FirstName + contactName.LastName;
}

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

If you are wondering why the different “flavors” of Linq … it’s because, when I first started re-running these tests for the blog, I got some strange differences that I hadn’t seen before between (what is now) LinqAnonType and LinqClassField. On examination, I found that these things made a difference and wanted to get a more rounded picture of what we were looking at here … so I added a couple of tests.

And the results …

 

 

 Average
LinqClass_Field277.61
DataReaderIndex283.43
DataReaderHardCodedIndex291.17
LinqClass_Prop310.76
DataSet323.71
LinqAnonType329.26
LinqClass_Ctor370.20
DataReaderNoIndex401.63

image

These results are actually quite different from what I saw when I ran the tests on a single machine … which is quite interesting and somewhat surprising to me. Linq still does very well when compared to DataReaders … depending on exactly how you implement the class. I didn’t expect that the version using the constructor would turn out to be the one that had the worst performance … and I’m not really sure what to make of that. I was surprised to see the DataSet do so well … it didn’t on previous tests, but in those cases, I also didn’t change the remoting format to binary; this does have a huge impact on the load performance, especially as the datasets get larger (XML gets pretty expensive when it starts getting big).

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I’ve got more tests, but due to the sheer length of this post, I’m going to post them separately.