Stored Procedures with Entity Framework - Performance Benchmark

Object-relational mapping (ORM) frameworks are crucial part of the application. No one wants to write
the raw queries and working with raw data anymore. Recently I got a chance to perform some performance
tests between Entity Framework (EF 6) regular approach and calling stored procedure using EF. The test was
very simple and results were surprising. Just assume we have some web application and we are doing logging
for each request (request time, IP, user etc.) in database using EF. There is a logging table in the database 
with the name RequestLogging  and it has following columns (just for demonstration purpose):

• Id (int - Identity)
• RequestTime (datetime)
• OperationId (int) [Foreign key from Operation table]
• IpAddress (varchar(20))
• UserId (int) [Foreign key from User table]
• RequestStatus (char(1)) [S - succes and F - failed]

The Test Environment

The test application is very simple. It has entity model (edmx) file which contains all the tables

in the database (there are total 3 tables) and stored procedure named as 'AddRequestLog'.

Main method contains two code blocks which are saving sample RequestLogging object into 

database. The first block is calling stored procedure using entity framework and the other block

is adding the object into DbContext and calling save changes. Both block contains stopwatch

to measure the time. Please see the code below which demonstrate the scenario:

static void Main(string[] args)
{
    var totalIterations = 1000;

    {
        var stopwatch = new Stopwatch();
        var entitiesContext = new TestDbEntities();

        stopwatch.Start();
        for (int i = 0; i < totalIterations; i++)
        {
            try
            {
                // Creating sample logging object.
                var sampleRequestLog = new RequestLogging
                {
                    RequestTime = DateTime.UtcNow,
                    OperationId = 1,
                    IpAddress = "127.0.0.1",
                    UserId = 1,
                    RequestStatus = "S"
                };

                // Calling stored procedure to save the object.
                entitiesContext.AddRequestLog(
                    sampleRequestLog.RequestTime
                    , sampleRequestLog.OperationId
                    , sampleRequestLog.IpAddress
                    , sampleRequestLog.UserId
                    , sampleRequestLog.RequestStatus);
            }
            catch (Exception e)
            {
            }
        }

        stopwatch.Stop();
        Console.WriteLine("Total time using Stored Procedure with EF: {0}", stopwatch.Elapsed);
    }

    {
        var stopwatch = new Stopwatch();
        var entitiesContext = new TestDbEntities();

        stopwatch.Start();
        for (int i = 0; i < totalIterations; i++)
        {
            try
            {
                // Creating sample logging object.
                var sampleRequestLog = new RequestLogging
                {
                    RequestTime = DateTime.UtcNow,
                    OperationId = 1,
                    IpAddress = "127.0.0.1",
                    UserId = 1,
                    RequestStatus = "S"
                };

                // Adding object into DbContext.
                entitiesContext.RequestLoggings.Add(sampleRequestLog);

                // Calling save changes after every object to demonstrate the
                // real-time scenario where context will be created for each request.
                entitiesContext.SaveChanges();
            }
            catch (Exception e)
            {
            }
        }

        stopwatch.Stop();
        Console.WriteLine("Total time using EF regular way: {0}", stopwatch.Elapsed);
    }

    Console.ReadLine();
}

I ran the above code three times and following are the results:

Performance Results - 1

Performance Results - 2

Performance Results - 3

The stored procedure approach is almost Three Times faster than the regular approach.

Test At Home

You can try the test at home at no cost. The sample code is available on github at Performance Test.

There is sql file to create the database in the same git repository. Once you clone the repo locally,

run the sql file to create MS SQL Server database, update the connection string in app.config file

and you are good to run the code. Note that you might get different figures in terms of time depending

on your machine; but the difference should be noticeable.