I have EF with LINQ with SQL Server in my project a long time.
And I was searching for a way to improve a performance of my requests to DB. And I read a lot of about Dapper and Procedures, that it's faster than EF. I added Dapper to project, I added procedure.... but my tests showed strange results. EF and Dapper and stored procedures have almost the same results - there are no benefits.
First of all, I checked with a request which has a lot of Join. I got almost the same results between Dapper and Procedure and EF. Then I decided to do a test with one simple table without relations.
I have table ZipCodes. There are 43200 records.
I made test by 1 000 records, 10 000 records and 43200 records, using EF, Dapper, stored procedure, and request in SQL Server.
Dapper
string query =
"SELECT TOP (43200) [Zip]\r\n ,[City]\r\n ,[State]\r\n ,[Latitude]\r\n ,[Longitude]\r\n ,[TimeZone]\r\n ,[DST]\r\n FROM [dbo].[ZipCodes]";
using (IDbConnection connection = new SqlConnection(_connectionString))
{
var result = connection.QueryAsync<ZipCodes>(query).Result.ToList();
return result;
}
EF
var zip = db.ZipCodes.AsNoTracking().Take(43200).ToList();
Stored procedure
ALTER PROCEDURE [dbo].[ZIPTest]
AS
BEGIN
SELECT TOP (43200)
[Zip], [City], [State], [Latitude], [Longitude], [TimeZone], [DST]
FROM
[dbo].[ZipCodes]
END
Request in SQL Server with time
SELECT GETDATE();
SELECT TOP (43200)
[Zip], [City], [State], [Latitude], [Longitude], [TimeZone], [DST]
FROM
[dbo].[ZipCodes]
SELECT GETDATE();
In the code I use Stopwatch
string first = "", second = "", third="";
System.Diagnostics.Stopwatch swatch = new System.Diagnostics.Stopwatch();
swatch = new Stopwatch();
swatch.Start(); Dapper request;
then
swatch.Stop();
first = swatch.Elapsed.ToString(@"m\:ss\.fff");
swatch = new Stopwatch();
swatch.Start();
And so on
Results: (in milliseconds)
1000 10000 43200
-------------------------------------------------
EF 107 1085 4527
Dapper 139 1084 4036
Stored procedure 129 1089 4519
SQL query 8 17 60
Difference between EF, Dapper and a stored procedure are very small. Why is it so?
And why is the query in SQL Server so fast and requests from code 15-70 times slower?
Is it OK or no?
Code using EF exhibiting performance issues will not magically run faster with Dapper or ADO + Sprocs. To get to the bottom of the performance problem you need to investigate and eliminate the causes of the performance issues.
At the top level, these performance issues stem from two core problems.
The key things I look out for: (as a start, there are a lot more items, but these are the big wins)
Lazy Loading: This is where the code is loading from a set of related entities, but in doing so, the code is accessing these related entities after the initial load, causing each of these related entities to be loaded individually.
.Include()
) to load these collections. The better fix is to use .Select()
to just load the properties needed by the code in question..ToList()
: Misplaced .ToList()
calls can cause huge performance headaches as systems mature because developers encountered a problem with EF that was solved by calling a .ToList
. Ususally these appear when developers try calling a method inside a .Where()
or .Select()
expression. EF cannot understand these to pass an SQL equivalent, so adding the .ToList()
converts it to Linq2Object and "ta-da" it works!
.ToList()
and flag any cases where you find a .ToList()
before a .Select()
or .Where()
etc..ToList()
causes an EF error.Client-side pagination + entities: Another sin of development without proper sample data. Queries are written that effectively return all data without consideration to the total # of records. The data gets displayed client-side in a grid with pagination, which "works" but is really really slow. It worked fine when there were only 200 rows in the database, but now crawls with 50,000. (and will only get worse)
.ToList()
or .Skip()
+.Take()
? Do these methods return back entities, or view models?Database indexing: Has the database been monitored and maintained? Are there indexes in place, and index maintenance? For SQL Server, is the database being backed up and having it's Tx Log shrunk? Code First implementations are rife with these issues where a system is spun up to life without any consideration to the backing database. As systems grow, no care is given to the database supporting it.
Guid.New()
without any index maintenance. The database has no indexes set. The Transaction Log (.LDF) is 15x larger than the database file (.MDF) etc. etc. etc.