EF6 vs Dapper vs EF View

P

P00HB33R

Senior Member
Joined
Jul 15, 2010
Messages
706
Reaction score
29
Location
Eloff
For those interested,

I have been spending a lot of time the last few days focusing on performance in my MVC app.
Here are some results:

4800 records, 12 columns each, with 2 joins from child table, each having 3 columns, thus 18 Columns in total, all orderder by ID Descending in parent table:
*EF6 AsNoTracking Enabled

EF6 Dynamic Object Creation: Avg. Execution Time - 83ms
Dapper Dynamic Object Creation: Avg. Execution Time- 152ms
EF6 Dynamic Object From View: Avg. Execution Time- 174ms

So, seems like EF6 is quite fast compared to version 5 & 4. But was quite suprised that EF6 beat Dapper.

Below the query code that won the race:
Code: 
db.Configuration.AutoDetectChangesEnabled = false;
                var x = db.tbl_Load.AsNoTracking()
                    .Select(a => new
                    {
                        a.ID,
                        a.Customer_ID,
                        a.Driver_ID,
                        a.EntryDate,
                        a.FTrailer_ID,
                        a.Horse_ID,
                        a.IsHazchem,
                        a.LoadCancelled,
                        a.LoadComplete,
                        a.LoadDeleted,
                        a.RTrailer_ID,
                        a.Status,
                        a.Transporter_ID,
                        LoadingSite = a.tbl_Load_LoadingSite.Select(b => new { LoadingID = b.ID, LoadingDate = b.LoadDate, LoadingSiteID = b.Loading_Site_ID }).OrderBy(c => c.LoadingDate).FirstOrDefault(),
                        OffloadingSite = a.tbl_Load_OffloadingSite.Select(b => new { OffloadingID = b.ID, OffloadingDate = b.OffloadDate, OffloadingSiteID = b.Offloading_Site_ID }).OrderByDescending(c => c.OffloadingDate).FirstOrDefault()
                    }
                    )
                    .OrderByDescending(a=> a.ID)
                    .ToList();

What are your experiences?
 
That is interesting as Dapper is normally the fastest of the lot being just a basic sql wrapper.

Anyone else replicate the results? Maybe put a trace and see what execution plans each kicks off if the speed dif is on a sql level or pure code.
 
Kosmik said:
That is interesting as Dapper is normally the fastest of the lot being just a basic sql wrapper.

Anyone else replicate the results? Maybe put a trace and see what execution plans each kicks off if the speed dif is on a sql level or pure code.
Click to expand...

I did run SQL profiler while running tests. Same result. EF6 Dynamic Object creation performed fastest overall. Query structures differed a bit here and there. Something to mention thought, when using EF6 vanilla with no adjustments, EF6 fails by a mile. But as per the SQL profiler results, when using EF6 vanilla, all related entities of each record queried is returned, even with LazyLoading enabled. That drops the performance from +-80ms to upto 5000ms+
 
Will post plain sql query stats when I get the chance. Will compare code vs T-SQL results.
 
Executing Query generated by each:

EF6 Dynamic Object Duration 65ms
Dapper Dynamic Object Duration 118ms
EF6 View Query Duration 151ms

Same result
 
Here is the raw SQL that both the EF6 DO & Dapper DO uses:

Code: 
SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Customer_ID] AS [Customer_ID], 
    [Extent1].[Driver_ID] AS [Driver_ID], 
    [Extent1].[EntryDate] AS [EntryDate], 
    [Extent1].[FTrailer_ID] AS [FTrailer_ID], 
    [Extent1].[Horse_ID] AS [Horse_ID], 
    [Extent1].[IsHazchem] AS [IsHazchem], 
    [Extent1].[LoadCancelled] AS [LoadCancelled], 
    [Extent1].[LoadComplete] AS [LoadComplete], 
    [Extent1].[LoadDeleted] AS [LoadDeleted], 
    [Extent1].[RTrailer_ID] AS [RTrailer_ID], 
    [Extent1].[Status] AS [Status], 
    [Extent1].[Transporter_ID] AS [Transporter_ID], 
    [Limit1].[ID] AS [ID1], 
    [Limit1].[LoadDate] AS [LoadDate], 
    [Limit1].[Loading_Site_ID] AS [Loading_Site_ID], 
    [Limit2].[ID] AS [ID2], 
    [Limit2].[OffloadDate] AS [OffloadDate], 
    [Limit2].[Offloading_Site_ID] AS [Offloading_Site_ID]
    FROM   [dbo].[tbl_Load] AS [Extent1]
    OUTER APPLY  (SELECT TOP (1) [Project1].[ID] AS [ID], [Project1].[Loading_Site_ID] AS [Loading_Site_ID], [Project1].[LoadDate] AS [LoadDate]
        FROM ( SELECT 
            [Extent2].[ID] AS [ID], 
            [Extent2].[Loading_Site_ID] AS [Loading_Site_ID], 
            [Extent2].[LoadDate] AS [LoadDate]
            FROM [dbo].[tbl_Load_LoadingSite] AS [Extent2]
            WHERE [Extent1].[ID] = [Extent2].[tbl_Load_ID]
        )  AS [Project1]
        ORDER BY [Project1].[LoadDate] ASC ) AS [Limit1]
    OUTER APPLY  (SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[Offloading_Site_ID] AS [Offloading_Site_ID], [Project2].[OffloadDate] AS [OffloadDate]
        FROM ( SELECT 
            [Extent3].[ID] AS [ID], 
            [Extent3].[Offloading_Site_ID] AS [Offloading_Site_ID], 
            [Extent3].[OffloadDate] AS [OffloadDate]
            FROM [dbo].[tbl_Load_OffloadingSite] AS [Extent3]
            WHERE [Extent1].[ID] = [Extent3].[tbl_Load_ID]
        )  AS [Project2]
        ORDER BY [Project2].[OffloadDate] DESC ) AS [Limit2]
    ORDER BY [Extent1].[ID] DESC
 
Kosmik said:
That is interesting as Dapper is normally the fastest of the lot being just a basic sql wrapper.

Anyone else replicate the results? Maybe put a trace and see what execution plans each kicks off if the speed dif is on a sql level or pure code.
Click to expand...

yep.. is the underlying SQL that is executed exactly the same? If you are also loading navigational properties then EF might be quicker and more efficient/optimised. But with straight entity/POCO loading, Dapper should smoke EF. All other things being equal of course. Best is to run the query a few times in both cases due the page caching on the DB server etc....
 
So it would look like the performance gain for EF6 Dynamic Object is on the code side, as both Dapper and EF are executing the same SQL statement.
 
P00HB33R said:
So it would look like the performance gain for EF6 Dynamic Object is on the code side, as both Dapper and EF are executing the same SQL statement.
Click to expand...

Hmm is the connection string the same? So does EF then return an object of type dynamic? As opposed to Dapper's strongly typed object. If so then that is likely the diff. Personally I always prefer strongly typed at compile time. Maybe in EF also return strongly typed object and compare.
 
Last edited:
Spacerat said:
Hmm is the connection string the same? So does EF then return an object of type dynamic? As opposed to Dapper's strongly typed object. If so then that is likely the diff. Personally I always prefer strongly typed at compile time. Maybe in EF also return strongly typed object and compare.
Click to expand...

Both dapper and ef are creating dynamic objects. Will test both with strongly typed objects.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X