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)));
}
}
}
Posts
