In this article, we will explain about how to optimize your SQL 2014 query. Modern day software applications have millions of concurrent users. Development of an efficiently serviceable application requires a huge amount of effort and requires many tools and techniques. Software developers always try to improve the performance of the application by improving design, coding and database development. For database development, query optimization and evaluation techniques are playing vital parts.
Selection of required field only.
It is very important to avoid unnecessary data selection of the query. We should select a data field that we need but not all fields of the table.
SELECT login_id, pawwsord FROM tbluser
Index
Properly created Indexes help to optimize search results. You need to better understand the databases before the selection of a better performing index. The selection of a highly used field as an index is very important.
CREATE clustered INDEX ind_login_id ON tbluser(login_id)
Primary Key
The Primary Key is the most important index of the table. The most important thing about a Primary Key is the selection of a short and unique field. This will lead to easy access to the data records.
CREATE TABLE tbluser(
id INT,
name VARCHAR(150),
email VARCHAR(100),
login_id VARCHAR(100),
password VARCHAR(10),
primary_key(id)
)
Index unique column
The indexing of a unique column will improve searching and increase the efficiency of the database. You must have a better understanding of the data field and their utilization before indexing a unique column. The indexing of a less used column does not help improve the efficiency of the database.
CREATE INDEX ind_email ON tbluser(email)
Select limited records
None of the user interfaces can visualize thousands of records at once. Hence there is no way to select all the records at once, so always limit the selection when you have a large number of records. Select the required data only.
SELECT id, name, email, login_id,password FROM tbluser WHERE 1 limite 10
Selection of correct data type and length
Use the most appropriate data type and correct length of the data. The bad selection of a data type will produce bulky databases and poor performance. This will improve resource utilization of the database server.
CREATE TABLE tbluser(id INT,
name VARCHAR(150),
email VARCHAR(100),
login_id VARCHAR(100),
password VARCHAR(10)
)
Avoid in sub query
Always avoid use of IN sub-queries in your applications. An In sub-query will evaluate all the records of table A with table B (product of records) before selecting the required data.
SELECT login_id,name, email FROM tbluser WHERE login_id IN ( SELECT login_id FROM tbllogin_details)
One of the correct ways is to use an inner join as in the following:
SELECT login_id,name, email FROM tbluser INNER JOIN tbllogin_details ON tbluser.login_id =tbllogin_details.login_id
Avoid NOT operator
Please avoid the usage of the NOT operator situation that the number of qualifying records are lower than unqualified records. Always use a positive operator such as LIKE, EXIST than NOT LIKE, NOT EXIST.
SELECT * FROM tbluser WHERE email NOT LIKE ‘%gmail%’
The prefered way is:
SELECT * FROM tbluser WHERE email LIKE ‘%yahoo%’
HostForLIFE.eu SQL 2014 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. They deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.
