PageRenderTime 38ms CodeModel.GetById 15ms RepoModel.GetById 0ms app.codeStats 0ms

/Tests/PerformanceTests.cs

http://github.com/SamSaffron/dapper-dot-net
C# | 327 lines | 252 code | 54 blank | 21 comment | 11 complexity | c6d0121684a0dc21e5935594b11ba42f MD5 | raw file
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Data.Linq;
  5. using System.Data.SqlClient;
  6. using System.Diagnostics;
  7. using System.Linq;
  8. using BLToolkit.Data;
  9. using Dapper;
  10. using Massive;
  11. using NHibernate.Criterion;
  12. using NHibernate.Linq;
  13. using ServiceStack.OrmLite;
  14. using ServiceStack.OrmLite.SqlServer;
  15. using SqlMapper.Linq2Sql;
  16. using SqlMapper.NHibernate;
  17. using Dapper.Contrib.Extensions;
  18. using SqlMapper.EntityFramework;
  19. using Susanoo;
  20. namespace SqlMapper
  21. {
  22. class PerformanceTests
  23. {
  24. class Test
  25. {
  26. public static Test Create(Action<int> iteration, string name)
  27. {
  28. return new Test {Iteration = iteration, Name = name };
  29. }
  30. public Action<int> Iteration { get; set; }
  31. public string Name { get; set; }
  32. public Stopwatch Watch { get; set; }
  33. }
  34. class Tests : List<Test>
  35. {
  36. public void Add(Action<int> iteration, string name)
  37. {
  38. Add(Test.Create(iteration, name));
  39. }
  40. public void Run(int iterations)
  41. {
  42. // warmup
  43. foreach (var test in this)
  44. {
  45. test.Iteration(iterations + 1);
  46. test.Watch = new Stopwatch();
  47. test.Watch.Reset();
  48. }
  49. var rand = new Random();
  50. for (int i = 1; i <= iterations; i++)
  51. {
  52. foreach (var test in this.OrderBy(ignore => rand.Next()))
  53. {
  54. test.Watch.Start();
  55. test.Iteration(i);
  56. test.Watch.Stop();
  57. }
  58. }
  59. foreach (var test in this.OrderBy(t => t.Watch.ElapsedMilliseconds))
  60. {
  61. Console.WriteLine(test.Name + " took " + test.Watch.ElapsedMilliseconds + "ms");
  62. }
  63. }
  64. }
  65. static DataClassesDataContext GetL2SContext(SqlConnection connection)
  66. {
  67. return new DataClassesDataContext(connection);
  68. }
  69. internal class SomaConfig : Soma.Core.MsSqlConfig
  70. {
  71. public override string ConnectionString
  72. {
  73. get { return Program.ConnectionString; }
  74. }
  75. public override void Log(Soma.Core.PreparedStatement preparedStatement)
  76. {
  77. // no op
  78. }
  79. }
  80. public void Run(int iterations)
  81. {
  82. using (var connection = Program.GetOpenConnection())
  83. {
  84. var tests = new Tests();
  85. var l2scontext1 = GetL2SContext(connection);
  86. tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq 2 SQL");
  87. var l2scontext2 = GetL2SContext(connection);
  88. var compiledGetPost = CompiledQuery.Compile((Linq2Sql.DataClassesDataContext ctx, int id) => ctx.Posts.First(p => p.Id == id));
  89. tests.Add(id => compiledGetPost(l2scontext2, id), "Linq 2 SQL Compiled");
  90. var l2scontext3 = GetL2SContext(connection);
  91. tests.Add(id => l2scontext3.ExecuteQuery<Post>("select * from Posts where Id = {0}", id).First(), "Linq 2 SQL ExecuteQuery");
  92. var entityContext = new EFContext(connection);
  93. tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity framework");
  94. var entityContext2 = new EFContext(connection);
  95. tests.Add(id => entityContext2.Database.SqlQuery<Post>("select * from Posts where Id = {0}", id).First(), "Entity framework SqlQuery");
  96. //var entityContext3 = new EFContext(connection);
  97. //tests.Add(id => entityFrameworkCompiled(entityContext3, id), "Entity framework CompiledQuery");
  98. //var entityContext4 = new EFContext(connection);
  99. //tests.Add(id => entityContext4.Posts.Where("it.Id = @id", new System.Data.Objects.ObjectParameter("id", id)).First(), "Entity framework ESQL");
  100. var entityContext5 = new EFContext(connection);
  101. tests.Add(id => entityContext5.Posts.AsNoTracking().First(p => p.Id == id), "Entity framework No Tracking");
  102. var mapperConnection = Program.GetOpenConnection();
  103. tests.Add(id => mapperConnection.Query<Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Mapper Query (buffered)");
  104. tests.Add(id => mapperConnection.Query<Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Mapper Query (non-buffered)");
  105. var mapperConnection2 = Program.GetOpenConnection();
  106. tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dynamic Mapper Query (buffered)");
  107. tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dynamic Mapper Query (non-buffered)");
  108. // dapper.contrib
  109. var mapperConnection3 = Program.GetOpenConnection();
  110. tests.Add(id => mapperConnection3.Get<Post>(id), "Dapper.Cotrib");
  111. var massiveModel = new DynamicModel(Program.ConnectionString);
  112. var massiveConnection = Program.GetOpenConnection();
  113. tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).First(), "Dynamic Massive ORM Query");
  114. // PetaPoco test with all default options
  115. var petapoco = new PetaPoco.Database(Program.ConnectionString, "System.Data.SqlClient");
  116. petapoco.OpenSharedConnection();
  117. tests.Add(id => petapoco.Fetch<Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco (Normal)");
  118. // PetaPoco with some "smart" functionality disabled
  119. var petapocoFast = new PetaPoco.Database(Program.ConnectionString, "System.Data.SqlClient");
  120. petapocoFast.OpenSharedConnection();
  121. petapocoFast.EnableAutoSelect = false;
  122. petapocoFast.EnableNamedParams = false;
  123. petapocoFast.ForceDateTimesToUtc = false;
  124. tests.Add(id => petapocoFast.Fetch<Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco (Fast)");
  125. // Subsonic ActiveRecord
  126. tests.Add(id => SubSonic.Post.SingleOrDefault(x => x.Id == id), "SubSonic ActiveRecord.SingleOrDefault");
  127. // Subsonic coding horror
  128. SubSonic.tempdbDB db = new SubSonic.tempdbDB();
  129. tests.Add(id => new SubSonic.Query.CodingHorror(db.Provider, "select * from Posts where Id = @0", id).ExecuteTypedList<Post>(), "SubSonic Coding Horror");
  130. // NHibernate
  131. var nhSession1 = NHibernateHelper.OpenSession();
  132. tests.Add(id => nhSession1.CreateSQLQuery(@"select * from Posts where Id = :id")
  133. .SetInt32("id", id)
  134. .List(), "NHibernate SQL");
  135. var nhSession2 = NHibernateHelper.OpenSession();
  136. tests.Add(id => nhSession2.CreateQuery(@"from Post as p where p.Id = :id")
  137. .SetInt32("id", id)
  138. .List(), "NHibernate HQL");
  139. var nhSession3 = NHibernateHelper.OpenSession();
  140. tests.Add(id => nhSession3.CreateCriteria<Post>()
  141. .Add(Restrictions.IdEq(id))
  142. .List(), "NHibernate Criteria");
  143. var nhSession4 = NHibernateHelper.OpenSession();
  144. tests.Add(id => nhSession4
  145. .Query<Post>()
  146. .Where(p => p.Id == id).First(), "NHibernate LINQ");
  147. var nhSession5 = NHibernateHelper.OpenSession();
  148. tests.Add(id => nhSession5.Get<Post>(id), "NHibernate Session.Get");
  149. // bltoolkit
  150. var db1 = new DbManager(Program.GetOpenConnection());
  151. tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList<Post>(), "BLToolkit");
  152. // Simple.Data
  153. var sdb = Simple.Data.Database.OpenConnection(Program.ConnectionString);
  154. tests.Add(id => sdb.Posts.FindById(id), "Simple.Data");
  155. //Susanoo
  156. var susanooDb = new DatabaseManager("Smackdown.Properties.Settings.tempdbConnectionString");
  157. var susanooDb2 = new DatabaseManager("Smackdown.Properties.Settings.tempdbConnectionString");
  158. var susanooPreDefinedCommand =
  159. CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
  160. .DefineResults<Post>()
  161. .Realize("PostById");
  162. var susanooDynamicPreDefinedCommand =
  163. CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
  164. .DefineResults<dynamic>()
  165. .Realize("DynamicById");
  166. tests.Add(Id =>
  167. CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
  168. .DefineResults<Post>()
  169. .Realize("PostById")
  170. .Execute(susanooDb, new { Id }).First(), "Susanoo Mapping Cache Retrieval");
  171. tests.Add(Id =>
  172. CommandManager.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)
  173. .DefineResults<dynamic>()
  174. .Realize("DynamicById")
  175. .Execute(susanooDb, new { Id }).First(), "Susanoo Dynamic Mapping Cache Retrieval");
  176. tests.Add(Id =>
  177. susanooDynamicPreDefinedCommand
  178. .Execute(susanooDb, new { Id }).First(), "Susanoo Dynamic Mapping Static");
  179. tests.Add(Id =>
  180. susanooPreDefinedCommand
  181. .Execute(susanooDb, new { Id }).First(), "Susanoo Mapping Static");
  182. // Soma
  183. // DISABLED: assembly fail loading FSharp.PowerPack, Version=2.0.0.0
  184. // var somadb = new Soma.Core.Db(new SomaConfig());
  185. // tests.Add(id => somadb.Find<Post>(id), "Soma");
  186. //ServiceStack's OrmLite:
  187. OrmLiteConfig.DialectProvider = SqlServerOrmLiteDialectProvider.Instance; //Using SQL Server
  188. IDbCommand ormLiteCmd = Program.GetOpenConnection().CreateCommand();
  189. tests.Add(id => ormLiteCmd.QueryById<Post>(id), "OrmLite QueryById");
  190. // HAND CODED
  191. var postCommand = new SqlCommand();
  192. postCommand.Connection = connection;
  193. postCommand.CommandText = @"select Id, [Text], [CreationDate], LastChangeDate,
  194. Counter1,Counter2,Counter3,Counter4,Counter5,Counter6,Counter7,Counter8,Counter9 from Posts where Id = @Id";
  195. var idParam = postCommand.Parameters.Add("@Id", System.Data.SqlDbType.Int);
  196. tests.Add(id =>
  197. {
  198. idParam.Value = id;
  199. using (var reader = postCommand.ExecuteReader())
  200. {
  201. reader.Read();
  202. var post = new Post();
  203. post.Id = reader.GetInt32(0);
  204. post.Text = reader.GetNullableString(1);
  205. post.CreationDate = reader.GetDateTime(2);
  206. post.LastChangeDate = reader.GetDateTime(3);
  207. post.Counter1 = reader.GetNullableValue<int>(4);
  208. post.Counter2 = reader.GetNullableValue<int>(5);
  209. post.Counter3 = reader.GetNullableValue<int>(6);
  210. post.Counter4 = reader.GetNullableValue<int>(7);
  211. post.Counter5 = reader.GetNullableValue<int>(8);
  212. post.Counter6 = reader.GetNullableValue<int>(9);
  213. post.Counter7 = reader.GetNullableValue<int>(10);
  214. post.Counter8 = reader.GetNullableValue<int>(11);
  215. post.Counter9 = reader.GetNullableValue<int>(12);
  216. }
  217. }, "hand coded");
  218. DataTable table = new DataTable
  219. {
  220. Columns =
  221. {
  222. {"Id", typeof (int)},
  223. {"Text", typeof (string)},
  224. {"CreationDate", typeof (DateTime)},
  225. {"LastChangeDate", typeof (DateTime)},
  226. {"Counter1", typeof (int)},
  227. {"Counter2", typeof (int)},
  228. {"Counter3", typeof (int)},
  229. {"Counter4", typeof (int)},
  230. {"Counter5", typeof (int)},
  231. {"Counter6", typeof (int)},
  232. {"Counter7", typeof (int)},
  233. {"Counter8", typeof (int)},
  234. {"Counter9", typeof (int)},
  235. }
  236. };
  237. tests.Add(id =>
  238. {
  239. idParam.Value = id;
  240. object[] values = new object[13];
  241. using (var reader = postCommand.ExecuteReader())
  242. {
  243. reader.Read();
  244. reader.GetValues(values);
  245. table.Rows.Add(values);
  246. }
  247. }, "DataTable via IDataReader.GetValues");
  248. tests.Run(iterations);
  249. }
  250. }
  251. }
  252. static class SqlDataReaderHelper
  253. {
  254. public static string GetNullableString(this SqlDataReader reader, int index)
  255. {
  256. object tmp = reader.GetValue(index);
  257. if (tmp != DBNull.Value)
  258. {
  259. return (string)tmp;
  260. }
  261. return null;
  262. }
  263. public static Nullable<T> GetNullableValue<T>(this SqlDataReader reader, int index) where T : struct
  264. {
  265. object tmp = reader.GetValue(index);
  266. if (tmp != DBNull.Value)
  267. {
  268. return (T)tmp;
  269. }
  270. return null;
  271. }
  272. }
  273. }