Entity Framework 6 vs EL6 DAAB Performance - Rematched

After I published the benchmark results for Entity Framework (EF) vs. DAAB, I was asked to verify whether the performance of EF can be improved by using Stored Procedures (SP). Logically speaking, using SP may help improve performance, but it should improve for both frameworks and not favour one over the other. Meaning if DAAB is shown to be faster than EF, any tuning on the database side should not be able to make EF faster than DAAB - at best, it can only make EF perform better than its own previous results.

To make sure there are no stones unturned, I have decided to do a rematch of the performance testing using the same methods and machine specifications from the previous test. The only differences are the code have all been converted to use Stored Procedures.

Here's a code snippet for one of the methods used in the DAAB code that has been converted to use SP:

public Leave SelectById(long leaveID)

{

    const string SQL_STATEMENT = "GetLeaveById";

    Leave leave = null;

    // Connect to database.

    Database db = DatabaseFactory.CreateDatabase(CONNECTION_NAME);

    using (DbCommand cmd = db.GetStoredProcCommand(SQL_STATEMENT))

    {

        db.AddInParameter(cmd, "@LeaveID", DbType.Int64, leaveID);

        using (IDataReader dr = db.ExecuteReader(cmd))

        {

            if (dr.Read())

            {

                // Create a new Leave

                leave = LoadLeave(dr);

            }

        }

    }

    return leave;

}

And here's the equivalent method in EF:

public Leave SelectById(long leaveID)

{

    using (var db = new DbContext(CONNECTION_NAME))

    {

        var paramLeaveID = CreateParameter("LeaveID", DbType.Int64, leaveID);

        var result = db.Database.SqlQuery<Leave>("GetLeaveById @LeaveID",

           paramLeaveID).ToList<Leave>();

        Leave leave = result.Count > 0 ? result[0] : null;

        // Return result.

        return leave;

    }

}

All the SPs were imported and mapped in the EF Model.

I was also tipped-off by some friends who have experience in EF to try to have these settings set to improvement performance.

db.Configuration.LazyLoadingEnabled = false;

db.Configuration.AutoDetectChangesEnabled = false;

db.Configuration.ValidateOnSaveEnabled = false;

However, they don't make much difference because the Entities that were used are Plain-Old-CLR-Objects (POCO) so change-tracking is not available, and they are not linked by navigation properties, so lazy-loading won't kick-in.

Now let the rematch begin.

Unit Test: Single Run

The ApplyThenApproveTest unit test method was chosen again for the test and the results are:

Method using Entity Framework 6.0.2 took 168 ms to complete 1 transaction.

Method using Enterprise Library 6.0 DAAB took 137 ms to complete 1 transaction.

Looks like the performance of EF has indeed been improved a little with SP but the performance of DAAB has dropped slightly. Nevertheless, the performance gap between EF and DAAB is almost consistent compared to the previous non-SP test.

Unit Test: Looping in 1000

Let's see how both of them fair in loops.

Method using Entity Framework 6.0.2 took 46 secs to complete 1000 transactions.

Method using Enterprise Library 6.0 DAAB took 32 secs to complete 1000 transactions.

Once again the results show that EF performs slower than DAAB and both seems to be 2 secs slower than their previous non-SP results.

Instrumented Performance and Diagnostics Profiler

Let's dig into the profiler and see some results:

Create method in data layer for Entity Framework 6.0.2 took an  Avg. Elapse Time of 1,299.91. The EF methods DbContext.SaveChanges() took Avg. Elapse Time of 1,133.98 and DbSet.Add() took 150.57.

Create method in data layer for Enterprise Library 6.0 DAAB took an Avg. Elapse Time of 23.06. The DAAB methods Database.ExecuteScalar() took Avg. Elapse Time of 19.71.

Strangely, it shows that both EF and DAAB takes longer when SPs are used (could this be due to the extra processing needed to map to SPs internally?). From the call tree we can observe that EF is still slower than DAAB. 

Load Test

Now for the grand finale! Let's see how they perform in load testing.

Entity Framework 6.0.2 completed with 6945 Total Test runs with an Avg. Test Time of 0.84 sec and gave 0 errors.

Enterprise Library 6.0 DAAB completed with 7310 Total Test runs with an Avg. Test Time of 0.79 sec and gave 0 errors.

Unlike the previous non-SP test, EF runs more stable this time and produces no errors under high load. Because of that, its performance has also been able to almost match the new results for DAAB. This could be due to the more optimized SQL statements which are used in the SPs as opposed to the ones auto-generated by EF's engine. The performance of DAAB somewhat fell when using SPs.

Behind The Scene

Let's take a look at what's going on behind the scene.

INSERT operation using Stored Procedure using Entity Framework 6.0.2

INSERT operation using Stored Procedure using Enterprise Library 6.0 DAAB

SELECT operation using Stored Procedure using Entity Framework 6.0.2

SELECT operation using Stored Procedure using Enterprise Library 6.0 DAAB

From SQL Server Profiler, we can see that both EF and DAAB now use the specified stored procedures to perform their operations. Curiously, EF still uses sp_executesql for the queries but you can see that it is no longer using the verbose auto-generated SQL statements previously.

Conclusion

It is common for us to think that using Stored Procedures (SP) may help improve the performance of our applications but it seems that RDBMS have came a long way and have evolved tremendously in providing us with the required performance. It should be noted that both native ADO.NET and EF uses sp_executesql internally which provides us the safety of preventing SQL-injection attacks (when used with named parameters) and cached execution plans. While sp_executesql can also be tuned further, it is suffice for general purpose usage.

The results in this rematch may not be able to prove that EF could perform better than DAAB by employing SPs but it does prove that EF is more stable and provides better query performance with properly written SPs. The only challenge I experienced is the rigid nature of SPs which makes it difficult to accommodate EF's dynamic nature i.e. difficult to support dynamic column sorting and parameter filtering, and I would certainly not encourage constructing dynamic SQL inside SPs.

For now, I will still need to stick with DAAB.

Disclaimer: The results are based on my own research and it is just a simple benchmark for EF and DAAB. It is not about performance tuning. If you are unhappy with the results, I would urge that you conduct your own tests in your own environment for verification. You never know, some things work differently in different environments.