Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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).
- 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.
- 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.
- Raw results:
- ===============
- Fetched 31465 Linq to Sql entities from the DB. Took 618ms
- Fetched 31465 Linq to Sql entities from the DB. Took 546ms
- Fetched 31465 Linq to Sql entities from the DB. Took 604ms
- Fetched 31465 Linq to Sql entities from the DB. Took 591ms
- Fetched 31465 Linq to Sql entities from the DB. Took 602ms
- Fetched 31465 Linq to Sql entities from the DB. Took 580ms
- Fetched 31465 Linq to Sql entities from the DB. Took 545ms
- Fetched 31465 Linq to Sql entities from the DB. Took 588ms
- Fetched 31465 Linq to Sql entities from the DB. Took 621ms
- Fetched 31465 Linq to Sql entities from the DB. Took 659ms
- Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 1010ms
- Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 808ms
- Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 744ms
- Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 784ms
- Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 767ms
- Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 751ms
- Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 808ms
- Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 774ms
- Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 782ms
- Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 818ms
- Fetching entities individually, LLBLGen Pro v4.1
- Fetched 31465 LLBLGen Pro v4.1 entities from the DB. Took 738ms
- Fetching 1001 entities individually through CTor/PK fetch took 1734ms, or 1,73226773226773ms/entity
- Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 871ms
- Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 322ms
- Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 270ms
- Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 292ms
- Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 280ms
- Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 280ms
- Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 313ms
- Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 238ms
- Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 323ms
- Fetched 31465 LLBLGen Pro v4.1 entities with resultset caching from the DB. Took 275ms
- Fetched 31465 entity framework v6.0.1 entities from the DB. Took 6240ms
- Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3456ms
- Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3530ms
- Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3452ms
- Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3495ms
- Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3452ms
- Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3615ms
- Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3417ms
- Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3437ms
- Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3583ms
- Fetching entities individually, EF6.0.1
- Fetched 31465 entity framework v6.0.1 entities from the DB. Took 3572ms
- Fetching 1001 entities individually through CTor/PK fetch took 3430ms, or 3,42657342657343ms/entity
- Fetched 31465 objects from Dapper in list from the DB. Took 612ms
- Fetched 31465 objects from Dapper in list from the DB. Took 581ms
- Fetched 31465 objects from Dapper in list from the DB. Took 604ms
- Fetched 31465 objects from Dapper in list from the DB. Took 576ms
- Fetched 31465 objects from Dapper in list from the DB. Took 593ms
- Fetched 31465 objects from Dapper in list from the DB. Took 578ms
- Fetched 31465 objects from Dapper in list from the DB. Took 589ms
- Fetched 31465 objects from Dapper in list from the DB. Took 662ms
- Fetched 31465 objects from Dapper in list from the DB. Took 607ms
- Fetched 31465 objects from Dapper in list from the DB. Took 595ms
- Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 3265ms
- Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1615ms
- Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1660ms
- Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1506ms
- Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1526ms
- Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1528ms
- Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1536ms
- Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1659ms
- Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1571ms
- Fetched 31465 NHibernate v3.3.1.4 entities from the DB. Took 1623ms
- Fetched 31465 handcoded objects in list from the DB. Took 705ms
- Fetched 31465 handcoded objects in list from the DB. Took 590ms
- Fetched 31465 handcoded objects in list from the DB. Took 590ms
- Fetched 31465 handcoded objects in list from the DB. Took 603ms
- Fetched 31465 handcoded objects in list from the DB. Took 586ms
- Fetched 31465 handcoded objects in list from the DB. Took 603ms
- Fetched 31465 handcoded objects in list from the DB. Took 588ms
- Fetched 31465 handcoded objects in list from the DB. Took 593ms
- Fetched 31465 handcoded objects in list from the DB. Took 592ms
- Fetched 31465 handcoded objects in list from the DB. Took 589ms
- Fetched 31465 rows in datatable from the DB. Took 607ms
- Fetched 31465 rows in datatable from the DB. Took 596ms
- Fetched 31465 rows in datatable from the DB. Took 600ms
- Fetched 31465 rows in datatable from the DB. Took 581ms
- Fetched 31465 rows in datatable from the DB. Took 598ms
- Fetched 31465 rows in datatable from the DB. Took 606ms
- Fetched 31465 rows in datatable from the DB. Took 634ms
- Fetched 31465 rows in datatable from the DB. Took 586ms
- Fetched 31465 rows in datatable from the DB. Took 630ms
- Fetched 31465 rows in datatable from the DB. Took 614ms
- Press any key to continue . . .
- ---------------------------------------------------------------------
- Code used:
- using System;
- using System.Collections;
- using System.Collections.Generic;
- using System.Data;
- using System.Data.SqlClient;
- using System.Diagnostics;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
- //using AdventureWorks.Dal.Adapter.DatabaseSpecific;
- //using AdventureWorks.Dal.Adapter.EntityClasses;
- //using AdventureWorks.Dal.Adapter.HelperClasses;
- //using AdventureWorks.Dal.Adapter.FactoryClasses;
- using AdventureWorks.Dal.Adapter.v41.DatabaseSpecific;
- using AdventureWorks.Dal.Adapter.v41.EntityClasses;
- using AdventureWorks.Dal.Adapter.v41.HelperClasses;
- using AdventureWorks.Dal.Adapter.v41.FactoryClasses;
- using EF6.Bencher;
- using L2S.Bencher;
- using NH.Bencher;
- using NHibernate;
- using NHibernate.Linq;
- using Dapper;
- using SD.LLBLGen.Pro.QuerySpec;
- using SD.LLBLGen.Pro.QuerySpec.Adapter;
- using SD.LLBLGen.Pro.ORMSupportClasses;
- namespace RawBencher
- {
- class Program
- {
- public static string ConnectionString = @"data source=zeusVM\SQLSERVER2005;initial catalog=AdventureWorks;integrated security=SSPI;persist security info=False;packet size=4096";
- static void Main(string[] args)
- {
- CacheController.RegisterCache(ConnectionString, new ResultsetCache());
- int loopAmount = 10;
- for(int i = 0; i < loopAmount; i++)
- {
- FetchSalesOrderHeaderL2S();
- }
- //Console.WriteLine("Attach profiler");
- //Console.ReadLine();
- for(int i = 0; i < loopAmount; i++)
- {
- FetchSalesOrderHeaderEntities();
- }
- //Console.WriteLine("Done for profiler");
- FetchSalesOrderHeaderEntitiesIndividually();
- for(int i = 0; i < loopAmount; i++)
- {
- FetchSalesOrderHeaderEntitiesWithCaching();
- }
- for(int i = 0; i < loopAmount; i++)
- {
- FetchSalesOrderHeaderEF();
- }
- FetchSalesOrderHeaderEFIndividually();
- for(int i = 0; i < loopAmount; i++)
- {
- FetchSalesOrderHeaderDapper();
- }
- for(int i = 0; i < loopAmount; i++)
- {
- FetchSalesOrderHeaderNH();
- }
- for(int i = 0; i < loopAmount; i++)
- {
- FetchSalesOrderHeaderHandCoded();
- }
- for(int i = 0; i < loopAmount; i++)
- {
- FetchSalesOrderHeaderDataTable();
- }
- }
- public static void FetchSalesOrderHeaderDataTable()
- {
- var sw = new Stopwatch();
- sw.Start();
- var headers = new DataTable();
- using(var con = new SqlConnection(ConnectionString))
- {
- var cmd = con.CreateCommand();
- cmd.CommandText = "select * from Sales.SalesOrderHeader";
- var adapter = new SqlDataAdapter(cmd);
- adapter.Fill(headers);
- }
- sw.Stop();
- Console.WriteLine("Fetched {0} rows in datatable from the DB. Took {1}ms", headers.Rows.Count, sw.ElapsedMilliseconds);
- }
- public static void FetchSalesOrderHeaderHandCoded()
- {
- var sw = new Stopwatch();
- sw.Start();
- var headers = new List<SalesOrderHeader>();
- using(var con = new SqlConnection(ConnectionString))
- {
- var cmd = con.CreateCommand();
- cmd.CommandText = "select * from Sales.SalesOrderHeader";
- con.Open();
- var reader = cmd.ExecuteReader();
- while(reader.Read())
- {
- object fieldValue = null;
- var soh = new SalesOrderHeader();
- soh.SalesOrderId = (int)reader.GetValue(0);
- soh.RevisionNumber = (byte)reader.GetValue(1);
- soh.OrderDate = (DateTime)reader.GetValue(2);
- soh.DueDate = (DateTime)reader.GetValue(3);
- fieldValue = reader.GetValue(4);
- soh.ShipDate = (DateTime)(fieldValue == DBNull.Value ? null : fieldValue);
- soh.Status = (byte)reader.GetValue(5);
- soh.OnlineOrderFlag = (bool)reader.GetValue(6);
- soh.SalesOrderNumber = (string)reader.GetValue(7);
- fieldValue = reader.GetValue(8);
- soh.PurchaseOrderNumber = (string)(fieldValue == DBNull.Value ? null : fieldValue);
- fieldValue = reader.GetValue(9);
- soh.AccountNumber = (string)(fieldValue == DBNull.Value ? null : fieldValue);
- soh.CustomerID = (int)reader.GetValue(10);
- soh.ContactID = (int)reader.GetValue(11);
- fieldValue = reader.GetValue(12);
- soh.SalesPersonID = (int?)(fieldValue == DBNull.Value ? null : fieldValue);
- fieldValue = reader.GetValue(13);
- soh.TerritoryID = (int?)(fieldValue == DBNull.Value ? null : fieldValue);
- soh.BillToAddressID = (int)reader.GetValue(14);
- soh.ShipToAddressID = (int)reader.GetValue(15);
- soh.ShipMethodID = (int)reader.GetValue(16);
- fieldValue = reader.GetValue(17);
- soh.CreditCardID = (int?)(fieldValue == DBNull.Value ? null : fieldValue);
- fieldValue = reader.GetValue(18);
- soh.CreditCardApprovalCode = (string)(fieldValue == DBNull.Value ? null : fieldValue);
- fieldValue = reader.GetValue(19);
- soh.CurrencyRateID = (int?)(fieldValue == DBNull.Value?null : fieldValue);
- soh.SubTotal = (decimal)reader.GetValue(20);
- soh.TaxAmt = (decimal)reader.GetValue(21);
- soh.Freight = (decimal)reader.GetValue(22);
- soh.TotalDue = (decimal)reader.GetValue(23);
- fieldValue = reader.GetValue(24);
- soh.Comment = (string)(fieldValue==DBNull.Value ? null : fieldValue);
- soh.Rowguid = (Guid)reader.GetValue(25);
- soh.ModifiedDate = (DateTime)reader.GetValue(26);
- headers.Add(soh);
- }
- reader.Close();
- con.Close();
- }
- sw.Stop();
- Console.WriteLine("Fetched {0} handcoded objects in list from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
- }
- public static void FetchSalesOrderHeaderDapper()
- {
- var sw = new Stopwatch();
- sw.Start();
- var headers = new List<SalesOrderHeader>();
- using(var con = new SqlConnection(ConnectionString))
- {
- con.Open();
- headers = con.Query<SalesOrderHeader>("select * from Sales.SalesOrderHeader").ToList();
- con.Close();
- }
- sw.Stop();
- Console.WriteLine("Fetched {0} objects from Dapper in list from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
- }
- public static void FetchSalesOrderHeaderEntitiesWithCaching()
- {
- var sw = new Stopwatch();
- sw.Start();
- var qf = new QueryFactory();
- var q = qf.SalesOrderHeader.CacheResultset(5);
- var headers = new EntityCollection<SalesOrderHeaderEntity>();
- using(var adapter = new DataAccessAdapter())
- {
- adapter.FetchQuery(q, headers);
- }
- sw.Stop();
- Console.WriteLine("Fetched {0} LLBLGen Pro v4.1 entities with resultset caching from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
- }
- private static void FetchSalesOrderHeaderEntitiesIndividually()
- {
- Console.WriteLine("Fetching entities individually, LLBLGen Pro v4.1");
- var headers = FetchSalesOrderHeaderEntities();
- int count = 0;
- var sw = new Stopwatch();
- sw.Start();
- foreach(var fetched in headers)
- {
- var toFetch = new SalesOrderHeaderEntity(fetched.SalesOrderId);
- using(var adapter = new DataAccessAdapter())
- {
- adapter.FetchEntity(toFetch);
- if(toFetch.Fields.State != SD.LLBLGen.Pro.ORMSupportClasses.EntityState.Fetched)
- {
- Console.WriteLine("Not fetched. Aborting");
- return;
- }
- count++;
- }
- if(count > 1000)
- {
- break;
- }
- }
- sw.Stop();
- double average = (double)sw.ElapsedMilliseconds / (double)count;
- Console.WriteLine("Fetching {0} entities individually through CTor/PK fetch took {1}ms, or {2}ms/entity", count, sw.ElapsedMilliseconds, average);
- }
- public static EntityCollection<SalesOrderHeaderEntity> FetchSalesOrderHeaderEntities()
- {
- var sw = new Stopwatch();
- sw.Start();
- var headers = new EntityCollection<SalesOrderHeaderEntity>();
- using(var adapter = new DataAccessAdapter())
- {
- adapter.FetchEntityCollection(headers, null);
- }
- sw.Stop();
- Console.WriteLine("Fetched {0} LLBLGen Pro v4.1 entities from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
- return headers;
- }
- public static List<EF6.Bencher.EntityClasses.SalesOrderHeader> FetchSalesOrderHeaderEF()
- {
- var sw = new Stopwatch();
- sw.Start();
- List<EF6.Bencher.EntityClasses.SalesOrderHeader> headers = null;
- using(var ctx = new AWDataContext())
- {
- headers = ctx.SalesOrderHeaders.ToList();
- }
- sw.Stop();
- Console.WriteLine("Fetched {0} entity framework v6.0.1 entities from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
- return headers;
- }
- private static void FetchSalesOrderHeaderEFIndividually()
- {
- Console.WriteLine("Fetching entities individually, EF6.0.1");
- var headers = FetchSalesOrderHeaderEF();
- int count = 0;
- var sw = new Stopwatch();
- sw.Start();
- foreach(var fetched in headers)
- {
- using(var ctx = new AWDataContext())
- {
- var toFetch = ctx.SalesOrderHeaders.Single(soh => soh.SalesOrderId == fetched.SalesOrderId);
- count++;
- }
- if(count > 1000)
- {
- break;
- }
- }
- sw.Stop();
- double average = (double)sw.ElapsedMilliseconds / (double)count;
- Console.WriteLine("Fetching {0} entities individually through CTor/PK fetch took {1}ms, or {2}ms/entity", count, sw.ElapsedMilliseconds, average);
- }
- public static void FetchSalesOrderHeaderL2S()
- {
- var sw = new Stopwatch();
- sw.Start();
- List<L2S.Bencher.EntityClasses.SalesOrderHeader> headers = null;
- var ctx = new L2SBencherDataContext();
- headers = ctx.SalesOrderHeaders.ToList();
- sw.Stop();
- Console.WriteLine("Fetched {0} Linq to Sql entities from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
- }
- public static void FetchSalesOrderHeaderNH()
- {
- var sw = new Stopwatch();
- sw.Start();
- List<NH.Bencher.EntityClasses.SalesOrderHeader> headers = null;
- using(var session = SessionManager.OpenSession())
- {
- headers = session.Query<NH.Bencher.EntityClasses.SalesOrderHeader>().ToList();
- }
- sw.Stop();
- Console.WriteLine("Fetched {0} NHibernate v3.3.1.4 entities from the DB. Took {1}ms", headers.Count, sw.ElapsedMilliseconds);
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement