/Tests/PerformanceTests.cs

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;



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()

        {

            return new DataClassesDataContext(Program.GetOpenConnection());

        }



		private static Func<EntityFramework.tempdbEntities1, int, EntityFramework.Post> entityFrameworkCompiled = System.Data.Objects.CompiledQuery.Compile<EntityFramework.tempdbEntities1, int, EntityFramework.Post>((db, id) => db.Posts.First(p => p.Id == id));



		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)

        {

            var tests = new Tests();



            var l2scontext1 = GetL2SContext();

            tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq 2 SQL");



            var l2scontext2 = GetL2SContext();

            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();

            tests.Add(id => l2scontext3.ExecuteQuery<Post>("select * from Posts where Id = {0}", id).First(), "Linq 2 SQL ExecuteQuery");

            

            var entityContext = new EntityFramework.tempdbEntities1();

            entityContext.Connection.Open();

            tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity framework");



            var entityContext2 = new EntityFramework.tempdbEntities1();

            entityContext2.Connection.Open();

            tests.Add(id => entityContext2.ExecuteStoreQuery<Post>("select * from Posts where Id = {0}", id).First(), "Entity framework ExecuteStoreQuery");



			var entityContext3 = new EntityFramework.tempdbEntities1();

			entityContext3.Connection.Open();

			tests.Add(id => entityFrameworkCompiled(entityContext3, id), "Entity framework CompiledQuery");



			var entityContext4 = new EntityFramework.tempdbEntities1();

			entityContext4.Connection.Open();

			tests.Add(id => entityContext4.Posts.Where("it.Id = @id", new System.Data.Objects.ObjectParameter("id", id)).First(), "Entity framework ESQL");



			var entityContext5 = new EntityFramework.tempdbEntities1();

			entityContext5.Connection.Open();

			entityContext5.Posts.MergeOption = System.Data.Objects.MergeOption.NoTracking;

			tests.Add(id => entityContext.Posts.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: true).First(), "Dynamic Mapper Query (non-buffered)");



            // dapper.contrib

            var mapperConnection3 = Program.GetOpenConnection();

            tests.Add(id => mapperConnection2.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), "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), "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");



			// Soma

			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 connection = Program.GetOpenConnection();



            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;

        }

    }

}