SQL Server Optimization For ASP.Net Developer

SQL Server is great database system. If you have small database it will probably work well in every case. However, when your demands grow, database becomes bigger, your web site has more and more visitors etc., you need to take the best from your SQL Server.

The list bellow includes best practices when programming ASP.NET web application that use SQL Server. These tips should be your common habits when building efficient, scalable web applications.

Do not use SELECT * FROM... in you SQL queries

Don't select all table columns if you need just a few of them. Always select only columns you need. In that way you reduce use of memory and network traffic. It is important especially if you have large tables or a lot of concurrent users.

Use stored procedures and views instead of large queries

You need to reduce network traffic as much as possible. To do that, it is better to send shorter queries that include only name of stored procedure or view instead of large strings.

Use constraints, not triggers

Since constraint is more efficient than trigger, you can improve your web application performances on this way.

When creating tables, don't use nvarchar and nchar if it is not necessary

If you don't need international support with unicode characters in some column or in whole table, use varchar and char instead of nvarchar and nchar data types. Varchar and char need two times less space.

Use comments in Stored Procedures

Like in C# or VB.NET code, comments don't decrease your performances, but they will increase your productivity when you come back to your T-SQL code after a few months, or years.

Use SQL Server .NET data provider

You can access to SQL Server on different ways when use ADO.NET. There are providers like OLEDB or ODBC, but the fastest is SQLServer .NET provider located in System.Data.SqlClient namespace. You can use SQLClient for SQL Server 7 or later versions. It cannot be used on SQL Server 6.5, so for 6.5 version you can use OLE DB or ODBC (a little better is OLEDB).

Avoid use of DISTINCT clause

Some developers use it in every query, but you need to use it only where you need it because it creates a lot of extra work on SQL Server.

Use UNION ALL instead of UNION

UNION ALL does not check for duplicate rows like UNION. Because of that UNION ALL is much faster. If there is no duplicate rows in table, using of UNION only do an unnecessary step, performing a SELECT DISTINCT on the final record set. Avoid it and get faster query execution with UNION ALL.

Use SmallDateTime instead of DateTime

DateTime data type needs two time more space than SmallDateTime (8 bytes for DateTime instead of just 4 bytes for SmallDateTime). But, DateTime is more precise. If you don't need information about time intervals less than minute, use SmallDateTime.

Use smallest possible integer data type

There are bigint, int, smallint and tinyint integer data types. Depending on your needs, choose the one which takes minimum space, but still can store all needed values.

Use TOP and WHERE clauses as much as possible

Performances are better if your SQL query returns less data. Return only what you really need by using these two keywords. The one specific problem is if you need to enable data paging in GridView, Repeater, DataList or some other control. It is not recommended to load all data to DataSet to get correct GridView paging facility, and then show only one page on user's screen. If you plan to enable data paging, I suggest to consider our Pager Control. By using Pager Control you can save your memory resources and load only one page (e.g. 20 records) from database to DataSet object and still get functional customizable nice looking Pager.

Avoid HAVING clause

To improve performance of the query, you need to use WHERE clause instead of HAVING clause. In many cases, you can write your query with GROUP BY clause without HAVING clause by using WHERE clause only.

Avoid SELECT COUNT(*)

Select COUNT(*) reads complete table to return the number of the rows. Instead of that, to find how many rows table contains you can use much faster way:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('TableName')

Use Stored Procedures

It is faster to access to your data directly on SQL Server by using stored procedures, so to achieve maximum performance avoid using of ADO.NET methods.

Set NO COUNT ON in stored procedures

With this command, client will not receive information about rows affected by SQL query. This will decrease network traffic and boost performance.

Avoid SQL Server cursors

Cursor will make your query slower, but also lock a tables and use a memory. Try to replace it with few simple SQL queries. If you want to make a loop through all rows you can use derived tables, correlated sub query or sometimes even write a loop in your ASP.NET code, but don't use cursors.

Use connection pooling on the right way

To get the most of the connection pooling always use the same query string, open connection when you need it and close connection when you don't need it (don't have unused open connections), drop temporary objects and close transactions before closing a connection and avoid using of application roles.

Use Server IP address in connection string

If you use server ip address name resolution step will be skipped and your connection will open faster. On cluster use virtual SQL server IP address.

Avoid NULL values in table columns

NULLable columns need more space (extra byte for every row) and it is harder to code. You will need to check for NULL in your Data Access Layer code every time.

These are some good practices that should becomes a habits for every ASP.NET developer. Do you have some tip? Please let us know.

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |   Google