Linq Performance - Part I

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:

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:

int firstName = rdr.GetOrdinal("FirstName");
int lastName = rdr.GetOrdinal("LastName"); 
while (rdr.Read())
{
    string fullName = rdr.GetString(firstName) + rdr.GetString(lastName);
}
rdr.Close();

while (rdr.Read())
{
    string fullName = rdr.GetString(0) + rdr.GetString(1);
}
rdr.Close();

while (rdr.Read())
{
    string fullName = (string)rdr["FirstName"] + (string)rdr["LastName"];
}
rdr.Close();

var contactNames = from c in dc.Contacts
                   select new { c.FirstName, c.LastName };
foreach (var contactName in contactNames)
{
    string fullName = contactName.FirstName + contactName.LastName;
}

IQueryable<AdvWorksName> contactNames = from c in dc.Contacts
                   select new AdvWorksName()
                    {FirstName= c.FirstName, LastName= c.LastName };
foreach (var contactName in contactNames)
{
    string fullName = contactName.FirstName + contactName.LastName;
}

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

IQueryable<AdvWorksNameProps> contactNames = from c in dc.Persons
                                        select new AdvWorksNameProps() { FirstName = c.FirstName, LastName = c.LastName };
foreach (var contactName in contactNames)
{
    string fullName = contactName.FirstName + contactName.LastName;
}

IQueryable<AdvWorksNameProps> contactNames = from c in dc.Persons
                                        select 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 …

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.