News, examples, tips, ideas and plans.
Thoughts around ORM, .NET and SQL databases.

Sunday, November 17, 2013

DataObjects.Net 5.0 Beta 1

This is a short announce about the upcoming major version of DataObjects.Net.

Changes:

Added

  • PostSharp is replaced with custom persistence weaver based on Mono.Cecil
  • Support for String.Like extension method with support in LINQ translator
  • Support for DateTime.ToString("s") in LINQ
  • Support for ignoring particular tables and/or columns during Domain.Build
  • Support for defining recycled fields via external definitions
  • ReSharper annotations for public API

Changed

  • Xtensive.Tuples performance is significantly improved
  • Validation framework is reworked
  • Persist operation is optimized to avoid sorting entities that do not have incoming/outgoing FK
  • IEnumerable.Remove() extension method is marked as obsolete

Removed

  • Support for .NET 3.5 and Visual Studio 2008
  • Object-to-object mapper
  • Xtensive.Core and Xtensive.Aspects assemblies. Now we have single Xtensive.Orm assembly with a bunch of database drivers in separate assemblies.
  • Installer. It is no longer provided so use NuGet or binaries package instead

Download

Just as always, releases are available from NuGet gallery.

Avoiding connection pool fragmentation


Microsofties admitted that some noticeable performance problems are possible while working with MS SQL Server via ADO.Net (link, scroll to Pool Fragmentation). The connection pooling technique used in ADO.Net to optimize and minimize the cost of opening connections reported to be "not so optimal" in particular scenarios.

The root of the problem is that the exact match between connection string and corresponding connection pool is required. Even if 2 connection strings are build from the same key-value pairs but differ in their order, you get 2 connection pools.

As a result, we get connection pool fragmentation, which is a common problem in many Web applications where the application can create a large number of pools that are not freed until the process exits. This leaves a large number of connections open and consuming memory, which results in poor performance. Surprise for large multi-tenant application developers and maintainers!

From their side, Microsoft is not going to do anything about it in foreseeable future, instead they suggest to use a workaround: connect to master database and open the desired database with a separate command:

// Assumes that command is a SqlCommand object and that
// connectionString connects to master.
command.Text = "USE DatabaseName";
using (SqlConnection connection = new SqlConnection(
  connectionString))
  {
    connection.Open();
    command.ExecuteNonQuery();
  }

How can you achieve the same not leaving the zone of comfortable DataObjects.Net API? Time to get familiar with new member of DomainConfiguration class: ConnectionInitializationSql. The main purpose of the member is to literally execute the provided text as DbCommand right after a connection is opened.

Say, we are using Northwind database:


<domain
name="Default"
provider="sqlserver"
connectionString="Server=myServerAddress;Database=master;..."
connectionInitializationSql="USE NORTHWIND"
...>
<domain>


The feature is available in DataObjects.Net 4.6.4. Download DataObjects.Net

Thursday, November 14, 2013

Support for in-memory database

I'm not sure whether anyone remembers, but in the beginning of DataObjects.Net 4.0 epoch we had a semi-successful attempt to provide our own in-memory database implementation, which existed for several years but eventually was removed from the product by various reasons.

And guess what? Starting from DataObjects.Net 4.6.4, in-memory database support is back! But this time it is not our own version, but SQLite in-memory mode.

To connect to in-memory database use the following connection string:

<domain
  name="Default"
  provider="sqlite"
  connectionString="Data Source=:memory:"
  ...>
<domain>

The advantages of such kind of storage are the highest possible speed of database-related operations and no necessity to bother with database files on disk. On the contrary, there are also disadvantages, like the absence of multiple connections to database and no real persistence.

SQLite in-memory mode has some requirements that DataObjects.Net must have met. The most important one is that as soon as connection to in-memory database is closed, the database is destroyed and all data is lost.

Before version 4.6.4, DataObjects.Net always built database scheme in a separate connection which was closed after that procedure. Moreover, the ORM didn't keep any connections open at all, closing them as soon as corresponding session is disposed to free up resources and return connection to connection pool. To support the SQL in-memory mode we had to change the connection management layer and make it more flexible and configurable.

And so we did it. Now we open connection to SQL in-memory database only once on domain build and keep it open until domain is disposed, protecting the database from being destroyed, so your data is safe while domain is alive. In the meantime, you may open and close sessions as usual except you shouldn't open concurrent sessions as only one connection is allowed at a time.

Bad practice: using nested sessions with SQLite in-memory database. Session use the same connection concurrently.

  using (var session1 = domain.OpenSession()) {
    using (var t1 = session1.OpenTransaction()) {

      using (var session2 = domain.OpenSession()) {
        // Here you'll get InvalidOperationException 
        // that the connection is used by another session


Good practice: using subsequent sessions. Both sessions use the same connection, but not concurrently.

  using (var session1 = domain.OpenSession()) {
    using (var t1 = session1.OpenTransaction()) {

      // Do some stuff
      t1.Complete();
    }
  }

  using (var session2 = domain.OpenSession()) {
    using (var t2 = session2.OpenTransaction()) {

      // Do some stuff
      t2.Complete();
    }
  }

Tuesday, November 12, 2013

Enhancement in optimistic concurrency mode

Starting from DataObjects.Net 4.6.4 we are introducing an update to optimistic concurrency feature — server-side version check.

The present API of optimistic concurrency feature in DataObjects.Net with all these VersionSet, VersionCapturer, VersionValidator, whatever is kind of tricky, over-complicated and mind-blowing, so eventually we started moving towards more simple and transparent solution. The server-side version check is the first step in this direction.

The whole idea of server-side version check is obvious: each time an entity is fetched from database, it is associated with a version. On each successful update version is incremented. Each UPDATE command contains additional check for the specific version. Here is an example:

Say, we operate an online book store and use the following simplified Book model with Version field for optimistic concurrency check:

[HierarchyRoot]
public class Book : Entity
{
    [Field, Key]
    public int Id { get; set; }

    [Field(Length = 128)]
    public string Title { get; set; }

    [Field, Version]
    public int Version { get; set; }

    public Book(Session session) : base(session)
    {}
}

As the field is marked with VersionAttribute, DataObjects.Net detects and uses it to store the Book version which is automatically incremented after each successfully committed transaction. Now we want that every update command to include the additional check for version, e.g.:

UPDATE [dbo].[Book]  
SET [Title] = 'DataObjects.Net 4 unleashed' 
WHERE (([Book].[Id] = 123) AND ([Book].[Version] = 2)); 

If this check fails, the Xtensive.Orm.VersionConflictException is thrown with message "Version of entity with key 'Book, (123)' differs from the expected one", so it can be easily detected and handled.

By default, this mode in DataObjects.Net 4.6.4. is switched off to provide compatibility with the older versions. To switch it on we should change SessionConfiguration like this:

var sessionConfig = new SessionConfiguration(
    SessionOptions.ServerProfile | SessionOptions.ValidateEntityVersions);

using (var session = domain.OpenSession(sessionConfig)) {
    // do some stuff 
}

Alternatively, this can be done in configuration file so it is automatically applied to all sessions, like this:

<Xtensive.Orm>
    <domains>
      <domain name="Default"
              upgradeMode="Recreate"
              connectionUrl="sqlserver://localhost/AmazonBookStore">
        ...
        <sessions>
          <session name="Default" options="ServerProfile, ValidateEntityVersions" />
        </sessions>
      </domain>
    </domains>
  </Xtensive.Orm>