Performance Design Principles - Data Access Layer Considerations

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, Rico Mariani, and Alex Mackman

Consider abstraction versus performance.

If your application uses a single database, use the database-specific data provider.

If you need to support multiple databases, you generally need to have an abstraction layer, which helps you transparently connect to the currently configured store. The information regarding the database and provider is generally specified in a configuration file. While this approach is very flexible, it can become a performance overhead if not designed appropriately.

Consider resource throttling.

In certain situations, it is possible for a single request to consume a disproportionate level of server-side resources. For example, a select query that spans a large number of tables might place too much stress on the database server. A request that locks a large number of rows on a frequently used table causes contention issues. This type of situation affects other requests, overall system throughput, and response times.

Consider introducing safeguards and design patterns to prevent this type of issue. For example, implement paging techniques to page through a large amount of data in small chunks rather than reading the whole set of data in one go. Apply appropriate database normalization and ensure that you only lock a small range of relevant rows.

Consider the identities you flow to the database.

If you flow the identity of the original user to the database, the connection cannot be reused by other users because the connection request to the database is authorized based on the caller's identity. Unless you have a specific requirement where you have a wide audience of both trusted and nontrusted users, you should make all the requests to the database by using a single identity. Single identity calls improve scalability by enabling efficient connection pooling.

Separate read-only and transactional requests.

Avoid interspersing read-only requests within a transaction. This tends to increase the time duration for the transaction, which increases lock times and increases contention. Separate out and complete any read-only requests before starting a transaction that requires the data as input.

Avoid unnecessary data returns.

Avoid returning data unnecessarily from database operations. The database server returns control faster to the caller when using operations that do not return. You should analyze your stored procedures and "write" operations on the database to minimize returning data the application does not need, such as row counts, identifiers, and return codes.