Last month, I explained how we had achieved a significant performance improvement by using Dapper to pull the data from the database, rather than using EF Core.
Whilst showing this to a colleague, he reminded me that EF Core has a FromSqlRaw
method that does pretty much the same thing. Assuming that EF Core would be slower than Dapper, I didn’t give it much thought at the time. However, being a compulsive tinkerer, I decided to take a closer look.
Benchmarking EF Core against Dapper
Given that the main reason for using Dapper was the much-touted increases in speed, the obvious thing to do before anything else was see how EF Core and Dapper performed.
If you search around for speed comparisons, you’ll find no end of blogs showing how much faster Dapper is. However, if you read carefully, you’ll see they are not comparing like with like. Sure Dapper is fast, but it’s doing a whole lot less than EF Core. You have to write your own SQL and manage pretty much everything yourself. By contrast, EF Core gives you an amazingly powerful abstraction over your data (my beloved Linq) and generates the SQL for you. Therefore it’s not really fair to do a direct comparison.
So, I decided to do some like-for-like testing, using EF Core’s FromSqlRaw
method instead of Linq. That is a more realistic comparison.
Before I get into that though, I have two (absolutely true) stories to tell you…
Anecdote #1: Reducing the size of an executable
OK, so not actually a benchmark, but suffers from exactly the same issues, so I’ll include it anyway 😎.
Many decades, I was reading a C++ book (stop laughing at the back!), which included a section on how to reduce the size of your executable. Remember, this was in the days when 16Mb of RAM was normal for a PC. Dead hard developers (like me 😎) had 32Mb!
Anyway, one of the ways they showed was moving hard-coded strings into resources (can’t remember the exact details). With this, they managed to reduce the size of the executable by around 20%, which was pretty impressive until you read it again and realised that they were using a noddy program as their sample, and the original executable file was only around 22Kb in the first place! Had they applied the technique to a more realistic executable, I doubt they would have achieved more than a 0.001% reduction.
Anecdote #2: Improving the access speed of a database object
This one also goes back quite a while, although not as far into the misty past as my (thankfully brief) experiences with C++.
Before I switched to .NET, my main development language was Visual Basic. Database access was done with ADO objects, and was usually the bottleneck of any application. One of the neat features of VB (still around in the latest version) was the With
. keyword. To quote the docs, this enables you to execute a series of statements that repeatedly refer to a single object or structure so that the statements can use a simplified syntax when accessing members of the object or structure. Eh?
An example makes it clear…
Private Sub AddCustomer() Dim theCustomer As New Customer With theCustomer .Name = "Coho Vineyard" .URL = "http://www.cohovineyard.com/" .City = "Redmond" End With With theCustomer.Comments .Add("First comment.") .Add("Second comment.") End With End Sub
As you can see, the With
enables you to get and set properties on the theCustomer
object, without having to reference theCustomer
explicitly.
There was some debate as to whether this keyword made any difference to performance. I remember reading a blog post on a very respected site that showed how using With
could give you some significant performance improvements when accessing ADO objects.
Great, except that it was rubbish! When you looked again, you realised that they were comparing the speed of accessing the same property on a single ADO object one million times. Now who does that? Had they pulled a million records from a database and checked the speed of accessing a property on each one with and without the With
keyword, then they might have been on to something, but as it was, the supposed benchmark was so unrealistic as to be ludicrous.
All of which was a very long and pointless build up to the following caveat to what comes next…
Important caveat: I am very suspicious of benchmarks, as they are almost always unrealistic, and look more impressive than they are. I offer the comments below with the intention that you apply the same generous amount of salt as you need to do with the two examples above.
Actually benchmarking EF Core against Dapper (as opposed to telling stories)
Now the main problem here was that the data access in my Dapper examples was so fast, that benchmarking was going to be very difficult. Very short execution times are always subject to a load of external factors, including what else is happening on the machine at the same time, so comparing them can be very difficult.
As the results of the benchmarking were less than exciting (see below), I didn’t bother keeping the code or the results, so the comments below are all done from memory
My first attempt at a comparison was to do something similar to what the extension method was doing, ie set up some SQL and run it loads of times to see how it performed. However, both Dapper and EF Core were so fast that I didn’t feel they told me anything.
One of the reasons the Dapper code in the previous post was so fast was because it was pulling back so little data. So, I decided to try pulling back more data. I used a table in a local database that contains around a quarter of a million rows, and pulled out 500. Even that isn’t realistic, as no human can grok that much data, and if your intent is to do some aggregation on it, you are always better doing as much of that as you can on the database. Nevertheless, I pushed on, just to see what happened.
The results showed that if there was indeed any real difference, EF Core was actually slightly faster. Not a huge difference, something of the order of 20ms against Dapper’s 30ms. Again, this is on 500 rows, on a more realistic case of (say) 30 rows, the difference was so small as to be irrelevant.
However, my main takeaway from this was that I didn’t need Dapper. not that I have anything against it at all, it’s just that performance with EF Core was certainly no worse, and possibly slightly better, so why introduce yet another dependency, and slight more code when I could stick with EF Core?
The point of this long ramble
If you’re still awake, you might be relieved to know that I’m finally getting to the point!
I set about rewriting the extension method from the previous blog post to use EF Core. Along the way, I fixed a bug, extended the range of filter operators supported and made a couple of major improvements. However, as this blog post is already far too long, I’ll leave the explanation of that for another time!
To be continued…
[…] package. However, it will probably be removed from the next version, as I later discovered that I can do everything that this method does without needing Dapper. I have written a new extension method that works with EF Core, and has more functionality. You can […]
[…] if that wasn’t exciting enough, I followed it with the revelation that EF Core can do just as well, possibly even slightly better. As that blog post ended up rather longer than I intended, I left […]