Data Access Layer Design Considerations

To help ensure that data access in your application is optimized for performance, there are several issues that you must consider and a number of decisions that you must make at design time:

• Design your data access layer based on how the data is used.


• Cache data to avoid unnecessary work.


• Connect by using service accounts.


• Acquire late, release early.


• Close disposable resources.


• Reduce round trips.


• Return only the data you need.


• Use Windows authentication.


• Choose the appropriate transaction type.


• Use stored procedures.


• Prioritize performance, maintainability, and productivity when you choose how to pass data across layers.


• Consider how to handle exceptions.


• Use appropriate normalization.

Reference: http://msdn.microsoft.com/en-us/library/ms998569.aspx