XPO to Database Connectivity: Mastering Fork Etiquette

UPDATED:

The DataStoreFork API is internal and undocumented and was described in this post before the official XPO solution for creating pooled connections was implemented.

Use the DevExpress.Xpo > XpoDefault > GetConnectionPoolString API in the latest XPO versions for this task.

=======================

Check out this how-to article that shows a new way of setting up XPO to work with websites, where a large number of simultaneously connected users is a common occurrence. It may help you reduce server response latency for pages that depend on data supplied by eXpress Persistent Objects.

Let's consider a standard approach to XPO data layer creation in an ASP.NET website:

// C#
using System.Configuration;
using System.Data.SqlClient;
using DevExpress.Xpo;
using DevExpress.Xpo.DB;
using DevExpress.Xpo.Metadata;

IDataLayer GetDataLayer() {
    ReflectionDictionary dict = new ReflectionDictionary();
    dict.CollectClassInfos(typeof(Person).Assembly);

    string connStr = ConfigurationManager.ConnectionStrings["PrimaryConnection"].ConnectionString;
    SqlConnection conn = new SqlConnection(connStr);
    IDataStore store = new MSSqlConnectionProvider(conn, AutoCreateOption.SchemaAlreadyExists);

    return new ThreadSafeDataLayer(dict, store);
}

' VB.NET
Imports System.Data.SqlClient
Imports DevExpress.Xpo
Imports DevExpress.Xpo.DB
Imports DevExpress.Xpo.Metadata

Private Function GetDataLayer() As IDataLayer
    Dim dict As New ReflectionDictionary()
    dict.CollectClassInfos(GetType(Person).Assembly)

    Dim connStr As String = Configuration.ConfigurationManager.ConnectionStrings("PrimaryConnection").ConnectionString
    Dim conn As New SqlConnection(connStr)
    Dim store As IDataStore = New MSSqlConnectionProvider(conn, AutoCreateOption.SchemaAlreadyExists)

    Return New ThreadSafeDataLayer(dict, store)
End Function

A single database connection object is created in the code snippet above, which is then used by XPO for data access. 

Generally, an ASP.NET application is a multi-user application. The situation when two users are requesting a webpage simultaneously is quite common. IIS and ASP.NET support multithreading. Said differently, two user requests can be processed in parallel. However, when it comes to querying the database, the second user is kept waiting while the first user's query is being processed, because a single database connection object has been created for the XPO data layer.

Creating a separate XPO data layer for each user or even for each page request is one possible solution here. This solution, however, is not perfect: XPO layer creation and establishing a database connection is a slow and resource expensive operation. Moreover, a ThreadSafeDataLayer, as it follows from its name, already supports multithreading. If one could create a pool of multiple connection objects and teach the XPO layer to select the first free available connection for the next database query, that would be really cool.

Fortunately, ThreadSafeDataLayer already can handle a connection pool called DataStoreFork. That is to say, you create an IDataStore object array, instantiate a DataStoreFork, which is then passed to the ThreadSafeDataLayer constructor:

// C#
IDataStore GetDataStore() {
    string connStr = ConfigurationManager.ConnectionStrings["PrimaryConnection"].ConnectionString;
    SqlConnection conn = new SqlConnection(connStr);
    IDataStore store = new MSSqlConnectionProvider(conn, AutoCreateOption.SchemaAlreadyExists);
    return store;
}

IDataLayer GetDataLayer() {
    ReflectionDictionary dict = new ReflectionDictionary();
    dict.CollectClassInfos(typeof(Person).Assembly);

    const int maxConnections = 3;
    IDataStore[] stores = new IDataStore[maxConnections];
    for(int i = 0; i < maxConnections; i++)
        stores[i] = GetDataStore();

    return new ThreadSafeDataLayer(dict, new DataStoreFork(stores));
}

' VB.NET
Private Function GetDataStore() As IDataStore
    Dim connStr As String = Configuration.ConfigurationManager.ConnectionStrings("PrimaryConnection").ConnectionString
    Dim conn As New SqlConnection(connStr)
    Dim store As IDataStore = New MSSqlConnectionProvider(conn, AutoCreateOption.SchemaAlreadyExists)
    Return store
End Function

Private Function GetDataLayer() As IDataLayer
    Dim dict As New ReflectionDictionary()
    dict.CollectClassInfos(GetType(Person).Assembly)

    Const maxConnections As Integer = 3
    Dim stores(maxConnections - 1) As IDataStore
    For i As Integer = 0 To maxConnections - 1
        stores(i) = GetDataStore()
    Next i

    Return New ThreadSafeDataLayer(dict, New DataStoreFork(stores))
End Function

How many connections should one create for DataStoreFork? We dare say that the number of connections must not exceed the number of processors (CPUs) installed on a given server, where the database is running.

We also want you to be aware that the use of DataStoreFork may result in not any noticeable effect at all. Usually, the most common database queries in ASP.NET - SELECT and UPDATE - are processed with lightning speed, while data is transferred via fast LAN, to say nothing about situations where IIS and a database server are running on one and the same system. If users of your website experience performance issues, do not immediately introduce DataStoreFork, but execute thorough performance profiling to discover any bottlenecks.

The use of DataStoreFork may make sense in other multiuser environments with a shared XPO data layer besides ASP.NET, e.g. in Web services or in 3-tier applications with XPO on the middle layer.

Free DevExpress Products - Get Your Copy Today