expressappframework-402149-debugging-testing-and-error-handling-performance-database-performance.md
This article explains how to analyze the application’s database performance.
You can collect diagnostic info in the following ways:
ORM-independent
ORM-dependent
The collected diagnostics info typically includes the following information:
If everything is fine with your SQL query performance in both production and development environments, go to the Application Performance article.
If you determined that SQL queries are slow, follow this topic to exclude the most common reasons for why performance of database-connected or distributed apps may become degraded.
Databases require ongoing maintenance to prevent poor application performance, system downtime, and data loss.
Most database systems (for example, Microsoft SQL Server) have good built-in implementation of backup, performance analysis, replication, and other essential maintenance functionality.
Even though a database created by XPO/XAF with their default settings can still be used for some time without maintenance, it is very important to note that for optimal performance and reliability these databases should still be maintained as time goes on, application data grows and other factors are involved during the application’s use in production. A good practice for application developers is to consider how database maintenance will be performed before the actual app goes to production, taking into account the specific knowledge of IT infrastructure used to deploy the system and other end client requirements.
You cannot find one-size-fits-all solution to database maintenance. Regular attention must be given to ensure the continued successful operation of any maintenance plan. In the list below, you can find recommendations for the database backup and other maintenance activities that are typically recommended on a regular basis to protect end application data and keep the system reliable, fast, and running smoothly:
For more details and specific instructions, refer to the documentation and related public community resources of your database vendor.
Well-designed indices can reduce disk I/O operations and consume fewer system resources therefore improving SQL query performance. When the number of records in database tables grows, the SELECT and INSERT queries may become slow. You may often need to add an index to columns involved in the WHERE, ORDER BY and GROUP BY operations. You can use XPO’s Indexed or Indices attributes to add an index for a new table.
If the indices are missing or corrupted, the following issues may occur:
Performance differences are often noticed when testing your app in production. You can analyze differences between the production and development environments, and experiment by making changes one by one. Common cases include the following:
If everything is fine with your database and environment and your SQL queries still cause performance issues, go to ORM Performance.
See Also