High-Performance .NET Application Development & Architecture

Data Access Layer

The data layer, on the other hand, is where we'll be covering various scenarios, and the best methods to use for the best performance and results. This layer encapsulates and compartmentalizes all our data access code within nice, clean components, that interact with our database. Also, keep in mind that the following tips apply to non-Tier standalone applications as well :-)

Incidentally, take the time and prep yourself with An Introduction to ADO.NET for a good look at ADO.NET, with v1.1 features as well, prior to diving into this section. Furthermore, although this section is named Data Access, it could nevertheless include XML as a viable data source. Therefore, read Reading, Storing and Transforming XML Data in .NET for a good look into this methodology.

Now to start, some quick tips off the top dealing with both aspects of data, SQL and ADO.NET. We'll divide these here solely to offer optimal means in each for prime data access. This involves both efficient database design and queries, and employing proficient ADO.NET techniques in retrieving already polished data.

SQL

  • Normalize your data for maximum efficiency and faster performance.

  • When creating new SQL tables always consider the best data types to employ with the type of column data you'll be storing.

  • Index your tables using SQL's Create Index command and or Query Analyzer's Index Tuning Wizard, assuring that your databases are finely tuned.

  • Use SQL Stored Procedures for all your data access, as SQL Server compiles them for all future uses. Furthermore, in SQL, straight and narrow is the best method in writing SQL Stored Procedures. Never overcomplicate your procedures with unnecessary or excessive temporary tables, server-side cursors , as these do create performance bottlenecks. You're better off creating subqueries off a base query and or with a join (avoiding left joins).

  • Never name your stored procedures with a "sp_" prefix, as SQL will interpret this as a system procedure.

  • Create SQL Views for added clarity and security.

  • Include SET NOCOUNT ON in all your stored procedures, as this diminishes network traffic by eliminating the need for SQL in always returning how many rows the procedure affected.

  • Use sp_executesql to execute any standard non-Sproc SQL statements in your code, gaining Stored Procedure-type benefits and reducing overhead:

    sp_executesql N'Select * from table' 

    and even within a Stored Procedure, instead of strictly EXECing any query strings:

    DECLARE @sqlQuery nvarchar (100)

    SET @sqlQuery = N'SELECT * FROM database.dbo.table'

    EXECUTE sp_executesql @sqlQuery


  • Finally, optimize SQL Server itself to maximize performance. Read SQL Server Settings Optimization Tips for more info.



    ADO.NET


  • Create all your database access routines as generic, versatile objects, rather than client-side repeated-code methods. All your UI should do is interact with these components, and not have to work out any details. The methods aforementioned in the Presentation Layer apply here as well in creating components and controls.

  • For all intents and purposes, the golden rule for data access is as follows: If you want to page data or provide your application with functionality use a Dataset as the preferred method of disconnected data, otherwise use a Datareader for all your data retrieval. For XML users this would translate to an XmlReader , and StreamReader for text files, both equivalent as that of a DataReader for their respective file types.

  • Use the correct managed data provider for your particular database, ex. System.Data.SqlClient for SQL Server , System.Data.OleDb for Access, System.Data.OracleClient for Oracle , etc.

  • Use Strongly-Typed Datasets over the standard, common un-Typed ones when possible, as this yields better performance. A generated typed Dataset is basically an early bound class inheriting the Dataset base class, as opposed to the typical late bound, non-inheritable Dataset. Thus, it allows for greater flexibility in dealing with your data. In turn, you'll be dealing with easier to read code, whereby you can access fields and tables by customizable names, instead of the conventional collection-based way. There's a good article here on DNJ that you should definitely read called Using Typed DataSets . Here you'll find all you need to know to get you going.

  • As mentioned briefly in the General .NET Best Practices, take full, and I mean full advantage of .NET Caching, as this will significantly boost performance and greatly diminish any persistent database interaction. However, and an important however is, if you decide on caching your data from within your data object, then conventional caching methods won't apply. Within the confines of components, and its interaction with the caller page, these requests happen via an HTTP request. Therefore, caching within your component can only be implemented by using the HttpContext.Cache Class property, part of .NET's Page class.

  • Use parameterized Stored Procedures along side .NET's Command Class Prepare() method, that caches your query for all future SQL Server uses.

    objCommand.Prepare()

  • Make chunky calls to your database rather than smaller, chatty calls. It's better to group all similar, associated calls in one SQL Server access. In other words, use one solid connection to retrieve as much as you can, as opposed to multiple ones.

  • Avoid using Universal Data Link ( UDL ) files for OleDb connections as these can cause potential performance hits. .NET's SQLClient managed data provider does not support this, as prior versions of SQL Server did.

  • Take advantage of connection pooling (whereby all your connection strings are identical) by storing all your connection strings in your web.config file. Furthermore, you'll find that your application will scale alot better with any increased network traffic by doubling, even tripling the default Max Pool Size of 100 to 300, and even bumping up the default Connect Timeout of 10 seconds to 60. Additionally, if your not enlisting any transactional procedures, include enlist=false; to your database's connection string for added performance.
    Additionally, if your not enlisting any transactional procedures, include enlist=false ; to your database's connection string for added performance.

    Example:

    <configuration>

    <appSettings>

    <add key="myDatabase" value="User ID=id; Password=pw; Data Source=datasrc; _
    Initial Catalog=dbCat; Enlist=false; Connect Timeout=60;
    Min Pool Size=10; Max Pool Size=300;"/>

    </appSettings>

    <configuration>

and call it from your page, code-behind source file or component like so:

            

[C#]

ConfigurationSettings.AppSettings["myDatabase"].ToString();

[VB]

ConfigurationSettings.AppSettings("myDatabase").ToString()

  • Finally, remember to close, clear and dispose of all your data objects no matter what. If you would like to further confirm that your database connection is indeed closed, you would write:

    if (dbConnection.State != ConnectionState.Closed) { dbConnection.Close(); }

You might also like...

Comments

About the author

Dimitrios Markatos

Dimitrios Markatos United States

Dimitrios, or Jimmy as his friends call him, is a .NET developer/architect who specializes in Microsoft Technologies for creating high-performance and scalable data-driven enterprise Web and des...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.” - Brian Kernighan