Whatever blows your hair back

A co-worker and friend of mine, Dr. Steve Morrison, came to me the other day and asked if I had used the CompiledQuery (System.Data.Linq.CompiledQuery) class with LINQ-to-SQL. I told him that I had not and he said that he read that it was a big performance gain over just straight LINQ queries. Skeptical as I usually am, I let it sit on frontal lobe’s back burner for a day or so and occasionally asked Steve if he had done any testing with it yet. Today, he still hadn’t done any testing so I curiosity killed my patient little cat and I wrote a few tests to see what kind of performance improvements this “extra step” could make.

But first, if you plan on doing any of your own tests, you need to know how to clear SQL Server’s cache before each test run. You do that by executing the following SQL commands:

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO

I suggest putting that into a stored procedure and just calling it from LINQ-to-SQL (as I did in this test).

Now to the testing. My test database is a SQL Server 2008 database that has a decent amount of data in it; roughly 140K records in the one test table I use for this test. I am using LINQ-to-SQL (without the T4 templates) in C# for this test. All tests were run in release mode on a Quad-core PC running Windows 7 with 4GB of RAM.

And as a side note, no underscores were harmed in the making of these tests!  You have to be “in the know” to get that joke.

Test 1: Incremental record Count retrieval (from 1 record to 5000 records incrementally)
Basically, there is a loop from 1 to 5000. If the [Id] (the primary key of the table) is less than or equal to the index of the loop, I retrieve that record. As the loop progresses, the query will bring back more and more records. Eh, it’s a test… leave it at that.

Test 2: Get one record at a time… 5000 times
Again, there is a loop from 1 to 5000. If the [Id] (the primary key of the table) is equal to the index of the loop, I retrieve that record. The query will always return a single record… a different record… but a single one.

Here’s the non-compiled, normal LINQ-to-SQL:

// BaseTestClass has Logging and the Clear method... don't worry about it!
internal sealed class NonCompiledQueryTest : BaseTestClass
{
	public void Run()
	{
		ClearDatabaseCache();

		using (var dataContext = new TestDbDataContext(_connectionString))
		{
			dataContext.ObjectTrackingEnabled = false;

			Log("Starting Non-Compiled Test");
			DateTime start = DateTime.Now;
			for (int index = 0; index < 5000; index++)
			{
				var q = from r in dataContext.GetTable<MySampleTableFullOfData>()
				        where r.Id == index select r;
				q.ToList();
			}
			Log("Non-compiled: " + DateTime.Now.Subtract(start).TotalSeconds);
		}
	}
}

And here’s the Compiled LINQ-to-SQL:

internal sealed class CompiledQueryTest : BaseTestClass
{
	public void Run()
	{
		ClearDatabaseCache();

		using (var dataContext = new TestDbDataContext(_connectionString))
		{
			dataContext.ObjectTrackingEnabled = false;

			Log("Starting Compiled Test");
			DateTime start = DateTime.Now;
			for (int index = 0; index < 5000; index++)
				Compiled(dataContext, index).ToList();

			Log("Compiled: " + DateTime.Now.Subtract(start).TotalSeconds);
		}
	}

	static private readonly Func<TestDbDataContext, int, IQueryable<MySampleTableFullOfData>> Compiled =
		CompiledQuery.Compile<TestDbDataContext, int, IQueryable<MySampleTableFullOfData>>(
			(dc_, index_) =>
			from r in dc_.GetTable<MySampleTableFullOfData>()
			where r.Id == index_
			select r);
}

Results? Stop being so pushy! Here ya go:

Those numbers are the total number of seconds taken to run each test. I was completely blown away by the results. I honestly thought there was something wrong with the results from Test 2 so I ran that test as many different ways as I could… all produced the same exact results. That’s just awesome! Time to refactor some existing code!

The CompiledQuery improvements really shine when there are a lot of queries executing; not a single query that brings back a lot of records. In the later case, I found the compiled query to be about the same or just a smidgeon slower. With multiple queries… it just blows away normal, non-compiled, queries.

Enjoy!

Tags: C#, LINQ, LINQ-to-SQL