dapper-dot-net /Tests/PerformanceTests.cs

Language C# Lines 295
MD5 Hash c68a8d49fbd3e47a9f695b6716a2efdb
Repository https://github.com/jomit/dapper-dot-net.git View Raw File
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
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;
        }
    }
}
Back to Top