Search This Blog

Tuesday, March 14, 2017

XAF app performance: Reducing the number of simultaneous database connections



Multiple simultaneously opened connections consume your database server memory and thus, have negative impact on your application performance. To diagnose this situation, you can use the following SQL script:

SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame

Thankfully, there is a couple of tricks to optimize your existing apps in production (they are already applied out of the box in new projects).




Scenario 1: XPO-based web application with multiple concurrent users

Ensure that the code demonstrated in the Initialization of one XPO data layer per one global HttpApplication ticket is added to the Global.asax.cs file. This code creates a single IXpoDataStoreProvider and IDataStore  objects shared by all usersStarting with the 15.2.4 version, this code is automatically added by the Solution Wizard, but it may be missing in projects created with an earlier version of XAF.

Scenario 2: An XPO-based WinForms application in Instant Feedback mode

Ensure that the following code, which registers the XPObjectSpaceProvider or SecuredObjectSpaceProvider with enabled caching and connection pooling in the data store provider, is added to the WinApplication.cs file.Starting with the 16.2.5 version, this code is automatically added by the Solution Wizard, but it may be missing in projects created with an earlier version of XAF.

winApplication.CreateCustomObjectSpaceProvider += (sender, e) => {
    e.ObjectSpaceProviders.Add(new SecuredObjectSpaceProvider((ISelectDataSecurityProvider)winApplication.Security, 
        XPObjectSpaceProvider.GetDataStoreProvider(e.ConnectionString, e.Connection, true), false));
    e.ObjectSpaceProviders.Add(new NonPersistentObjectSpaceProvider(winApplication.TypesInfo, null));
};

See Also: How to measure and improve the application's performance

5 comments:

  1. Is using single connection effective from CPU point of view? As far as I know MS SQL single connection can benefit from only one CPU logical core. So it looks more practical to have a number of connections equal to CPU logical cores number at least.

    ReplyDelete
    Replies
    1. If we are talking about multi-threading applications like ASP.NET, using connection pooling is usually recommended (take special note that we now use GetConnectionPoolString). As far as I know, the pool size can really be dependent on CPU cores for better performance, though it also depends on use-cases like long or short transactions. You can learn more about this topic and connection pooling optimization in particular from public community resources:
      https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
      https://blogs.msdn.microsoft.com/angelsb/2004/10/08/ado-net-creating-a-single-connection-versus-relying-on-connection-pooling-my-two-cents/
      http://www.codeguru.com/csharp/.net/net_asp/article.php/c19395/Tuning-Up-ADONET-Connection-Pooling-in-ASPNET-Applications.htm
       
       

      Delete
    2. In the new solution, we are using a pooling connection provider, not a single connection.
      This provider returns a connection from its pool or creates a new one when required. So, we always have only the required quantity of open connections.

      Delete
  2. Should we do this to Workflow server, too?

    ReplyDelete
    Replies
    1. No, these optimizations aren't related to the Workflow Server.

      Delete