A few months ago, Brian Dawson gave us a look at a bunch of perf tests for querying with ADO.NET and Entity Framework.
I adapted and enhanced those tests for my session at TechEd and then added even more logic to them as I dug into performance for my book.
I thought I would share some of what I found. All of the timings here are only meant to demonstrate the performance relatively between the technologies. They are hardly scientific benchmarks. In my book I've called them "backyard benchmarks".
The first set of tests is similar to Brian's in that I merely query for all of the customers in the customer table of AdventureWorksLT. That's about 450.
However, thanks to some tips from Brad Sarsfield and Bruno Guardia Robles who do performance testing on the DP team, we added an extra loop of queries to "prime the pump" so to speak and get anything like metadata loading, query caching and well as SQL Server query caching out of the way.
Here are the average times comparing different ways of querying data.
Again, read these as relative to one another and nothing else.
The EntityClient query looks funny, as you'd expect it to be faster. When the query is more complex and the data is shaped, the EntityClient performs better than ObjectQuery which makes sense to me.
This is not using any query caching or pre-compiled queries or other advantages. I actually do a ton of those tests in that particular book chapter though.
Note that Entity Client and Object Services cache compiled queries by default (you can turn this off) which is a little different than using Compiled Queries in LINQ but has similar effect. So by default, the two Entity SQL tests are benefiting from that but the LINQ to Entities is not. So I really should either create a compiled query for LINQ to Entities and LINQ to SQL or turn query caching off for the two Entity SQL tests, because that's not completely fair.On the other hand, these *are* the defaults.
But what I found really interesting were updates.
There are a lot of ways to update data with ADO.NET. I chose to use DataAdapters since calling DataAdapter.Update is relative to calling context.SaveChanges. Here I queried those same 450 or so customers, edited each one, added 10 new ones then updated the database. I also was sure to clear out the 10 new records from the database after each iteration of the tests was complete. I didn't happen to do any deletes in this test.
I also did one DataAdapter.Update using UpdateBatch, just to compare.
In all cases I am starting the stopwatch, performing the update, then stopping the stopwatch. These times are the average of my 100 iterations of each test.
That is not a typo next to LINQ to SQL. I have not dug into why this is so different, but I had been told that EF was much faster than LINQ to SQL for updates. I had to see for myself. Bruno says this looks about right, but he's going to double check my code to make sure I didn't do anything completely wacky. And if I did it wasn't intentional.
But I was also interested to see that Object Services was also faster than DataAdapter.
I'm not trying to make a point here...just an observation because I have never seen these comparisons made before.
For all of these methods there are a lot of things you can do to improve this performance, not only in code but on the servers. And some server tweaks might benefit one method but not another. These tests are just using all defaults and gives me a stake in the ground. Now if I didn't need to actually finish my book, I could just keep at it which would be fun and interesting. But it wouldn't be so fun when Tim O'Reilly comes knocking on my door wondering where the heck my book is!