DevExpress XAF is a framework for .NET and Visual Studio developers who wants to create Office-like LOB apps for desktop, web and mobile faster and without hiring a large team.
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 users. Starting 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.
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
Subscribe to:
Post Comments (Atom)
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.
ReplyDeleteIf 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:
Deletehttps://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
In the new solution, we are using a pooling connection provider, not a single connection.
DeleteThis 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.
Should we do this to Workflow server, too?
ReplyDeleteNo, these optimizations aren't related to the Workflow Server.
Deletenew template in 17.1.3 has this:
ReplyDeleteargs.ObjectSpaceProviders.Add(new XPObjectSpaceProvider(XPObjectSpaceProvider.GetDataStoreProvider(args.ConnectionString, args.Connection, true), false));
I assume this is the same thing?????
Yes, see also: https://www.devexpress.com/Support/Center/Question/Details/T501418
Delete