How to optimize and improve SQL Server Query Performance?

In this article, we will discuss how we can improve our SQL query performance.

The best way to achieve performance and scalability is to know the characteristics of the queries. There is a cooperation required for both developers and database administrators to improve the performance of the queries.
There a lot of parameters which help to give us the better performance of the queries execution. Some are as follows –

• Queries are correctly formed

Make sure your SQL queries are correctly formed. In case of joins, all parts of keys are included in the ON clause.

Make sure no DISTINCT clause by default added to your SELECT statements if your query does not return duplicate data. Duplicate data may be the reason for an incorrect join. Investigate your queries that return redundant data.

• Return only the columns needed

One of the most common performance issues are those queries that return too many columns unnecessarily. Using a SELECT * query not only returns unnecessary data, but it also can force clustered index scans for the query plan, regardless of the WHERE clause restrictions.

• Avoid Expensive operators

You should avoid some operators like LIKE, NOT LIKE etc. The LIKE operator with a value enclosed in wildcards (for ex. %value%) almost always causes a table scan. Operators like <> or NOT LIKE are also very expensive. Change your queries if you can.

• Avoid functions in WHERE clause

Columns in a WHERE clause are treated as an expression rather than a column, so the columns are not used in the execution plan optimization.

Let’s understand this with the help of an example.

Let say if you have a DateTime column in WHERE clause and you want to convert it using a DATE function, such query causes a table scan, even though there is an index applied over that DateTime column.

Example. Select BookID From Books WHERE DATEADD(day, 15, PublishedDate) = ‘10/11/2017’;

As shown in above query, there is a DateTime column named ‘PublishedDate’ in “Books” table in WHERE clause and a DATEADD function is used to convert its value, this query will cause “Books” table scan.

But, if you move the DATEADD function to the other side of the WHERE equation, an index can be used on the “PublishedDate” DateTime type column like below –

Example.  Select BookID From Books WHERE PublishedDate = DATEADD(day, 15, ‘10/11/2017’); 

Note – Implicit conversions also cause table and index scans.

For example – “varchar” and “nvarchar” datatype mismatches and “nchar” and “char” data type mismatches that can cause an implicit conversion. 

• Use Stored Procedures or Parameterized Queries

You should use stored procedures or parameterized queries to improve the performance and scalability.

Remember the following couple of points when you decide to store your SQL commands on the server by using stored procedures or using embed SQL commands in your application as hardcoded –

• Do separate your business logic from the data manipulation logic for performance and maintainability.
• Validate your business rules before you send the data to the database to help reduce network trips.
• Since stored procedures are stored on the server, there is no need to redeploy your application when your stored procedures change.
• By using stored procedures, you can reduce the amount of data sent to the server, especially when large SQL operations are run. In case of embed SQL, the full source code of the commands must be transmitted each time when the commands are run.
• Prevent SQL injection attack by using parameters in stored procedures as well as SQL queries.

• Use Cursor minimum

Cursors are used to perform a function row by row on the table. Cursors are very heavy to use and force the database engine to repeatedly fetch rows. Use forward-only and read-only cursors unless you need to update tables.

If you have a Primary Key on the table, Use WHILE loop to do the same work and try to avoid cursors.

• Avoid long-running code in Triggers

Since Triggers are always part of INSERT, UPDATE or DELETE calling transactions, a long-running code in a trigger can cause locks and result in blocking of other queries. Keep your trigger code as small and as efficient as possible.

• Use Temporary Tables and Table Variables appropriately

If you are using temporary tables frequently in your application, try to use Table variable or a permanent table instead. Table variables are cleaned up automatically at the end of the function, stored procedure or batch where they are defined in. Very large temporary tables are problematic. If you find that you are creating many large temporary tables, you may use a permanent table that can be truncated between users.

Try to avoid large table variables too, since table variables use the tempdb database. Also, table variables are not considered by the optimizer when the optimizer generates execution plans and parallel queries. Therefore, table variables may cause decreased performance. Finally, table variables cannot be indexed as flexibly as temporary tables.

• Full Qualified Database Objects

Use always fully qualified database object that will minimize the overhead for name resolution.

 Let’s understand this with the help of below example.

Let say there is a query like

Select * from dbo.Books;

Will perform better than

Select * from Books;

This same case applies to stored procedures as well. In case of non-fully qualified objects, the amount of time that is spent to resolve a non-qualified name adds up.

To be continued…..

---

To Learn Angular 5 from scratch, Watch the following videos -

Angular 5 Tutorial - Project Setup using Angular CLI & Create First Angular App?

Angular 5 Tutorial - #2 - Using Bootstrap in Angular 5 App?

Angular 5 Tutorial - #3 - How Angular 5 App gets loaded its components?

For upcoming videos, Please like & subscribe our channel.