Click here to Skip to main content
15,905,683 members
Articles / Database Development / SQL Server / SQL Server 2008R2
Tip/Trick

How to Improve Performance of Query

Rate me:
Please Sign up or sign in to vote.
4.40/5 (13 votes)
8 Oct 2014CPOL2 min read 16.3K   11   6
This tip will provide you with an idea about basic things that you can use to get better performance from query.

Introduction

This is a tip to increase performance of your SQL queries.

Reduce the Number of Columns and Rows

Each and every column in a table has a datatype and each type will take sufficient bytes. So, more columns or more rows will mean more bytes of data. More data means more time to transfer that over network.

So, always select the required number of columns and rows.

Use SQL Hints (NOLOCK)

Several times, it happens that our query returns few records but the time it takes is much more in comparison to data. A reason for this could be locking. To avoid any delay because of locking, always use WITH(NOLOCK) in your select statements. I personally faced this issue, a SP that returns one record was talking very long.

Use Indexes

To get faster results, always implement proper indexes on your tables. Indexed query gets executed faster since SQL will scan the index instead of scanning whole data rows. This will reduce the logical reads and provides faster execution.

Use Sargable Queries

Sargable query means the query that will take advantage of indexes. Yes, this is correct, the way we write a query will decide whether it will use indexes or not.

A query is said to be sargable if it doesn’t have functions in Where clause.

For example:

If we have a conditional parameter that will have value in some cases and rest it will be null. Mostly people like query as:

SQL
SELECT  <columns to select> FROM <Table> WHERE <Column>= ISNULL(@Param,<Column>);

This is the easy way to write the query but it is not sargable since it is using ISNULL function. SQL will not be able to confirm the value (which value to use) at the time of compilation, so in this case it will read all data rows and then fetch the required rows.

Sargable Form will be:

SQL
SELECT  <columns to select> FROM <Table> WHERE <Column>= @Param or @Param IS NULL;

Similarly, If we need to fetch data for current Year, we can do non- sargable like:

SQL
SELECT  <columns to select> FROM <Table> WHERE Year(<column>) = 2014

Sargable query will be like:

SQL
SELECT  <columns to select> FROM <Table> WHERE column >= ‘01-01-2014’ AND column<’01-01-2015’

To get performance from your query, always write sargable queries.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Mindfire Solutions
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
RUs12314-Oct-14 0:05
RUs12314-Oct-14 0:05 
GeneralMy vote of 5 Pin
Andres Cassagnes9-Oct-14 7:44
Andres Cassagnes9-Oct-14 7:44 
GeneralRe: My vote of 5 Pin
Arora_Ankit30-Oct-14 22:24
Arora_Ankit30-Oct-14 22:24 
you are welcome
QuestionBe very careful with NOLOCK! Pin
PeejayAdams9-Oct-14 2:29
PeejayAdams9-Oct-14 2:29 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun8-Oct-14 22:36
Humayun Kabir Mamun8-Oct-14 22:36 
SuggestionUse option(recompile) Pin
Peter BCKR8-Oct-14 5:13
Peter BCKR8-Oct-14 5:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.