In SQL Server, Entity Framework 6 vs. Dapper vs. stored procedures vs. requests

dapper database entity-framework linq sql-server

Question

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.

ZipCode table

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?

1
0
3/17/2019 10:32:20 AM

Popular Answer

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.

  • Loading too much data.
  • Loading data too often.

The key things I look out for: (as a start, there are a lot more items, but these are the big wins)

  1. 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.

    • Hunting Method: Hook up an SQL profiler against a database that just your debugging instance of the app is running against. (I.e. a local DB)
    • Symptom: Seeing a whole lot of "SELECT TOP (1)..." queries after a main query to load a single entity, or collection.
    • Fix: The quick fix is to introduce eager loads (.Include()) to load these collections. The better fix is to use .Select() to just load the properties needed by the code in question.
  2. .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!

    • Hunting Method: Look for instances of .ToList() and flag any cases where you find a .ToList() before a .Select() or .Where() etc.
    • Symptom: Removing the extra .ToList() causes an EF error.
    • Fix: Check to see if the offending function has a DbFunctions equivalent. A common problem is working with DateTime functions which can be found in DbFunctions. In other cases, find the offending function and create a View Model for the expected data that is being selected, then create a property to run the function in the view model.
  3. 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)

    • Hunting Method: Look at any API/Controller method that returns collections. Do these queries use .ToList() or .Skip()+.Take()? Do these methods return back entities, or view models?
    • Symptom: Paginated lists are really slow to load. Once loaded, switching pages is fast.
    • Fix: Calls from pagination controls should be modified to use server-side pagination. This means sending sorting, page size, and page # information to the server calls. This way the EF queries can be made more efficient to load just the # of rows needed to be displayed by the control. The code should also return view models for the search results which only present the columns displayed, and the keys needed to load a full entity on demand. (such as when a user clicks to open a record.) Entities can be heavyweight when you only need to display a handful of fields.
  4. 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.

    • Hunting Method: Do you have a dedicated DBA that is looking after the database and giving the all-clear that it is performing up to scratch?
    • Symptoms: No DBA or scrutiny has been given to the database. The application is using GUID PKs for example which are set to NEWID() or 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.
    • Fix: Hire a DBA. If you are using GUID keys, switch over to NEWSEQUENTIALID() and establish some scheduled index maintenance jobs.
4
3/17/2019 10:09:46 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow