NHibernate 3.0: Testing Using NHibernate Profiler and SQLite

Exclusive offer: get 50% off this eBook here
NHibernate 3.0 Cookbook

NHibernate 3.0 Cookbook — Save 50%

Get solutions to common NHibernate problems to develop high-quality performance-critical data access applications

$26.99    $13.50
by Jason Dentler | October 2010 | .NET Cookbooks Open Source

This article by Jason Dentler, author of NHibernate 3.0 Cookbook, introduces some techniques you can apply to quickly test your NHibernate applications and includes an introduction to NHibernate Profiler. Testing is a critical step in the development of any application. The recipes in this article are designed to ease the testing process and expose common issues.

In this article we will cover the following topics:

  • Using NHibernate Profiler
  • Testing with the SQLite in-memory database
  • Preloading data with SQLite

 

NHibernate 3.0 Cookbook

NHibernate 3.0 Cookbook

Get solutions to common NHibernate problems to develop high-quality performance-critical data access applications

  • Master the full range of NHibernate features
  • Reduce hours of application development time and get better application architecture and performance
  • Create, maintain, and update your database structure automatically with the help of NHibernate
  • Written and tested for NHibernate 3.0 with input from the development team distilled in to easily accessible concepts and examples
  • Part of Packt's Cookbook series: each recipe is a carefully organized sequence of instructions to complete the task as efficiently as possible

Read more about this book

(For more resources on NHibernate, see here.)

Using NHibernate Profiler

NHibernate Profiler from Hibernating Rhinos is the number one tool for analyzing and visualizing what is happening inside your NHibernate application, and for discovering issues you may have. In this recipe, I'll show you how to get up and running with NHibernate Profiler.

Getting ready

Download NHibernate Profiler from http://nhprof.com, and unzip it. As it is a commercial product, you will also need a license file. You may request a 30-day trial license from the NHProf website.

Using our Eg.Core model, set up a new NHibernate console application with log4net. (Download code).

How to do it...

  1. Add a reference to HibernatingRhinos.Profiler.Appender.dll from the NH Profiler download.
  2. In the session-factory element of App.config, set the property generate_statistics to true.
  3. Add the following code to your Main method:

    log4net.Config.XmlConfigurator.Configure();

    HibernatingRhinos.Profiler.Appender.
    NHibernate.NHibernateProfiler.Initialize();

    var nhConfig = new Configuration().Configure();

    var sessionFactory = nhConfig.BuildSessionFactory();

    using (var session = sessionFactory.OpenSession())
    {
    var books = from b in session.Query<Book>()
    where b.Author == "Jason Dentler"
    select b;

    foreach (var book in books)
    Console.WriteLine(book.Name);
    }

  4. Run NHProf.exe from the NH Profiler download, and activate the license.
  5. Build and run your console application.
  6. Check the NH Profiler. It should look like the next screenshot. Notice the gray dots indicating alerts next to the Session #1 and Recent Statements.

  7. Select Session #1 from the Sessions list at the top left pane.
  8. Select the statement from the top right pane.
  9. Notice the SQL statement in the following screenshot:

  10. Click on See the 1 row(s) resulting from this statement.
  11. Enter your database connection string in the field provided, and click on OK.
  12. Close the query results window.
  13. Switch to the Alerts tab, and notice the alert: Use of implicit transaction is discouraged.
  14. Click on the Read more link for more information and suggested solutions to this particular issue.
  15. Switch to the Stack Trace tab, as shown in the next screenshot:

  16. Double-click on the NHProfTest.NHProfTest.Program.Main stack frame to jump to that location inside Visual Studio.
  17. Using the following code, wrap the foreach loop in a transaction and commit the transaction:

    using (var tx = session.BeginTransaction())
    {
    foreach (var book in books)
    Console.WriteLine(book.Name);
    tx.Commit();
    }

  18. In NH Profiler, right-click on Sessions on the top left pane, and select Clear All Sessions.
  19. Build and run your application.
  20. Check NH Profiler for alerts.

How it works...

NHibernate Profiler uses a custom log4net appender to capture data about NHibernate activities inside your application and transmit that data to the NH Profiler application.

Setting generate_statistics allows NHibernate to capture many key data points. These statistics are displayed in the lower, left-hand side of the pane of NHibernate Profiler.

We initialize NHibernate Profiler with a call to NHibernateProfiler.Initialize(). For best results, do this when your application begins, just after you have configured log4net.

There's more...

NHibernate Profiler also supports offline and remote profiling, as well as command-line options for use with build scripts and continuous integration systems.

In addition to NHibernate warnings and errors, NH Profiler alerts us to 12 common misuses of NHibernate, which are as follows:

  • Transaction disposed without explicit rollback or commit: If no action is taken, transactions will rollback when disposed. However, this often indicates a missing commit rather than a desire to rollback the transaction
  • Using a single session on multiple threads is likely a bug: A Session should only be used by one thread at a time. Sharing a session across threads is usually a bug, not an explicit design choice with proper locking.
  • Use of implicit transaction is discouraged: Nearly all session activity should happen inside an NHibernate transaction.
  • Excessive number of rows: In nearly all cases, this indicates a poorly designed query or bug.
  • Large number of individual writes: This indicates a failure to batch writes, either because adonet.batch_size is not set, or possibly because an Identity-type POID generator is used, which effectively disables batching.
  • Select N+1: This alert indicates a particular type of anti-pattern where, typically, we load and enumerate a list of parent objects, lazy-loading their children as we move through the list. Instead, we should eagerly fetch those children before enumerating the list
  • Superfluous updates, use inverse="true": NH Profiler detected an unnecessary update statement from a bi-directional one-to-many relationship. Use inverse="true" on the many side (list, bag, set, and others) of the relationship to avoid this.
  • Too many cache calls per session: This alert is targeted particularly at applications using a distributed (remote) second-level cache. By design, NHibernate does not batch calls to the cache, which can easily lead to hundreds of slow remote calls. It can also indicate an over reliance on the second-level cache, whether remote or local.
  • Too many database calls per session: This usually indicates a misuse of the database, such as querying inside a loop, a select N+1 bug, or an excessive number of writes.
  • Too many joins: A query contains a large number of joins. When executed in a batch, multiple simple queries with only a few joins often perform better than a complex query with many joins. This alert can also indicate unexpected Cartesian products.
  • Unbounded result set: NH Profiler detected a query without a row limit. When the application is moved to production, these queries may return huge result sets, leading to catastrophic performance issues. As insurance against these issues, set a reasonable maximum on the rows returned by each query
  • Different parameter sizes result in inefficient query plan cache usage: NH Profiler detected two identical queries with different parameter sizes. Each of these queries will create a query plan. This problem grows exponentially with the size and number of parameters used. Setting prepare_sql to true allows NHibernate to generate queries with consistent parameter sizes.

See also

  • Configuring NHibernate with App.config
  • Configuring log4net logging
NHibernate 3.0 Cookbook Get solutions to common NHibernate problems to develop high-quality performance-critical data access applications
Published: October 2010
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

Read more about this book

(For more resources on NHibernate, see here.)

Fast testing with SQLite in-memory database

Running a full range of tests for a large NHibernate application can take some time. In this recipe, I will show you how to use SQLite's in-memory database to speed up this process.

This is not meant to replace running integration tests against the real RDBMS before moving to production. Rather, it is a smoke test to provide feedback to developers quickly before running the slower integration tests.

Getting ready

  1. Download and install NUnit from http://nunit.org
  2. Download and install SQLite from http://sqlite.phxsoftware.com

Note: This recipe will work with other test frameworks such as MSTest, MbUnit, and xUnit. Just replace the NUnit-specific attributes with those for your preferred framework.

How to do it...

  1. Create a new, empty class library project.
  2. Add references to our Eg.Core model, as well as nunit.framework, System.Data.Sqlite, log4net, NHibernate and NHibernate.ByteCode.Castle

    System.Data.Sqlite has 32-bit and 64-bit versions. Use the appropriate file for your operating system and target platform.

  3. Add an application configuration file with NHibernate and log4net configuration sections.
  4. Change the log4net configuration to use a ConsoleAppender.
  5. Add a new, static class named NHConfigurator with the following code:

    private const string CONN_STR =
    "Data Source=:memory:;Version=3;New=True;";
    private static readonly Configuration _configuration
    private static readonly ISessionFactory _sessionFact

    static NHConfigurator()
    {

    _configuration = new Configuration().Configure()
    .DataBaseIntegration(db =>
    {
    db.Dialect<SQLiteDialect>();
    db.Driver<SQLite20Driver>();
    db.ConnectionProvider<TestConnectionProvider>(
    db.ConnectionString = CONN_STR;
    })
    .SetProperty(Environment.CurrentSessionContextCl
    "thread_static");


    var props = _configuration.Properties;
    if (props.ContainsKey(Environment.ConnectionString
    props.Remove(Environment.ConnectionStringName);
    _sessionFactory = _configuration.BuildSessionFacto
    }

    public static Configuration Configuration
    {
    get
    {
    return _configuration;
    }
    }
    public static ISessionFactory SessionFactory
    {
    get
    {
    return _sessionFactory;
    }
    }

  6. Add a new, abstract class named BaseFixture using the following code:

    protected static ILog log = new Func<ILog>(() =>
    {
    log4net.Config.XmlConfigurator.Configure();
    return LogManager.GetLogger(typeof(BaseFixture));
    }).Invoke();

    protected virtual void OnFixtureSetup() { }
    protected virtual void OnFixtureTeardown() { }
    protected virtual void OnSetup() { }
    protected virtual void OnTeardown() { }
    [TestFixtureSetUp]
    public void FixtureSetup()
    {
    OnFixtureSetup();
    }
    [TestFixtureTearDown]
    public void FixtureTeardown()
    {
    OnFixtureTeardown();
    }
    [SetUp]
    public void Setup()
    {
    OnSetup();
    }
    [TearDown]
    public void Teardown()
    {
    OnTeardown();
    }

  7. Add a new, abstract class named NHibernateFixture, inherited from BaseFixture, with the following code:

    protected ISessionFactory SessionFactory
    {
    get
    {
    return NHConfigurator.SessionFactory;
    }
    }
    protected ISession Session
    {
    get
    {
    return SessionFactory.GetCurrentSession();
    }
    }
    protected override void OnSetup()
    {
    SetupNHibernateSession();
    base.OnSetup();
    }
    protected override void OnTeardown()
    {
    TearDownNHibernateSession();
    base.OnTeardown();
    }
    protected void SetupNHibernateSession()
    {
    TestConnectionProvider.CloseDatabase();
    SetupContextualSession();
    BuildSchema();
    }
    protected void TearDownNHibernateSession()
    {
    TearDownContextualSession();
    TestConnectionProvider.CloseDatabase();
    }
    private void SetupContextualSession()



    {
    var session = SessionFactory.OpenSession();
    CurrentSessionContext.Bind(session);
    }
    private void TearDownContextualSession()
    {
    var sessionFactory = NHConfigurator.SessionFactory;
    var session = CurrentSessionContext.Unbind(sessionFactory);
    session.Close();
    }
    private void BuildSchema()
    {
    var cfg = NHConfigurator.Configuration;
    var schemaExport = new SchemaExport(cfg);
    schemaExport.Create(false, true);
    }

  8. Add a new class named PersistenceTests, inherited from NHibernateFixture.
  9. Decorate the PersistenceTests class with NUnit's TestFixture attribute.
  10. Add the following test method to PersistenceTests:

    [Test]
    public void Movie_cascades_save_to_ActorRole()
    {
    Guid movieId;
    Movie movie = new Movie()
    {
    Name = "Mars Attacks",
    Description = "Sci-Fi Parody",
    Director = "Tim Burton",
    UnitPrice = 12M,
    Actors = new List<ActorRole>()
    {
    new ActorRole() {
    Actor = "Jack Nicholson",
    Role = "President James Dale"
    }
    }
    };
    using (var session = SessionFactory.OpenSession())

    using (var tx = session.BeginTransaction())
    {
    movieId = (Guid)session.Save(movie);
    tx.Commit();
    }
    using (var session = SessionFactory.OpenSession())
    using (var tx = session.BeginTransaction())
    {
    movie = session.Get<Movie>(movieId);
    tx.Commit();
    }
    Assert.That(movie.Actors.Count == 1);
    }

  11. Build the project.
  12. Start NUnit.
  13. Select File | Open Project.
  14. Select the project's compiled assembly from the bin\Debug folder.
  15. Click on Run.

How it works...

NHConfigurator loads an NHibernate configuration from the App.config, then overwrites the dialect, driver, connection provider, and connection string properties to use SQLite instead. It also uses the thread static session context to provide sessions to code that may rely on NHibernate contextual sessions. Finally, we remove the connection.connection_string_name property, as we have provided a connection string value

The magic of SQLite happens in our custom TestConnectionProvider class. Typically, a connection provider will return a new connection from each call to GetConnection(), and close the connection when CloseConnection() is called. However, each SQLite in-memory database only supports a single connection. That is, each new connection creates and connects to its own in-memory database. When the connection is closed, the database is lost. When each test begins, we close any lingering connections. This ensures we will get a fresh, empty database. When NHibernate first calls GetConnection(), we open a new connection. We return this same connection for each subsequent call. We ignore any calls to CloseConnection(). Finally, when the test is completed, we dispose the database connection, effectively disposing the in-memory database with it.

This provides a perfectly clean database for each test, ensuring that remnants of a previous test cannot contaminate the current test, possibly altering the results.

In BaseFixture, we configure log4net and set up some virtual methods that can be overridden in inherited classes.

In NHibernateFixture, we override OnSetup, which runs just before each test. For code that may use contextual sessions, we open a session and bind it to the context. We also create our database tables with NHibernate's schema export. This, of course, opens a database connection, establishing our in-memory database.

We override OnTeardown, which runs after each test, to unbind the session from the session context, close the session, and finally close the database connection. When the connection is closed, the database is erased from memory.

The test uses the session from the NHibernateFixture to save a movie with an associated ActorRole. We use two separate sessions to save, and then fetch the movie to ensure that when we fetch the movie, we load it from the database rather than just returning the instance from the first level cache. This gives us a true tests of what we have persisted in the database. Once we've fetched the movie back from the database, we make sure it still has an ActorRole. This test ensures that when we save a movie, the save cascades down to ActorRoles in the Actors list as well.

There's more...

While SQLite in-memory databases are fast, the SQLite engine has several limitations. For example, foreign key constraints are not enforced. Its speed makes it great for providing quick test feedback, but because of the limitations, before deploying the application, it is best to run all tests against the production database engine. There are a few approaches to testing with a real RDBMS, each with significant issues, which are as follows

  • Drop and recreate the database between each test. This is extremely slow for enterprise-level databases. A full set of integration tests may take hours to run, but this is the least intrusive option.
  • Roll back every transaction to prevent changes to the database. This is very limiting. For instance, even our simple Persistence test would require some significant changes to work in this way. This may require you to change business logic to suit a testing limitation.
  • Clean up on a test-by-test basis. For instance, for every insert, perform a delete. This is a manual, labor-intensive, error-prone process.

See also

  • Preloading data with SQLite
  • Using the Fluent NHibernate Persistence tester
  • Using the Ghostbusters test
NHibernate 3.0 Cookbook Get solutions to common NHibernate problems to develop high-quality performance-critical data access applications
Published: October 2010
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

Read more about this book

(For more resources on NHibernate, see here.)

Preloading data with SQLite

It is often desirable to preload the database with test data before running tests. This recipe will show you how to quickly load the in-memory database with data from a SQLite file database.

Getting ready

Complete the previous recipe, Fast testing with SQLite in-memory database.

Create a SQLite file database with identical schema, containing test data. This can be accomplished in a number of ways. Perhaps the easiest is to export an in-memory database using SQLiteLoader.ExportData from this recipe.

How to do it...

  1. Add a new class named SQLiteLoader using the following code:

    private static ILog log = LogManager.GetLogger(typeof(SQLiteLoad
    er));
    private const string ATTACHED_DB = "asdfgaqwernb";
    public void ImportData(
    SQLiteConnection conn,
    string sourceDataFile)
    {

    var tables = GetTableNames(conn);
    AttachDatabase(conn, sourceDataFile);
    foreach (var table in tables)
    {
    var sourceTable = string.Format("{0}.{1}",
    ATTACHED_DB, table);
    CopyTableData(conn, sourceTable, table);
    }
    DetachDatabase(conn);
    }
    public void ExportData(
    SQLiteConnection conn,
    string destinationDataFile)
    {
    var tables = GetTableNames(conn);
    AttachDatabase(conn, destinationDataFile);
    foreach (var table in tables)
    {
    var destTable = string.Format("{0}.{1}",
    ATTACHED_DB, table);
    CopyTableData(conn, table, destTable);
    }
    DetachDatabase(conn);
    }
    private IEnumerable<string> GetTableNames(
    SQLiteConnection conn)
    {
    string tables = SQLiteMetaDataCollectionNames.Tables;
    DataTable dt = conn.GetSchema(tables);
    return from DataRow R in dt.Rows
    select (string)R["TABLE_NAME"];
    }
    private void AttachDatabase(
    SQLiteConnection conn,
    string sourceDataFile)
    {
    SQLiteCommand cmd = new SQLiteCommand(conn);
    cmd.CommandText = String.Format("ATTACH '{0}' AS {1}",
    sourceDataFile, ATTACHED_DB);
    log.Debug(cmd.CommandText);
    cmd.ExecuteNonQuery();
    }
    private void CopyTableData(
    SQLiteConnection conn,
    string source,
    string destination)
    {
    SQLiteCommand cmd = new SQLiteCommand(conn);
    cmd.CommandText = string.Format(
    "INSERT INTO {0} SELECT * FROM {1}",
    destination, source);
    log.Debug(cmd.CommandText);
    cmd.ExecuteNonQuery();
    }

    private void DetachDatabase(SQLiteConnection conn)
    {
    SQLiteCommand cmd = new SQLiteCommand(conn);
    cmd.CommandText = string.Format(«DETACH {0}», ATTACHED_DB);
    log.Debug(cmd.CommandText);
    cmd.ExecuteNonQuery();
    }

  2. Add a new abstract class named DataDependentFixture, inherited from NHibernateFixture, using the following code:

    protected abstract string GetSQLiteFilename();
    protected override void OnSetup()
    {
    base.OnSetup();
    var conn = (SQLiteConnection) Session.Connection;
    new SQLiteLoader().ImportData(conn, GetSQLiteFilename());
    }

  3. Add a new class named QueryTests, inherited from DataDependentFixture.
  4. In QueryTests, override GetSQLiteFilename() to return the path to your SQLite file.
  5. Add the following test to QueryTests:

    [Test]
    public void Director_query_should_return_one_movie()
    {
    var query = Session.QueryOver<Movie>()
    .Where(m => m.Director == "Tim Burton");
    using (var tx = Session.BeginTransaction())
    {
    var movies = query.List<Movie>();
    Assert.That(movies.Count == 1);
    tx.Commit();
    }
    }

  6. Decorate QueryTests with NUnit's TestFixture attribute.
  7. Build the project.
  8. Run the NUnit tests.

How it works...

In the QueryTests fixture, GetSQLiteFilename() returns the path of the SQLite file containing our test data. DataDependentFixture passes this file path and our connection to the SQLite in-memory database over to SQLiteLoader.ImportData().

We call SQLiteConnection.GetSchema() to create a list of table names in the database.

Next, we attach the file database to the in-memory database using the command ATTACH 'filePath' AS schemaName where filePath is the path to the file database and schemaName is a string constant. This allows us to reference the tables in the file database from the memory database. For example, if our file database has a table named tblTestData, and we use the string asdf for schemaName, we can execute SELECT * FROM asdf.tblTestData

We loop through each table, executing the statement INSERT INTO tableName SELECT * FROM schemaName.tableName. This command quickly copies all the data from a table in the file database to an identical table in the memory database. Because SQLite doesn't enforce foreign key constraints, we do not need to be concerned with the order we use to copy this data

Finally, we detach the file database using the command DETACH schemaName.

There's more...

We can use SQLiteLoader.ExportData to move data from the SQLite in-memory database to a file database. Also, each test fixture can use test data from a different file database.

See also

  • Fast testing with SQLite in-memory
  • Using the Ghostbusters test

Summary

In this article we covered:

  • Using NHibernate Profiler
  • Testing with the SQLite in-memory database
  • Preloading data with SQLite

In the next article, we will cover the following topics:


Further resources on this subject:


About the Author :


Jason Dentler

Jason Dentler grew up in the small Texas town of Mission Valley. He started tinkering with computers as a kid in the late 1980s, and all these years later, he hasn't stopped. He's worked in a few different industries. Currently, he builds really awesome software in higher education. He's an Eagle Scout and a graduate of the University of Houston – Victoria.

Books From Packt


NHibernate 2 Beginner's Guide
NHibernate 2 Beginner's Guide

WCF 4.0 Multi-tier Services Development with LINQ to Entities
WCF 4.0 Multi-tier Services Development with LINQ to Entities

ASP.NET 3.5 Application Architecture and Design
ASP.NET 3.5 Application Architecture and Design

Microsoft Windows Communication Foundation 4.0 Cookbook for Developing SOA Applications
Microsoft Windows Communication Foundation 4.0 Cookbook for Developing SOA Applications

.NET Compact Framework 3.5 Data Driven Applications
.NET Compact Framework 3.5 Data Driven Applications

Microsoft Azure: Enterprise Application Development
Microsoft Azure: Enterprise Application Development

Applied Architecture Patterns on the Microsoft Platform
Applied Architecture Patterns on the Microsoft Platform

Microsoft Windows Workflow Foundation 4.0 Cookbook
Microsoft Windows Workflow Foundation 4.0 Cookbook


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software