Creating Understanding and Educating Programmers, Developers and Technical Communities, one post at a time.

Tuesday, June 9, 2009

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.



  1. Some good points, although I would say "Use stored procedures when applicable". For a lot of data access scenarios such as a lot of simple CRUD stuff, there is no benefit to using stored procedures.

  2. @Craig: In my opinion, CRUD stored procedure is good idea. There is no harm to use it. Individual queries are not good for Server and consume server resources.

  3. You can read Paul Nielsen's, a database expert, post about stored procedures.

  4. Now, you can follow Programming360 updates on twitter Programming360