XPO to Database Connectivity: Mastering Fork Etiquette

XPO Team Blog
06 July 2010

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.
Mastering Fork Etiquette
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.

 


Tags
8 comment(s)
Neven
Neven

Cool! :) How to use this approach in ASP.NET where my DataLayer is defined in global.asax (Application_Start) typically:

       Dim conn As String = ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString

       Dim dict As DevExpress.Xpo.Metadata.XPDictionary = New DevExpress.Xpo.Metadata.ReflectionDictionary()

       Dim store As DevExpress.Xpo.DB.IDataStore = New MyDataStore(XpoDefault.GetConnectionProvider(conn, DevExpress.Xpo.DB.AutoCreateOption.SchemaAlreadyExists))

       dict.GetDataStoreSchema(GetType(Poruke).Assembly)

       XpoDefault.Session = Nothing

       Dim layer As Object = New ThreadSafeDataLayer(dict, store)

       Application.Add("XpoLayer", layer)

In my page init I retrive this information like this

uow = New UnitOfWork(CType(Application("XpoLayer"), IDataLayer))

7 July, 2010
Joe Hendricks
Joe Hendricks

Very helpful description, code and guidance on when to use/test!

Thanks!

7 July, 2010
Eric Von Zee
Eric Von Zee

Great article!   I was having trouble with a high-volume site today (whose SQL selects may be somewhat less than lightning-fast, as you say) and this was just what the doctor ordered!

However, are you aware of any problems this approach causes with session transactions?  After I implemented the change, everything else looks OK but my transaction unit tests are failing.

Thanks!

7 July, 2010
Eric Von Zee
Eric Von Zee

To clarify my previous comment:

The tests that are failing are for committing transactions (rollbacks seem to work fine.)  The test does the following:

1) Start a transaction via Session.BeginTransaction()

2) Create a new instance of an XP object, and save it

3) Commit that transaction

4) Use Session.FindObject<>(criteria) to lookup that new object

If I set a breakpoint and wait about 5 seconds between 3 and 4, I get the object back.  If I wait less than 5 seconds, I get back null.

If it matters, the tests are running against an Access database (which is only used for these unit tests, not in production, of course).  I haven't tried against SQL server.

Thanks,

-e

7 July, 2010
Iouri Savastiouk
Iouri Savastiouk

Is there any way to use XPO in a web application so that it uses the database connections from the pool?

8 July, 2010
Joe Hendricks
Joe Hendricks

Thanks for an excellent and very practical post!

12 July, 2010
Renaud Bompuis
Renaud Bompuis

Could we have an example of using the XPO Publication service with a similar setup?

I find the abundance of variations for setting up a datalayer to be a bit confusing, especially since the documentation for this rather complex subject is scattered over 4 years of blogs and not a single line in the help file...

I'm grateful for the flexibility though.

The main issue is how to properly configure a server-side and client-side setup that has good multi-user performance.

1) On the server side, is a single DataCacheRoot enough?

2) On the client side, should we use multiple DataCacheNodes using in the DataStoreFork with a single instance of the remote DataCacheRoot?

3) In that case, I'm not really clear how the DataCacheRoot can honour multiple simultaneous requests even though it's using a single database connection (or should we connect it to the through the DataStoreFork?)

So many questions...

13 July, 2010
Slaven Batnozic
Slaven Batnozic

This is how ThreadSafeDataLayer should work by default and I'm astonished that it doesn't. You almost converted web apps to single thread applications.

This:

"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."

should be first line in ThreadSafeDataLayer and XPO documentation. So, if I have 100 users and one of them executes longer running query other 99 wait for their turn. And that is default behaviour?

Instead docs state this:

"This class implements a data access layer that allows multiple threads to access data in a data store at the same time."

which obviously isn't true.

30 January, 2015

Please login or register to post comments.