Advertisement
Guest User

Preliminary entity materialization benchmarks, .NET ORMs

a guest
Oct 29th, 2013
365
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C# 16.42 KB | None | 0 0
  1. Benchmark results of fetching all SalesOrderHeader rows from an AdventureWorks catalog on a SQL Server 2005 install running in a VM. The point of this benchmark is the entity materialization, so how fast can the used Data access technology consume a resultset which is larger than a couple of rows (31K rows to be exact).
  2.  
  3. The Entity Framework v6 results seem really off. EFv5 scored around 1050ms per fetch, EFv6 scores much lower, which suggests something odd is going on. I'll look with a profiler later to see what exactly causes the delay.
  4.  
  5. Code ran in release build, on .NET 4.5 (with .NET 4.5.1 installed), compiled in VS.NET 2012. Used latest Dapper.NET. LLBLGen Pro v4.1 results run with release build of v4.1 beta.
  6.  
  7. Raw results:
  8. ===============
  9. Fetched 31465 Linq to Sql entities from the DB. Took 618ms
  10. Fetched 31465 Linq to Sql entities from the DB. Took 546ms
  11. Fetched 31465 Linq to Sql entities from the DB. Took 604ms
  12. Fetched 31465 Linq to Sql entities from the DB. Took 591ms
  13. Fetched 31465 Linq to Sql entities from the DB. Took 602ms
  14. Fetched 31465 Linq to Sql entities from the DB. Took 580ms
  15. Fetched 31465 Linq to Sql entities from the DB. Took 545ms
  16. Fetched 31465 Linq to Sql entities from the DB. Took 588ms
  17. Fetched 31465 Linq to Sql entities from the DB. Took 621ms
  18. Fetched 31465 Linq to Sql entities from the DB. Took 659ms
  19. Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 1010ms
  20. Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 808ms
  21. Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 744ms
  22. Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 784ms
  23. Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 767ms
  24. Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 751ms
  25. Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 808ms
  26. Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 774ms
  27. Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 782ms
  28. Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 818ms
  29. Fetching entities individually, LLBLGen Pro v4.1
  30. Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 738ms
  31. Fetching 1001 entities individually through CTor/PK fetch took 1734ms, or 1,73226773226773ms/entity
  32. Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 871ms
  33. Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 322ms
  34. Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 270ms
  35. Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 292ms
  36. Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 280ms
  37. Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 280ms
  38. Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 313ms
  39. Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 238ms
  40. Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 323ms
  41. Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 275ms
  42. Fetched 31465 entity framework v6.0.1 entities from the DB. Took 6240ms
  43. Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3456ms
  44. Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3530ms
  45. Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3452ms
  46. Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3495ms
  47. Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3452ms
  48. Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3615ms
  49. Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3417ms
  50. Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3437ms
  51. Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3583ms
  52. Fetching entities individually, EF6.0.1
  53. Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3572ms
  54. Fetching 1001 entities individually through CTor/PK fetch took 3430ms, or 3,42657342657343ms/entity
  55. Fetched 31465 objects from Dapper in list from the DB. Took 612ms
  56. Fetched 31465 objects from Dapper in list from the DB. Took 581ms
  57. Fetched 31465 objects from Dapper in list from the DB. Took 604ms
  58. Fetched 31465 objects from Dapper in list from the DB. Took 576ms
  59. Fetched 31465 objects from Dapper in list from the DB. Took 593ms
  60. Fetched 31465 objects from Dapper in list from the DB. Took 578ms
  61. Fetched 31465 objects from Dapper in list from the DB. Took 589ms
  62. Fetched 31465 objects from Dapper in list from the DB. Took 662ms
  63. Fetched 31465 objects from Dapper in list from the DB. Took 607ms
  64. Fetched 31465 objects from Dapper in list from the DB. Took 595ms
  65. Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 3265ms
  66. Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1615ms
  67. Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1660ms
  68. Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1506ms
  69. Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1526ms
  70. Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1528ms
  71. Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1536ms
  72. Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1659ms
  73. Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1571ms
  74. Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1623ms
  75. Fetched 31465 handcoded objects in list from the DB. Took 705ms
  76. Fetched 31465 handcoded objects in list from the DB. Took 590ms
  77. Fetched 31465 handcoded objects in list from the DB. Took 590ms
  78. Fetched 31465 handcoded objects in list from the DB. Took 603ms
  79. Fetched 31465 handcoded objects in list from the DB. Took 586ms
  80. Fetched 31465 handcoded objects in list from the DB. Took 603ms
  81. Fetched 31465 handcoded objects in list from the DB. Took 588ms
  82. Fetched 31465 handcoded objects in list from the DB. Took 593ms
  83. Fetched 31465 handcoded objects in list from the DB. Took 592ms
  84. Fetched 31465 handcoded objects in list from the DB. Took 589ms
  85. Fetched 31465 rows in datatable from the DB. Took 607ms
  86. Fetched 31465 rows in datatable from the DB. Took 596ms
  87. Fetched 31465 rows in datatable from the DB. Took 600ms
  88. Fetched 31465 rows in datatable from the DB. Took 581ms
  89. Fetched 31465 rows in datatable from the DB. Took 598ms
  90. Fetched 31465 rows in datatable from the DB. Took 606ms
  91. Fetched 31465 rows in datatable from the DB. Took 634ms
  92. Fetched 31465 rows in datatable from the DB. Took 586ms
  93. Fetched 31465 rows in datatable from the DB. Took 630ms
  94. Fetched 31465 rows in datatable from the DB. Took 614ms
  95. Press any key to continue . . .
  96.  
  97. ---------------------------------------------------------------------
  98. Code used:
  99. using System;
  100. using System.Collections;
  101. using System.Collections.Generic;
  102. using System.Data;
  103. using System.Data.SqlClient;
  104. using System.Diagnostics;
  105. using System.Linq;
  106. using System.Text;
  107. using System.Threading.Tasks;
  108. //using AdventureWorks.Dal.Adapter.DatabaseSpecific;
  109. //using AdventureWorks.Dal.Adapter.EntityClasses;
  110. //using AdventureWorks.Dal.Adapter.HelperClasses;
  111. //using AdventureWorks.Dal.Adapter.FactoryClasses;
  112.  
  113. using AdventureWorks.Dal.Adapter.v41.DatabaseSpecific;
  114. using AdventureWorks.Dal.Adapter.v41.EntityClasses;
  115. using AdventureWorks.Dal.Adapter.v41.HelperClasses;
  116. using AdventureWorks.Dal.Adapter.v41.FactoryClasses;
  117. using EF6.Bencher;
  118. using L2S.Bencher;
  119. using NH.Bencher;
  120. using NHibernate;
  121. using NHibernate.Linq;
  122. using Dapper;
  123. using SD.LLBLGen.Pro.QuerySpec;
  124. using SD.LLBLGen.Pro.QuerySpec.Adapter;
  125. using SD.LLBLGen.Pro.ORMSupportClasses;
  126.  
  127. namespace RawBencher
  128. {
  129.     class Program
  130.     {
  131.         public static string ConnectionString = @"data source=zeusVM\SQLSERVER2005;initial catalog=AdventureWorks;integrated security=SSPI;persist security info=False;packet size=4096";
  132.  
  133.         static void Main(string[] args)
  134.         {
  135.             CacheController.RegisterCache(ConnectionString, new ResultsetCache());
  136.             int loopAmount = 10;
  137.             for(int i = 0; i < loopAmount; i++)
  138.             {
  139.                 FetchSalesOrderHeaderL2S();
  140.             }
  141.             //Console.WriteLine("Attach profiler");
  142.             //Console.ReadLine();
  143.             for(int i = 0; i < loopAmount; i++)
  144.             {
  145.                 FetchSalesOrderHeaderEntities();
  146.             }
  147.             //Console.WriteLine("Done for profiler");
  148.             FetchSalesOrderHeaderEntitiesIndividually();
  149.             for(int i = 0; i < loopAmount; i++)
  150.             {
  151.                 FetchSalesOrderHeaderEntitiesWithCaching();
  152.             }
  153.             for(int i = 0; i < loopAmount; i++)
  154.             {
  155.                 FetchSalesOrderHeaderEF();
  156.             }
  157.             FetchSalesOrderHeaderEFIndividually();
  158.    
  159.             for(int i = 0; i < loopAmount; i++)
  160.             {
  161.                 FetchSalesOrderHeaderDapper();
  162.             }
  163.             for(int i = 0; i < loopAmount; i++)
  164.             {
  165.                 FetchSalesOrderHeaderNH();
  166.             }
  167.             for(int i = 0; i < loopAmount; i++)
  168.             {
  169.                 FetchSalesOrderHeaderHandCoded();
  170.             }
  171.             for(int i = 0; i < loopAmount; i++)
  172.             {
  173.                 FetchSalesOrderHeaderDataTable();
  174.             }
  175.         }
  176.  
  177.  
  178.         public static void FetchSalesOrderHeaderDataTable()
  179.         {
  180.             var sw = new Stopwatch();
  181.             sw.Start();
  182.             var headers = new DataTable();
  183.             using(var con = new SqlConnection(ConnectionString))
  184.             {
  185.                 var cmd = con.CreateCommand();
  186.                 cmd.CommandText = "select * from Sales.SalesOrderHeader";
  187.                 var adapter = new SqlDataAdapter(cmd);
  188.                 adapter.Fill(headers);
  189.             }
  190.             sw.Stop();
  191.             Console.WriteLine("Fetched {0} rows in datatable from the DB. Took {1}ms", headers.Rows.Count, sw.ElapsedMilliseconds);
  192.         }
  193.  
  194.  
  195.         public static void FetchSalesOrderHeaderHandCoded()
  196.         {
  197.             var sw = new Stopwatch();
  198.             sw.Start();
  199.             var headers = new List<SalesOrderHeader>();
  200.             using(var con = new SqlConnection(ConnectionString))
  201.             {
  202.                 var cmd = con.CreateCommand();
  203.                 cmd.CommandText = "select * from Sales.SalesOrderHeader";
  204.                 con.Open();
  205.                 var reader = cmd.ExecuteReader();
  206.                 while(reader.Read())
  207.                 {
  208.                     object fieldValue = null;
  209.                     var soh = new SalesOrderHeader();
  210.                     soh.SalesOrderId = (int)reader.GetValue(0);
  211.                     soh.RevisionNumber = (byte)reader.GetValue(1);
  212.                     soh.OrderDate = (DateTime)reader.GetValue(2);
  213.                     soh.DueDate = (DateTime)reader.GetValue(3);
  214.                     fieldValue = reader.GetValue(4);
  215.                     soh.ShipDate = (DateTime)(fieldValue == DBNull.Value ? null : fieldValue);
  216.                     soh.Status = (byte)reader.GetValue(5);
  217.                     soh.OnlineOrderFlag = (bool)reader.GetValue(6);
  218.                     soh.SalesOrderNumber = (string)reader.GetValue(7);
  219.                     fieldValue = reader.GetValue(8);
  220.                     soh.PurchaseOrderNumber = (string)(fieldValue == DBNull.Value ? null : fieldValue);
  221.                     fieldValue = reader.GetValue(9);
  222.                     soh.AccountNumber = (string)(fieldValue == DBNull.Value ? null : fieldValue);
  223.                     soh.CustomerID = (int)reader.GetValue(10);
  224.                     soh.ContactID = (int)reader.GetValue(11);
  225.                     fieldValue = reader.GetValue(12);
  226.                     soh.SalesPersonID = (int?)(fieldValue == DBNull.Value ? null : fieldValue);
  227.                     fieldValue = reader.GetValue(13);
  228.                     soh.TerritoryID = (int?)(fieldValue == DBNull.Value ? null : fieldValue);
  229.                     soh.BillToAddressID = (int)reader.GetValue(14);
  230.                     soh.ShipToAddressID = (int)reader.GetValue(15);
  231.                     soh.ShipMethodID = (int)reader.GetValue(16);
  232.                     fieldValue = reader.GetValue(17);
  233.                     soh.CreditCardID = (int?)(fieldValue == DBNull.Value ? null : fieldValue);
  234.                     fieldValue = reader.GetValue(18);
  235.                     soh.CreditCardApprovalCode = (string)(fieldValue == DBNull.Value ? null : fieldValue);
  236.                     fieldValue = reader.GetValue(19);
  237.                     soh.CurrencyRateID = (int?)(fieldValue == DBNull.Value?null : fieldValue);
  238.                     soh.SubTotal = (decimal)reader.GetValue(20);
  239.                     soh.TaxAmt = (decimal)reader.GetValue(21);
  240.                     soh.Freight = (decimal)reader.GetValue(22);
  241.                     soh.TotalDue = (decimal)reader.GetValue(23);
  242.                     fieldValue = reader.GetValue(24);
  243.                     soh.Comment = (string)(fieldValue==DBNull.Value ? null : fieldValue);
  244.                     soh.Rowguid = (Guid)reader.GetValue(25);
  245.                     soh.ModifiedDate = (DateTime)reader.GetValue(26);
  246.                     headers.Add(soh);
  247.                 }
  248.                 reader.Close();
  249.                 con.Close();
  250.             }
  251.             sw.Stop();
  252.             Console.WriteLine("Fetched {0} handcoded objects in list from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
  253.         }
  254.  
  255.  
  256.         public static void FetchSalesOrderHeaderDapper()
  257.         {
  258.             var sw = new Stopwatch();
  259.             sw.Start();
  260.             var headers = new List<SalesOrderHeader>();
  261.             using(var con = new SqlConnection(ConnectionString))
  262.             {
  263.                 con.Open();
  264.                 headers = con.Query<SalesOrderHeader>("select * from Sales.SalesOrderHeader").ToList();
  265.                 con.Close();
  266.             }
  267.             sw.Stop();
  268.             Console.WriteLine("Fetched {0} objects from Dapper in list from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
  269.         }
  270.  
  271.  
  272.         public static void FetchSalesOrderHeaderEntitiesWithCaching()
  273.         {
  274.             var sw = new Stopwatch();
  275.             sw.Start();
  276.             var qf = new QueryFactory();
  277.             var q = qf.SalesOrderHeader.CacheResultset(5);
  278.             var headers = new EntityCollection<SalesOrderHeaderEntity>();
  279.             using(var adapter = new DataAccessAdapter())
  280.             {
  281.                 adapter.FetchQuery(q, headers);
  282.             }
  283.             sw.Stop();
  284.             Console.WriteLine("Fetched {0} LLBLGen Pro v4.1 entities with resultset caching from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
  285.         }
  286.  
  287.  
  288.         private static void FetchSalesOrderHeaderEntitiesIndividually()
  289.         {
  290.             Console.WriteLine("Fetching entities individually, LLBLGen Pro v4.1");
  291.             var headers = FetchSalesOrderHeaderEntities();
  292.             int count = 0;
  293.             var sw = new Stopwatch();
  294.             sw.Start();
  295.             foreach(var fetched in headers)
  296.             {
  297.                 var toFetch = new SalesOrderHeaderEntity(fetched.SalesOrderId);
  298.                 using(var adapter = new DataAccessAdapter())
  299.                 {
  300.                     adapter.FetchEntity(toFetch);
  301.                     if(toFetch.Fields.State != SD.LLBLGen.Pro.ORMSupportClasses.EntityState.Fetched)
  302.                     {
  303.                         Console.WriteLine("Not fetched. Aborting");
  304.                         return;
  305.                     }
  306.                     count++;
  307.                 }
  308.                 if(count > 1000)
  309.                 {
  310.                     break;
  311.                 }
  312.             }
  313.             sw.Stop();
  314.             double average = (double)sw.ElapsedMilliseconds / (double)count;
  315.             Console.WriteLine("Fetching {0} entities individually through CTor/PK fetch took {1}ms, or {2}ms/entity", count, sw.ElapsedMilliseconds, average);
  316.         }
  317.        
  318.  
  319.         public static EntityCollection<SalesOrderHeaderEntity> FetchSalesOrderHeaderEntities()
  320.         {
  321.             var sw = new Stopwatch();
  322.             sw.Start();
  323.             var headers = new EntityCollection<SalesOrderHeaderEntity>();
  324.             using(var adapter = new DataAccessAdapter())
  325.             {
  326.                 adapter.FetchEntityCollection(headers, null);
  327.             }
  328.             sw.Stop();
  329.             Console.WriteLine("Fetched {0} LLBLGen Pro v4.1 entities from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
  330.             return headers;
  331.         }
  332.  
  333.  
  334.         public static List<EF6.Bencher.EntityClasses.SalesOrderHeader>  FetchSalesOrderHeaderEF()
  335.         {
  336.             var sw = new Stopwatch();
  337.             sw.Start();
  338.             List<EF6.Bencher.EntityClasses.SalesOrderHeader> headers = null;
  339.             using(var ctx = new AWDataContext())
  340.             {
  341.                 headers = ctx.SalesOrderHeaders.ToList();
  342.             }
  343.             sw.Stop();
  344.             Console.WriteLine("Fetched {0} entity framework v6.0.1 entities from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
  345.             return headers;
  346.         }
  347.  
  348.  
  349.         private static void FetchSalesOrderHeaderEFIndividually()
  350.         {
  351.             Console.WriteLine("Fetching entities individually, EF6.0.1");
  352.             var headers = FetchSalesOrderHeaderEF();
  353.             int count = 0;
  354.             var sw = new Stopwatch();
  355.             sw.Start();
  356.             foreach(var fetched in headers)
  357.             {
  358.                 using(var ctx = new AWDataContext())
  359.                 {
  360.                     var toFetch = ctx.SalesOrderHeaders.Single(soh => soh.SalesOrderId == fetched.SalesOrderId);
  361.                     count++;
  362.                 }
  363.                 if(count > 1000)
  364.                 {
  365.                     break;
  366.                 }
  367.             }
  368.             sw.Stop();
  369.             double average = (double)sw.ElapsedMilliseconds / (double)count;
  370.             Console.WriteLine("Fetching {0} entities individually through CTor/PK fetch took {1}ms, or {2}ms/entity", count, sw.ElapsedMilliseconds, average);
  371.         }
  372.  
  373.  
  374.         public static void FetchSalesOrderHeaderL2S()
  375.         {
  376.             var sw = new Stopwatch();
  377.             sw.Start();
  378.             List<L2S.Bencher.EntityClasses.SalesOrderHeader> headers = null;
  379.             var ctx = new L2SBencherDataContext();
  380.             headers = ctx.SalesOrderHeaders.ToList();
  381.             sw.Stop();
  382.             Console.WriteLine("Fetched {0} Linq to Sql entities from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
  383.         }
  384.  
  385.         public static void FetchSalesOrderHeaderNH()
  386.         {
  387.             var sw = new Stopwatch();
  388.             sw.Start();
  389.             List<NH.Bencher.EntityClasses.SalesOrderHeader> headers = null;
  390.             using(var session = SessionManager.OpenSession())
  391.             {
  392.                 headers = session.Query<NH.Bencher.EntityClasses.SalesOrderHeader>().ToList();
  393.             }
  394.             sw.Stop();
  395.             Console.WriteLine("Fetched {0} NHibernate v3.3.1.4 entities from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
  396.         }
  397.     }
  398. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement