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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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

What's the difference between Entity Framework, LINQ to SQL, and ADO.NET with stored procedures?

First off, if you're starting a new project, go with Entity Framework ("EF") - it now generates much better SQL (more like Linq to SQL does) and is easier to maintain and more powerful than Linq to SQL ("L2S"). As of the release of .NET 4.0, I consider Linq to SQL to be an obsolete technology. MS has been very open about not continuing L2S develo...
ado.net entity-framework linq-to-entities linq-to-sql sql
asked by Dave Markle

Linq to Entities vs. Linq to SQL vs. Entity Framework

There are two ways of writing queries with Entity Framework:...Linq to Entities ( ...http://msdn.microsoft.com/en-us/library/bb386964.aspx... )...Entity SQL ( ...http://msdn.microsoft.com/en-us/library/bb387145.aspx... )...L2E is not a separate technology, but rather a part of EF....Have a look at this article for a comparison: ...http://thedatafar...
ado.net entity-framework linq-to-entities linq-to-sql
asked by Yakimych

Performance Comparison between Entity Framework and Stored Procedures

In agreement with @Wouter de Kort ... Moreover, when you need to move to procedures, you can use EF in conjunction with procedures to assist migration from one to the other....Moving to procedures will be faster in a typical application if you ...unify functionality... into well designed procedures. i.e. Get as much work done in ...one... sproc cal...
database-performance entity-framework performance-testing stored-procedures
asked by MikeM

Database upgrade problem with DevExpress XPO, NHibernate, and Entity Framework

I can't comment on the other ORM's, but I have used DevExpress XPO for a corporate treasury application since 2007. The schema changes a little with every release but there have also been some big schema changes over the years as well. A somewhat extended version of the default XPO upgrade mechanism has comfortably catered for all the changes....
devexpress entity-framework nhibernate upgrade xpo
asked by shamp00

Linq To Entities - Any VS First VS Exists

Okay, I wasn't going to weigh in on this, but Diego's answer complicates things enough that I think some additional explanation is in order....In most cases, ....Any()... will be faster.... Here are some examples....Workflows.Where(w => w.Activities.Any()) Workflows.Where(w => w.Activities.Any(a => a.Title == "xyz")) ...In the above two examples, E...
any ef-code-first entity-framework exists
asked by StriplingWarrior

Performance of Linq on entity framework vs SQL stored procedure

I suspect the the major performance issue is due to a major gripe of mine. Abuse of the keyword join....Due to the usage of JOIN, you are getting too many results. So you then used a DISTINCT. Worse, you did so for the outer result set, which SQL server has no index on....var brands = from b in context.Brands where (from m in context.Materials...
c# entity-framework linq performance
asked by Aron

Entity Framework - CreateQuery VS ExecuteFunction VS ExecuteStoreQuery VS ExecuteStoreCommand

Very similar to ...CreateQuery... would be following:...var id = 42; using(var ctx = new Entities()){ var query = ctx.Companies.Where(o=>o.Id==id); var result = query.First(); } ...Before I call First() query is just a query. Nothing has been sent to the database. Only when data is asked will the query get executed and data retrieved. ...Writin...
c# difference entity-framework executestorequery
asked by Margus

Entities VS Domain Models VS View Models

I think you're just having issues with defining what each layer is and what role it is playing in your solution....Data Tier...Your data tier is simply your database / SharePoint list / .csv file / excel sheet... you get the idea, it's simply where your data is stored, and it can be in any format. So remember that the data tier is nothing more than...
architecture asp.net-mvc c# design-patterns entity-framework
asked by Aydin

Entity vs Model vs View Model

The term "Model" is ambiguous. They are all models....Entity Model...A class which closely resembles structure in persistence. A ...MemberEntity... is a model which represents one member row in the Members table in a database. Not strictly tied to a Database, but some entity of some persistence. Typically has an "ID" property such as "int Membe...
asp.net-mvc c# entity-framework mvvm
asked by Suamere

Entity Framework SaveChanges() vs. SaveChangesAsync() and Find() vs. FindAsync()

Any time that you need to do an action on a remote server, your program generates the request, sends it, then waits for a response. I will use ...SaveChanges()... and ...SaveChangesAsync()... as an example but the same applies to ...Find()... and ...FindAsync().......Say you have a list ...myList... of 100+ items that you need to add to your databa...
async-await c# entity-framework
asked by Jacob Lambert

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

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