As the principles of the benchmark have been laid, I can now move to implementations. What follows are code snippets of each test case, with comments where necessary.
ADO.NET
The ADO.NET test is straightforward:
public class PerformanceADOSelect : IPerformanceTest
{ public PerformanceADOSelect( int NumberOfItems, int NumberOfTests )
{ this._NUMBEROFITEMS = NumberOfItems;
this._NUMBEROFTESTS = NumberOfTests;
}
int _NUMBEROFITEMS;
int _NUMBEROFTESTS;
public void PerformTest( out DateTime StartedAt, out DateTime EndedAt )
{ StartedAt = DateTime.Now;
for ( int i = 0; i < _NUMBEROFTESTS; i++ )
{ StringBuilder sb = new StringBuilder();
using ( SqlConnection conn =
new SqlConnection( ConfigurationManager.ConnectionStrings["ParentChildConnectionString"].ConnectionString ) )
{ conn.Open();
using ( SqlCommand cmd =
new SqlCommand( string.Format( "SELECT TOP {0} * FROM Child", _NUMBEROFITEMS ), conn ) ) using ( SqlDataReader dr = cmd.ExecuteReader() )
{ while ( dr.Read() )
{ Child child = new Child();
child.ID = (int)dr["ID"];
child.ID_PARENT = (int)dr["ID_PARENT"];
child.ChildName = (string)dr["ChildName"];
sb.Append( child.ChildName );
}
}
}
sb = null;
}
EndedAt = DateTime.Now;
}
public string Category
{ get { return "ADO.NET"; } }
public string Name
{ get { return string.Format( "SELECT TOP {0} - {1} TIMES", _NUMBEROFITEMS, _NUMBEROFTESTS ); } }
}
public class Child
{ public int ID { get; set; } public string ChildName { get; set; } public int ID_PARENT { get; set; } }
Note that the materialization is done manually but in addition, a dummy StringBuilder accumulates some data for each read record. This string builder is not important with this ADO.NET test but in later tests the intention of it will be to simulate that the data is actually used somehow (not only read from the database).
Linq2SQL
This time also the test is straightforward. The model has been generated automatically, both the data context and entities.
public class PerformanceLinq2SqlSelect : IPerformanceTest
{ public PerformanceLinq2SqlSelect( int NumberOfItems, int NumberOfTests )
{ this._NUMBEROFITEMS = NumberOfItems;
this._NUMBEROFTESTS = NumberOfTests;
}
int _NUMBEROFITEMS;
int _NUMBEROFTESTS;
public void PerformTest( out DateTime StartedAt, out DateTime EndedAt )
{ // warmup
using ( ParentChildDataContext ctx = new ParentChildDataContext() )
{ var children = ctx.Childs.ToList();
}
StartedAt = DateTime.Now;
for ( int i = 0; i < _NUMBEROFTESTS; i++ )
{ StringBuilder sb = new StringBuilder();
using ( ParentChildDataContext ctx = new ParentChildDataContext() )
{ foreach ( var child in ctx.Childs.Take( _NUMBEROFITEMS ) )
sb.Append( child.ChildName );
}
sb = null;
}
EndedAt = DateTime.Now;
}
public string Category
{ get { return "Linq2Sql"; } }
public string Name
{ get { return string.Format( "SELECT TOP {0} - {1} TIMES", _NUMBEROFITEMS, _NUMBEROFTESTS ); } }
}
Note that this time I perform a “warmup” – this is to make sure that no internals of the ORM depending on the “first run” will influence the measurement.
EF5 Model First
This test is similar to the previous one – model is generated with the help of the designer, both object context and entity classes are available.
public class PerformanceEFModelFirstSelect : IPerformanceTest
{ public PerformanceEFModelFirstSelect( bool Tracking, int NumberOfItems, int NumberOfTests )
{ this.Tracking = Tracking;
this._NUMBEROFITEMS = NumberOfItems;
this._NUMBEROFTESTS = NumberOfTests;
}
bool Tracking;
int _NUMBEROFITEMS;
int _NUMBEROFTESTS;
public void PerformTest( out DateTime StartedAt, out DateTime EndedAt )
{ using ( ParentChildEntities ctx = new ParentChildEntities() )
{ var children = ctx.Child.ToList();
var parents = ctx.Parent.ToList();
}
StartedAt = DateTime.Now;
for ( int i = 0; i < _NUMBEROFTESTS; i++ )
{ StringBuilder sb = new StringBuilder();
using ( ParentChildEntities ctx = new ParentChildEntities() )
{ if ( !this.Tracking )
ctx.Child.MergeOption = System.Data.Objects.MergeOption.NoTracking;
foreach ( var child in ctx.Child.Take( _NUMBEROFITEMS ) )
sb.Append( child.ChildName );
}
sb = null;
}
EndedAt = DateTime.Now;
}
public string Category
{ get { return string.Format( "EF ModelFirst {0}", Tracking ? "Tracking" : "NoTracking" ); } }
public string Name
{ get { return string.Format( "SELECT TOP {0} - {1} TIMES", _NUMBEROFITEMS, _NUMBEROFTESTS ); } }
}
What is interesting is that this time I consider tracking as an important factor and I make sure that it can be turned off. Turining tracking off has some serious consequences for change tracking but since we only select the data, the only observable difference would be that turning tracking off will make the execution much faster.
I also make sure that views are pregenerated (by forcing the pregeneration with the EF Power Tools).
EF5 Code First
For Code First approach, we create entities and the dbcontext manually
public partial class Child
{ public int ID { get; set; } public int ID_PARENT { get; set; } public string ChildName { get; set; } public virtual Parent Parent { get; set; } }
public partial class Parent
{ public Parent()
{ //this.Children = new List<Child>();
}
public int ID { get; set; } public string ParentName { get; set; } public virtual ICollection<Child> Children { get; set; } }
public partial class ParentChildContext : DbContext
{ static ParentChildContext()
{ Database.SetInitializer<ParentChildContext>(null);
}
public ParentChildContext()
: base( "Name=ParentChildConnectionString" )
{ this.Configuration.AutoDetectChangesEnabled = false;
}
public DbSet<Child> Children
{ get;
set;
}
public DbSet<Parent> Parents
{ get;
set;
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{ modelBuilder.Entity<Child>()
.ToTable("Child");
// Relationships
modelBuilder.Entity<Child>()
.HasRequired( t => t.Parent )
.WithMany( t => t.Children )
.HasForeignKey( d => d.ID_PARENT );
modelBuilder.Entity<Parent>()
.ToTable( "Parent" );
}
}
The test now becomes
public class PerformanceEFCodeFirstSelect : IPerformanceTest
{ public PerformanceEFCodeFirstSelect( bool Tracking, int NumberOfItems, int NumberOfTests )
{ this.Tracking = Tracking;
this._NUMBEROFITEMS = NumberOfItems;
this._NUMBEROFTESTS = NumberOfTests;
}
bool Tracking;
int _NUMBEROFITEMS;
int _NUMBEROFTESTS;
public void PerformTest( out DateTime StartedAt, out DateTime EndedAt )
{ #region warmup
using ( ParentChildContext ctx = new ParentChildContext() )
{ var parents = ctx.Parents.ToList();
var children = ctx.Children.ToList();
}
#endregion
StartedAt = DateTime.Now;
for ( int i = 0; i < _NUMBEROFTESTS; i++ )
{ StringBuilder sb = new StringBuilder();
using ( ParentChildContext ctx = new ParentChildContext() )
{ if ( Tracking )
foreach ( var child in ctx.Children.Take( _NUMBEROFITEMS ) )
sb.Append( child.ChildName );
else
foreach ( var child in ctx.Children.AsNoTracking().Take( _NUMBEROFITEMS ) )
sb.Append( child.ChildName );
}
sb = null;
}
EndedAt = DateTime.Now;
}
public string Category
{ get { return string.Format( "EF CodeFirst {0}", Tracking ? "Tracking" : "NoTracking" ); } }
public string Name
{ get { return string.Format( "SELECT TOP {0} - {1} TIMES", _NUMBEROFITEMS, _NUMBEROFTESTS ); } }
}
Note that in this case there is no easy way of turing tracking off on the db context, instead, an extension AsNoTracking is used.
nHibernate
For nHibernate, model has to be created manually
public class Child
{ public virtual int ID { get; set; } public virtual int ID_PARENT { get; set; } public virtual string ChildName { get; set; } public virtual Parent Parent { get; set; } }
public class Parent
{ public Parent()
{ }
public virtual int ID { get; set; } public virtual string ParentName { get; set; } public virtual IList<Child> Children { get; set; } }
as well as mappings
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping
xmlns="urn:nhibernate-mapping-2.2"
assembly="Performance.NH"
namespace="Performance.NH.Model" >
<class name="Child"
optimistic-lock="none" dynamic-update="true">
<id name="ID">
<generator class="native" />
</id>
<property name="ChildName" />
<many-to-one name="Parent" column="ID_PARENT" />
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="Performance.NH" namespace="Performance.NH.Model">
<class name="Parent" table="`Parent`"
optimistic-lock="none" dynamic-update="true">
<id name="ID">
<generator class="native" />
</id>
<property name="ParentName" />
<bag name="Children">
<key column="ID_PARENT" />
<one-to-many class="Child"/>
</bag>
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
<property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
<property name="connection.connection_string_name">ParentChildConnectionString</property>
</session-factory>
</hibernate-configuration>
and the test is
public class PerformanceNHSelect : IPerformanceTest
{ public PerformanceNHSelect( bool StateLess, int NumberOfItems, int NumberOfTests )
{ this._StateLess = StateLess;
this._NUMBEROFITEMS = NumberOfItems;
this._NUMBEROFTESTS = NumberOfTests;
}
bool _StateLess;
int _NUMBEROFITEMS;
int _NUMBEROFTESTS;
public void PerformTest( out DateTime StartedAt, out DateTime EndedAt )
{ Configuration cfg = new Configuration();
cfg.Configure( typeof( PerformanceNHSelect ).Assembly,
"Performance.NH.Model.hibernate.cfg.xml" );
cfg.AddAssembly( typeof( PerformanceNHSelect ).Assembly );
var sessionFactory = cfg.BuildSessionFactory();
#region warmup
StringBuilder sbw = new StringBuilder();
using ( var session = sessionFactory.OpenSession() )
{ session.DefaultReadOnly = true;
session.FlushMode = FlushMode.Never;
foreach ( var child in session.Query<Child>().Take( _NUMBEROFITEMS ) )
sbw.Append( child.ChildName );
}
#endregion
StartedAt = DateTime.Now;
for ( int i = 0; i < _NUMBEROFTESTS; i++ )
{ StringBuilder sb = new StringBuilder();
if ( _StateLess )
{ using ( var session = sessionFactory.OpenStatelessSession() )
{ using ( var tran = session.BeginTransaction() )
{ foreach ( var child in session.Query<Child>().Take( _NUMBEROFITEMS ) )
sb.Append( child.ChildName );
}
}
}
else
{ using ( var session = sessionFactory.OpenSession() )
{ using ( var tran = session.BeginTransaction() )
{ foreach ( var child in session.Query<Child>().Take( _NUMBEROFITEMS ) )
sb.Append( child.ChildName );
}
}
}
sb = null;
}
EndedAt = DateTime.Now;
}
public string Category
{ get { return string.Format( "nHibernate {0}", _StateLess ? "StateLess" : "StateFull" ); } }
public string Name
{ get { return string.Format( "SELECT TOP {0} - {1} TIMES", _NUMBEROFITEMS, _NUMBEROFTESTS ); } }
}
What is interesting here is that I am trying to compare the statefull and stateless session with the hope that stateless sessions would somehow correspond to EF’s “no tracking” in a sense that a stateless session should fetch rows from the database faster.
In the next entry we will analyze results.
