/Tests/PerformanceTests.cs
C# | 327 lines | 252 code | 54 blank | 21 comment | 11 complexity | c6d0121684a0dc21e5935594b11ba42f MD5 | raw file
- using System;
- using System.Collections.Generic;
- using System.Data;
- using System.Data.Linq;
- using System.Data.SqlClient;
- using System.Diagnostics;
- using System.Linq;
- using BLToolkit.Data;
- using Dapper;
- using Massive;
- using NHibernate.Criterion;
- using NHibernate.Linq;
- using ServiceStack.OrmLite;
- using ServiceStack.OrmLite.SqlServer;
- using SqlMapper.Linq2Sql;
- using SqlMapper.NHibernate;
- using Dapper.Contrib.Extensions;
- using SqlMapper.EntityFramework;
- using Susanoo;
-
- namespace SqlMapper
- {
- class PerformanceTests
- {
- class Test
- {
- public static Test Create(Action<int> iteration, string name)
- {
- return new Test {Iteration = iteration, Name = name };
- }
-
- public Action<int> Iteration { get; set; }
- public string Name { get; set; }
- public Stopwatch Watch { get; set; }
- }
-
- class Tests : List<Test>
- {
- public void Add(Action<int> iteration, string name)
- {
- Add(Test.Create(iteration, name));
- }
-
- public void Run(int iterations)
- {
- // warmup
- foreach (var test in this)
- {
- test.Iteration(iterations + 1);
- test.Watch = new Stopwatch();
- test.Watch.Reset();
- }
-
- var rand = new Random();
- for (int i = 1; i <= iterations; i++)
- {
- foreach (var test in this.OrderBy(ignore => rand.Next()))
- {
- test.Watch.Start();
- test.Iteration(i);
- test.Watch.Stop();
- }
- }
-
- foreach (var test in this.OrderBy(t => t.Watch.ElapsedMilliseconds))
- {
- Console.WriteLine(test.Name + " took " + test.Watch.ElapsedMilliseconds + "ms");
- }
- }
- }
-
- static DataClassesDataContext GetL2SContext(SqlConnection connection)
- {
- return new DataClassesDataContext(connection);
- }
-
- internal class SomaConfig : Soma.Core.MsSqlConfig
- {
- public override string ConnectionString
- {
- get { return Program.ConnectionString; }
- }
-
- public override void Log(Soma.Core.PreparedStatement preparedStatement)
- {
- // no op
- }
- }
-
- public void Run(int iterations)
- {
- using (var connection = Program.GetOpenConnection())
- {
- var tests = new Tests();
-
- var l2scontext1 = GetL2SContext(connection);
- tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq 2 SQL");
-
- var l2scontext2 = GetL2SContext(connection);
- var compiledGetPost = CompiledQuery.Compile((Linq2Sql.DataClassesDataContext ctx, int id) => ctx.Posts.First(p => p.Id == id));
- tests.Add(id => compiledGetPost(l2scontext2, id), "Linq 2 SQL Compiled");
-
- var l2scontext3 = GetL2SContext(connection);
- tests.Add(id => l2scontext3.ExecuteQuery<Post>("select * from Posts where Id = {0}", id).First(), "Linq 2 SQL ExecuteQuery");
-
- var entityContext = new EFContext(connection);
- tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity framework");
-
-
- var entityContext2 = new EFContext(connection);
- tests.Add(id => entityContext2.Database.SqlQuery<Post>("select * from Posts where Id = {0}", id).First(), "Entity framework SqlQuery");
-
- //var entityContext3 = new EFContext(connection);
- //tests.Add(id => entityFrameworkCompiled(entityContext3, id), "Entity framework CompiledQuery");
-
- //var entityContext4 = new EFContext(connection);
- //tests.Add(id => entityContext4.Posts.Where("it.Id = @id", new System.Data.Objects.ObjectParameter("id", id)).First(), "Entity framework ESQL");
-
- var entityContext5 = new EFContext(connection);
- tests.Add(id => entityContext5.Posts.AsNoTracking().First(p => p.Id == id), "Entity framework No Tracking");
-
- var mapperConnection = Program.GetOpenConnection();
- tests.Add(id => mapperConnection.Query<Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Mapper Query (buffered)");
- tests.Add(id => mapperConnection.Query<Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Mapper Query (non-buffered)");
-
- var mapperConnection2 = Program.GetOpenConnection();
- tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dynamic Mapper Query (buffered)");
- tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dynamic Mapper Query (non-buffered)");
-
- // dapper.contrib
- var mapperConnection3 = Program.GetOpenConnection();
- tests.Add(id => mapperConnection3.Get<Post>(id), "Dapper.Cotrib");
-
- var massiveModel = new DynamicModel(Program.ConnectionString);
- var massiveConnection = Program.GetOpenConnection();
- tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).First(), "Dynamic Massive ORM Query");
-
- // PetaPoco test with all default options
- var petapoco = new PetaPoco.Database(Program.ConnectionString, "System.Data.SqlClient");
- petapoco.OpenSharedConnection();
- tests.Add(id => petapoco.Fetch<Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco (Normal)");
-
- // PetaPoco with some "smart" functionality disabled
- var petapocoFast = new PetaPoco.Database(Program.ConnectionString, "System.Data.SqlClient");
- petapocoFast.OpenSharedConnection();
- petapocoFast.EnableAutoSelect = false;
- petapocoFast.EnableNamedParams = false;
- petapocoFast.ForceDateTimesToUtc = false;
- tests.Add(id => petapocoFast.Fetch<Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco (Fast)");
-
- // Subsonic ActiveRecord
- tests.Add(id => SubSonic.Post.SingleOrDefault(x => x.Id == id), "SubSonic ActiveRecord.SingleOrDefault");
-
- // Subsonic coding horror
- SubSonic.tempdbDB db = new SubSonic.tempdbDB();
- tests.Add(id => new SubSonic.Query.CodingHorror(db.Provider, "select * from Posts where Id = @0", id).ExecuteTypedList<Post>(), "SubSonic Coding Horror");
-
- // NHibernate
-
- var nhSession1 = NHibernateHelper.OpenSession();
- tests.Add(id => nhSession1.CreateSQLQuery(@"select * from Posts where Id = :id")
- .SetInt32("id", id)
- .List(), "NHibernate SQL");
-
- var nhSession2 = NHibernateHelper.OpenSession();
- tests.Add(id => nhSession2.CreateQuery(@"from Post as p where p.Id = :id")
- .SetInt32("id", id)
- .List(), "NHibernate HQL");
-
- var nhSession3 = NHibernateHelper.OpenSession();
- tests.Add(id => nhSession3.CreateCriteria<Post>()
- .Add(Restrictions.IdEq(id))
- .List(), "NHibernate Criteria");
-
- var nhSession4 = NHibernateHelper.OpenSession();
- tests.Add(id => nhSession4
- .Query<Post>()
- .Where(p => p.Id == id).First(), "NHibernate LINQ");
-
- var nhSession5 = NHibernateHelper.OpenSession();
- tests.Add(id => nhSession5.Get<Post>(id), "NHibernate Session.Get");
-
- // bltoolkit
- var db1 = new DbManager(Program.GetOpenConnection());
- tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList<Post>(), "BLToolkit");
-
- // Simple.Data
- var sdb = Simple.Data.Database.OpenConnection(Program.ConnectionString);
- tests.Add(id => sdb.Posts.FindById(id), "Simple.Data");
-
- //Susanoo
- var susanooDb = new DatabaseManager("Smackdown.Properties.Settings.tempdbConnectionString");
- var susanooDb2 = new DatabaseManager("Smackdown.Properties.Settings.tempdbConnectionString");
-
- var susanooPreDefinedCommand =
- CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
- .DefineResults<Post>()
- .Realize("PostById");
-
- var susanooDynamicPreDefinedCommand =
- CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
- .DefineResults<dynamic>()
- .Realize("DynamicById");
-
- tests.Add(Id =>
- CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
- .DefineResults<Post>()
- .Realize("PostById")
- .Execute(susanooDb, new { Id }).First(), "Susanoo Mapping Cache Retrieval");
-
- tests.Add(Id =>
- CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
- .DefineResults<dynamic>()
- .Realize("DynamicById")
- .Execute(susanooDb, new { Id }).First(), "Susanoo Dynamic Mapping Cache Retrieval");
-
- tests.Add(Id =>
- susanooDynamicPreDefinedCommand
- .Execute(susanooDb, new { Id }).First(), "Susanoo Dynamic Mapping Static");
-
- tests.Add(Id =>
- susanooPreDefinedCommand
- .Execute(susanooDb, new { Id }).First(), "Susanoo Mapping Static");
-
- // Soma
- // DISABLED: assembly fail loading FSharp.PowerPack, Version=2.0.0.0
- // var somadb = new Soma.Core.Db(new SomaConfig());
- // tests.Add(id => somadb.Find<Post>(id), "Soma");
-
- //ServiceStack's OrmLite:
- OrmLiteConfig.DialectProvider = SqlServerOrmLiteDialectProvider.Instance; //Using SQL Server
- IDbCommand ormLiteCmd = Program.GetOpenConnection().CreateCommand();
- tests.Add(id => ormLiteCmd.QueryById<Post>(id), "OrmLite QueryById");
-
- // HAND CODED
-
- var postCommand = new SqlCommand();
- postCommand.Connection = connection;
- postCommand.CommandText = @"select Id, [Text], [CreationDate], LastChangeDate,
- Counter1,Counter2,Counter3,Counter4,Counter5,Counter6,Counter7,Counter8,Counter9 from Posts where Id = @Id";
- var idParam = postCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int);
-
- tests.Add(id =>
- {
- idParam.Value = id;
-
- using (var reader = postCommand.ExecuteReader())
- {
- reader.Read();
- var post = new Post();
- post.Id = reader.GetInt32(0);
- post.Text = reader.GetNullableString(1);
- post.CreationDate = reader.GetDateTime(2);
- post.LastChangeDate = reader.GetDateTime(3);
-
- post.Counter1 = reader.GetNullableValue<int>(4);
- post.Counter2 = reader.GetNullableValue<int>(5);
- post.Counter3 = reader.GetNullableValue<int>(6);
- post.Counter4 = reader.GetNullableValue<int>(7);
- post.Counter5 = reader.GetNullableValue<int>(8);
- post.Counter6 = reader.GetNullableValue<int>(9);
- post.Counter7 = reader.GetNullableValue<int>(10);
- post.Counter8 = reader.GetNullableValue<int>(11);
- post.Counter9 = reader.GetNullableValue<int>(12);
- }
- }, "hand coded");
-
- DataTable table = new DataTable
- {
- Columns =
- {
- {"Id", typeof (int)},
- {"Text", typeof (string)},
- {"CreationDate", typeof (DateTime)},
- {"LastChangeDate", typeof (DateTime)},
- {"Counter1", typeof (int)},
- {"Counter2", typeof (int)},
- {"Counter3", typeof (int)},
- {"Counter4", typeof (int)},
- {"Counter5", typeof (int)},
- {"Counter6", typeof (int)},
- {"Counter7", typeof (int)},
- {"Counter8", typeof (int)},
- {"Counter9", typeof (int)},
- }
- };
- tests.Add(id =>
- {
- idParam.Value = id;
- object[] values = new object[13];
- using (var reader = postCommand.ExecuteReader())
- {
- reader.Read();
- reader.GetValues(values);
- table.Rows.Add(values);
- }
- }, "DataTable via IDataReader.GetValues");
-
- tests.Run(iterations);
- }
- }
-
- }
-
- static class SqlDataReaderHelper
- {
- public static string GetNullableString(this SqlDataReader reader, int index)
- {
- object tmp = reader.GetValue(index);
- if (tmp != DBNull.Value)
- {
- return (string)tmp;
- }
- return null;
- }
-
- public static Nullable<T> GetNullableValue<T>(this SqlDataReader reader, int index) where T : struct
- {
- object tmp = reader.GetValue(index);
- if (tmp != DBNull.Value)
- {
- return (T)tmp;
- }
- return null;
- }
- }
- }