How to Improve Performance of 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:

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:

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:

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

Sargable query will be like:

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.