In this programming tutorial you will learn the MS SQL Server optimization techniques for asp.net programmers. MS SQL Server is a widely used database with websites developed in asp.net technology. If your website is connected with a large database that contains hundreds of tables and lot of visitors come daily to your website then you must have to explore your database and optimize it in order to get more efficiency from it.
The list below includes best techniques when developing or maintaining the website interacting with the MS SQL Server database. Try to adopt these optimization techniques when building efficient, scalable website. So let’s have a look over them.
Avoid Select * statement in your SQL queries
Don’t select all columns from table by using * in your sql query. Always mention columns name in your sql query because sometimes we get unwanted columns in the result, definitely those unwanted columns consume memory, so why should we get those unwanted columns. Avoiding select * will give you double benefit.
First benefit.
Let’s assume we have a table named as web_tbl_student and in this table we have 25 columns and 1600 records but in our query we only want to get the student’s firstname, lastname, rollno columns (fields) when gender is male. So the best practice that reduce memory consumption and network traffic will be
select firstname, lastname, rollno from web_tbl_student where gender="male"
And literally the bad practice will be
select * from web_tbl_student where gender="male"
Second benefit.
Make your query flexible and easy to understand for other programmers in-case you are not more attached with the company you are working for :)
So the Bottom line is, please don’t put extra burden on database.
Try to Use stored procedures and views instead of large and nested queries
If you really want to boost up the performance of your ms sql server database then always use stored procedures and views instead of large and nested queries. Send shorter queries to database that include only the name of stored procedure or view instead of large and heavy strings.
Use constraints instead of triggers
Constraint is more efficient and effective than trigger; you can improve the performance by using constraints instead of triggers.
When creating tables, don't use nvarchar and nchar if it is not necessary
Don’t use nvarchar and nchar while creating the tables if they are not necessary.
If you don’t want to store the
Unicode characters in any column of your table or in whole table then please use varchar and char data types instead of nvarchar and nchar data types. Varchar and char need two times less space. I have already written a tutorial over
Difference between varchar and nvarchar Over it.
Use comments in Stored Procedures
Always use comments while creating stored procedures. It will not decrease the performance and efficiency; literally it will improve your performance and productivity when you come back to your T-SQL code after a long time.
Use SQL Server .NET data provider
You can get access to MS SQL Server database with different ways. There are multiple providers such as
OLEDB,
ODBC and
SQLServer.NET. The fastest and best is SQLServer.NET provider located in
System.Data.SqlClient namespace. Remember, you can use SQLClient for SQL Server 7 or later versions. It cannot be used on SQL Server versions older than SQL Server 7. In older versions you can use OLEDB or ODBC (I recommend OLEDB).
Avoid use of unnecessary DISTINCT clause
I have seen some web developers use distinct clause in almost every query, it is totally bad practice. You should only use it where you really want to use because it put extra burden on database.
Use UNION ALL instead of UNION
UNION ALL does not check the duplicate rows in database like UNION. Due to this UNION ALL is very fast. If there is no duplicate row in the table then use UNION ALL instead of UNION to get faster query execution.
Use SmallDateTime instead of DateTime
You should use SmallDateTime data type instead of DateTime. DateTime requires two times more space (8 bytes) than SmallDateTime (4 bytes). But one think you must know is that DateTime is precise. If you don’t want to save time intervals less than minute then you should use SmallDateTime.
Try to Use smallest possible integer data type
Again I have seen many web developers who by default choose int as a data type of column. Data type for column must be chosen according to value that will be stored in it. If column will store value within the range of 0 to 100 then in this case you should use tinyint data type rather than int.
Bigint, int, smallint and tinyint are integer data types available in ms sql server database.
tinyint data type can store numbers in the range from -128 to +127 / 0 to 255 (unsigned) only. Require 1 byte of storage per value.
Smallint data type can store numbers in the range from -32,768 to +32,767 / 0 to 65,535 (unsigned) only. Require 2 bytes of storage per value.
Int data type can store numbers in the range from -2,147,483,648 to 2,147,483,647 / 0 to 4,294,967,295 (unsigned) only. Require 4 bytes of storage per value.
Bigint data type can store numbers in the range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 / 0 to 18,446,744,073,709,551,615 (unsigned) only. Require 8 bytes of storage per value.
Use WHERE clause instead of HAVING CLAUSE
In order to boost up the performance of query, you should use WHERE clause instead of HAVING clause.
Must avoid SELECT COUNT(*)
This is the most important factor to discuss. Most of the asp.net web developers use
select count(*) from tbl_name
to get the total number of rows in the table. If your table contains 10,000 records then you muse think how much time that above query will take to give you the result as
Output:-
10000
Select COUNT(*) reads and scans entire table to return the total number of the rows. Using select count(*) is a bad practice, instead of select count(*) you must use the following query to get the total number of rows in the table
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tbl_name') and indid < 2
Sysindexes is the built-in table in MS SQL Server that contains each and every information related all tables saved in the database. There is ROWS column in the sysindexes table that contains the total row count for each table in your database.
Make habit to Use Stored Procedures
It is better and faster to get access to the data via stored procedures. So to get the maximum performance and efficiency always use store procedures rather than ADO.NET methods.
Set NO COUNT ON in stored procedures
If we use
NO COUNT ON command in stored procedures then the end users will not know how many rows effected by SQL query. It will boost up the performance and decrease the network traffic.
Use connection pooling on effective way
To get maximum from the connection pooling always use the same query string. You should only open connection when you really need to open and don’t forget to close connection when you don’t want to interact with database any more. Must remember to
drop the temporary objects and close the transactions before closing the connection. Try to avoid the use of application roles as well.
Use Server IP address in connection string
If you use the
server ip address instead of name resolution then your connection will be open very fast. On cluster use virtual SQL server ip address.
So that’s it. If you have any other ms sql server optimization techniques then share your thoughts with the rest of the world through our blog.
So these are the
MS SQL Server Optimization Techniques for ASP.Net Programmers.
I love your feedback.
