High performance applications that need to be optimised for persistence are often referred to as OLTP (online transaction processing) systems. This type of system is focused on maintaining high levels of transactional throughput. When designing a system of this type the following factors should be considered:
Transactions. Ensure that transactional boundaries are kept as small (in terms of time and scope) as possible without compromising data integrity.
Choose an appropriate isolation level, such as serializable, repeatable read, read committed etc.
Unless multiple data sources are involved use database transactions, rather than .NET Enterprise Services transactions. These should only be used when data is distributed across multiple databases, message queuing systems etc.
Database locking. There are two mechanisms for locking data in a database, pessimistic locking and optimistic locking. Consider using optimistic locking where possible, this can be implemented using timestamps. Another consideration is the use of locking hints, which in certain circumstances can be used effectively with an appropriate transaction isolation level.
Normalisation and redundancy. Ensure that the database structure is highly normalised (e.g. third normal form). This reduces redundant data and makes creating and updating data faster since it is only stored (ideally) in a single place.
Data volume. Ensure that only data that is required to carry out day to day business functions in persisted in the OLTP database. Any historical data should be archived out into an OLAP system such as a data warehouse, to ensure that database access is as fast as possible.
Reporting. Create a separate reporting database or data warehouse. Don’t allow users to run resource intensive reports on the OLTP database. If a single user is allowed to do this it could reduce performance for all other users significantly
Concurrency and threading. Consider putting background tasks that are not time sensitive on low priority threads and making them asynchronous, leaving more resources for time sensitive operations.
Caching. Reduce expensive database access by caching data that does not change often. Microsoft had recently released a distributed caching technology, known as Velocity; another popular option is NCache from Alachisoft.
You must log in to post a comment.