Data Access (OLTP)---a Technical Reference Guide for Designing OLTP Solutions

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Designing the data access interactivity for an application is a critical task for the success of any deployment. Various new technologies have recently been introduced for the next generation of applications; however, the fundamental guidelines for efficient and effective database access continue to be relevant, even with enhancements such as SQL Common Language Runtime (CLR), User-Defined Types (UDTs), and User-Defined Functions (UDFs). Similarly, the common best practices for data access libraries (from ADO.NET to Windows Communication Foundation Data and Rich Internet Application Services) continue to apply.

Different technologies have evolved over time to meet different requirements. Choosing the right access technology requires a good understanding of the data and application use scenarios, and requires you to match the required abilities with the features and facilities available for the data access. Past experience indicates that data access technologies and APIs will continue to evolve, so it is important to structure your applications to accommodate that evolution. You can then benefit from new technologies as they become available with minimal impact on the rest of the application, making upgrades when appropriate.

Best Practices

This section provides some best practice guidance and resources for more information. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)

Technology Overview

General Design Guidance

  • The application retry logic is to be designed to catch any errors that occur while connecting to the database or executing commands (queries or transactions) on the database. When an error occurs, connectivity is to be re-established, and then failed commands are re-executed if necessary (not all failures mean the transaction failed). This is a very significant coding best practice and is needed to ensure that the user has a more pleasant experience in case of errors. For additional discussion, see the white paper Implementing Application Failover with Database Mirroring.7

  • Use stored procedures whenever possible. See the article Stored Procedure Basics8 for more information.

  • Avoid using the Microsoft distributed transaction coordinator (MSDTC) transactions when unnecessary. More information about the MSDTC appears in the Technical Reference Guide “Data Access - Distributed Transactions”.

  • The article SQL Server Connection Basics9 describes how to use remote procedure call (RPC) instead of language events for better performance.

  • As a general rule, keep transactions as short as possible and avoid round trips between the application layer and the DBMS inside a logical operation. Also avoid the use of Microsoft Distributed Transaction Coordinator (MSDTC) transactions when not necessary. For specific guidance on transactional behaviors of the data access code and for other performance optimizations, see the "Design Consideration" section of Chapter 12- Improving ADO.NET Performance.10

  • SQL Server connections are expensive to create and should be reused where possible. Connection pooling is a useful technology for achieving this goal. Pooled connections should be opened as late as possible and closed as soon as possible. Pools are per process for all the connections with the same characteristics (for example, connection string syntax, and security token). For specific connection pooling guidance, see the article Using Connection Pooling with SQL Server.11

  • Use Table-Valued Parameters (TVPs) to reduce database round-trips with stored procedures and to substitute large IN clause in ad-hoc Transact-SQL statements. For more information, see the article Table-Valued Parameters (Database Engine).12

  • Make sure to use parameterization or other techniques (for example, prepared commands and sp_executesql) to reuse existing query plans as much as possible and to reduce recompilations, as described in the article Plan Caching in SQL Server 2008.13

Case Studies and References

SQL Server Data access technologies have been critical in many deployments. Examples are included below:

Questions and Considerations

Review the following considerations in light of your project requirements; more details on these considerations are included in the reference material listed above.

Guidance for Native Data Access

  • For new native applications that need a generalized abstraction layer with support for multiple data sources, consider using Open Database Connectivity (ODBC). This is the most efficient, full-featured API with new feature support.

  • For specialized, high-performance data access to SQL Server, consider using SQL Server Native ODBC Client (C/C++), the Java Database Connectivity (JDBC) driver (Java/JavaScript), or the PHP driver, depending on your choice of language.

  • If you have invested in Visual Basics for Applications (VBA) and Active Server Pages (ASP) classic, continue to use ADO. NET; it is supported (including security fixes), but no additional enhancements are likely.

  • If you need component object model (COM)-based data access, use Object Linking and Embedding Database (OLE DB). This is supported, but no additional enhancements are likely.

Guidance for .NET Data Access

  • For new applications using one of Microsoft's Object/Relational Mapping technologies, consider the ADO.NET Entity Framework in .NET 4 (including language-integrated query [LINQ] to Entities) for data access.

    • The Entity Framework was introduced with .NET 3.5 SP1 and several improvements were introduced in .NET 4. New enhancements in object/relational mapping are currently planned for the Entity Framework.

    • Entity Framework 4.0 architecture is open to customizations, and performance tuning options are available to meet the requirements of Tier-1 highly transactional applications.

    • The Entity Framework can be incrementally adopted in applications that are currently using ADO.NET Core. For example, much of the connect/query/process code of ADO.NET Core can be replaced with entry-level Entity Framework code.

  • Consider using ADO.NET Core when you want precise control.

    • ADO.NET Core is the basis for data access in .NET and provides the common and familiar development patterns for data access (connect, query, process). For straightforward applications which require only simple connections and streaming results, ADO.NET may be a good choice to get a job done quickly.

    • If you have requirements for fine-grained control, ADO.NET might give you more capabilities than the Entity Framework. For example, ADO.NET Core is the API to directly support features such as table-valued parameters (TVPs) and spatial data types.

    • DataSets and LINQ to DataSet are supported and can still be useful in some scenarios. Specific guidance for LINQ to DataSet is described in the article Programming Guide (LINQ to DataSet).16

  • LINQ to SQL is currently supported, but is not likely to see new enhancements.

Appendix

Following are the full URLs for the hyperlinked text.

1 MSDN Data Developer Centerhttps://msdn.microsoft.com/en-us/data/default.aspx

2 Data Development GPS: Guidance for Choosing with Right Data Access Technology for Your Application Todayhttps://www.msteched.com/2010/NorthAmerica/DEV324

3 Guide to Data Development Platform for .NET Developershttps://msdn.microsoft.com/library/ff770157.aspx

4 Evolving ADO.NET Entity Framework in .NET 4 and Beyondhttps://www.microsoftpdc.com/2009/FT10

5 Overview of the Microsoft ADO.NET Entity Frameworkhttps://www.msteched.com/2010/NorthAmerica/DEV205

6 Deep Dive into the ADO.NET Entity Frameworkhttps://www.msteched.com/2010/NorthAmerica/DEV305

7 Implementing Application Failover with Database Mirroringhttp://sqlcat.com/whitepapers/archive/2007/12/16/implementing-application-failover-with-database-mirroring.aspx

8 Stored Procedure Basicshttps://msdn.microsoft.com/en-us/library/ms191436.aspx

9 SQL Server Connection Basicshttps://msdn.microsoft.com/en-us/library/aa175395(SQL.80).aspx

10 Chapter 12- Improving ADO.NET Performancehttps://msdn.microsoft.com/en-us/library/ff647768.aspx

11 Using Connection Pooling with SQL Serverhttps://msdn.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx

12 Table-Valued Parameters (Database Engine) https://msdn.microsoft.com/en-us/library/bb510489.aspx

13 Plan Caching in SQL Server 2008https://msdn.microsoft.com/en-us/library/ee343986(SQL.100).aspx

14 Epicor Software Corporation: Software Company Eases Development, Reduces Server Requests by 90 Percenthttps://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000006887

15 Veracity Solutions: Latest Developer Tools Increase Productivity by 20 Percent for Small Software Companyhttps://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000007305

16 Programming Guide (LINQ to DataSet)https://msdn.microsoft.com/en-us/library/bb387004.aspx