Tuesday, October 16, 2012

ADO.NET + DataTables vs Dapper.NET Benchmarks

In my previous blog post I was looking at Dapper - basic insert and select operations versus an raw ADO.NET. In order to perform some basic speed tests (and compare lines of code written) I added a new page to my project called Benchmark.aspx with a single PlaceHolder control (called Container) to hold the results.

The code and results below demonstrate that for large queries using Dapper can cut execution time by about four fifths (in my not very scientific tests). If you're thinking of using Dapper you can use/modify the code below to prove to yourself that it will be faster. This is basic boiler plate code that you can modify to perform your own analysis. Please excuse the inline SQL!

As a small aside Marc (the creator) says that in later builds of Dapper.NET you don't even have to open the connection - it will do it for you!

For more comprehensive and detailed benchmarks please see this post here


Results
Dapper inserts: 100, time taken: 00:00:00.4724731
Dapper inserts: 1000, time taken: 00:00:05.3936728
Dapper inserts: 10000, time taken: 00:00:15.3227475

ADO.NET inserts: 100, time taken: 00:00:00.0400515
ADO.NET inserts: 1000, time taken: 00:00:03.8124865
ADO.NET inserts: 10000, time taken: 00:00:18.4029055

Dapper Select: 10, time taken: 00:00:00.0164510
Dapper Select: 100, time taken: 00:00:00.0017245
Dapper Select: 1000, time taken: 00:00:00.0033249
Dapper Select: 10000, time taken: 00:00:00.0296995

DataTable Select: 10, time taken: 00:00:00.0008202
DataTable Select: 100, time taken: 00:00:00.0014142
DataTable Select: 1000, time taken: 00:00:00.0097499
DataTable Select: 10000, time taken: 00:00:00.1048403

Code:

using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace DapperSandbox
{
    public partial class BenchMark : System.Web.UI.Page
    {
        public class DapperUser
        {
            public Guid Id { get; set; }
            public string Firstname { get; set; }
            public string Surname { get; set; }
            public string Email { get; set; }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            RunDapperInsertBenchMark(100);
            RunDapperInsertBenchMark(1000);
            RunDapperInsertBenchMark(10000);

            RunAdoInsertBenchMark(100);
            RunAdoInsertBenchMark(1000);
            RunAdoInsertBenchMark(10000);

            RunDapperSelectBenchmark(100);
            RunDapperSelectBenchmark(1000);
            RunDapperSelectBenchmark(10000);

            RunDataTableSelectBenchmark(100);
            RunDataTableSelectBenchmark(1000);
            RunDataTableSelectBenchmark(10000);
        }

        private void RunDapperInsertBenchMark(int iterations)
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            for (int i = 0; i < iterations; i++)
            {
                using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
                {
                    connection.Open();
                    connection.Execute("UserInsert", new { Id = Guid.NewGuid(), Firstname = "First", Surname = "Second", Email = "Email" },
                        commandType: CommandType.StoredProcedure);
                }
            }
            stopwatch.Stop();
       Container.Controls.Add(new LiteralControl(string.Format("Dapper inserts: {0}, time taken: {1}
", iterations.ToString(), stopwatch.Elapsed)));
        }

        private void RunAdoInsertBenchMark(int iterations)
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            for (int i = 0; i < iterations; i++)
            {
                using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand("UserInsert", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Id", Guid.NewGuid());
                        command.Parameters.AddWithValue("@Firstname", "First");
                        command.Parameters.AddWithValue("@Surname", "Second");
                        command.Parameters.AddWithValue("@Email", "Email");
                        command.ExecuteNonQuery();
                    }
                }
            }
            stopwatch.Stop();
       Container.Controls.Add(new LiteralControl(string.Format("ADO.NET inserts: {0}, time taken: {1}
", iterations.ToString(), stopwatch.Elapsed)));
        }

        private void RunDapperSelectBenchmark(int numberOfResults)
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
            {
                connection.Open();
                var rows = connection.Query("SELECT TOP " + numberOfResults.ToString() + " * FROM dbo.[User]", commandType: CommandType.Text).ToList();
            }
            stopwatch.Stop();
            Container.Controls.Add(new LiteralControl(string.Format("Dapper Select: {0}, time taken: {1}
", numberOfResults.ToString(), stopwatch.Elapsed)));
        }

        private void RunDataTableSelectBenchmark(int numberOfResults)
        {
            Stopwatch stopwatch = new Stopwatch();
            stopwatch.Start();
            DataTable results = new DataTable();
            List user = new List();
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand("SELECT TOP " + numberOfResults.ToString() + " * FROM dbo.[User]",connection))
                {
                    command.CommandType = CommandType.Text;
                    results.Load(command.ExecuteReader());
                    foreach (DataRow row in results.Rows)
                    {
                        user.Add(new DapperUser() { Id = Guid.Parse(row["id"].ToString()), Firstname = row["Firstname"].ToString(), Surname = row["Surname"].ToString(), Email = row["Email"].ToString() });
                    }
                }
            }
            stopwatch.Stop();
            Container.Controls.Add(new LiteralControl(string.Format("DataTable Select: {0}, time taken: {1}
", numberOfResults.ToString(), stopwatch.Elapsed)));
        }
    }
}

2 comments:

Anonymous said...

This is precisely the important information I'd been searching for. Incredible blog. Very inspirational! Your posts are so good and also detailed. The links you come with are also very beneficial as well. Many thanks :)

Anonymous said...

magnificent issues altogether, you just received a new reader. What may you recommend in regards to your submit that you simply made some days in the past? Any positive?