SQL Optimization Techniques

Nikita Paul – Associate Consultant

We all know that SQL queries can be written in multiple ways. But there is always a better build of one query from another, which improves the performance while fetching the result from the database. One bad query, in the best configuration of the hardware, can misbehave. This can result in critical issues thereby affecting performance.

SQL Flowchart

As best practice, here is what we can do to optimize our queries:

1. Use 3BNF database design

A database design technique that organizes tables - This reduces the redundancy and dependency of data. 3BNF divides larger tables to smaller tables and links them using relationships that has no transitive functional dependencies.

For e.g.

3BNF Database Design

2. Avoid correlated subqueries:

A correlated query utilizes the results from the parent query, which runs row by row, returning the output of the outer query. This decreases the SQL performance.

For e.g.

SELECT C.Name, C.City, (SELECT Employee Name FROM Employee Data WHERE ID = C.Employee ID) AS Employee Name FROM Company C

After tuning the query:

SELECT C.Name, C.City, E.Employee Name FROM Company C LEFT JOIN Employee E ON C.CompanyID = E.CompanyID

3. Improve indexes:

Create your indexes carefully on all the tables where you have frequent search operations. Avoid index on the tables where you have fewer number of search operations and a larger number of insert and update operations.

4. Pattern Matching:

Use the pattern matching technique in the search operations. If possible, refine the search to the next level.

For e.g.

LIKE JOH%%Y - %Y cannot be effectively evaluated.
LIKE JOH% - Matches all the records with JOH , returning a subset of the database. Above mentioned wildcards, both give the similar results.

5. Reduce the number of columns in a table:

The fewer the number of columns in a table, lesser the space occupied by them. Since more rows can be accommodated on a single data page, lesser I/O overhead will be required to access the table’s data.

6. Right Data Type Usage:

There are various data types in SQL. Making use of proper data type as per value would increase the performance of the database server to a great extent.

  • Integer data from 0 through 255 — tinyint data type, one byte.
  • Integer data from -32,768 through 32,767 — smallint data type, two bytes.
  • Integer data from -2,147,483,648 through 2,147,483,647 — int data type, four bytes.
  • Use smalldatetime data type instead of datetime data type, if you need to store the date and time data from January 1, 1900 through June 6, 2079, with accuracy to the minute, take four bytes.
  • Varchar/nvarchar columns instead of text or ntext columns whenever possible.
  • As SQL Server stores text or ntext columns on the Text or Image pages separately from the other data, stored on the Data pages, it can take more time to get the text or ntext values.
  • Use char/varchar columns instead of nchar/nvarchar if you do not need to store unicode data.

7. Optimizing Queries:

Using the WHERE clause in SELECT queries narrows down the rows returned, reducing the SQL resources and decreasing the network bandwidth.

For e.g.

SELECT * FROM Employee Data WHERE Employee Name=’john’

If the above query is written in the below format, the data is being refined to the exact search.

SELECT * FROM Employee data WHERE LOWER(Employee Name)=’John’

Both the above queries give the same output, but the second query arrives at the results faster.

8. Use of temp table:

  • This problem is yet another to look out for. We can use a temp table in several areas, to stop double-dipping to the big tables. This may also be used to drastically reduce the processing power mandatory to join huge amounts of data.
  • When joining of data from one table to a large table, the performance hindering is reduced when the subset of big table data is joined after pulling out of it. This will also work when we have several queries to make similar joins at the same table.

9. Use constraints instead of triggers, rules, and defaults:

Constraints are much more efficient than triggers and this in turn increases the performance. Compared to triggers, constraints are more consistent and reliable, for the simple reason that you may make errors when you write your own code to perform similar actions as the constraints.

10. Use of procedures:

A procedure is a potentially large group of SQL statements. Procedures are first compiled by the database engine and then executed. Unlike an SQL statement, the database engine need not optimize the procedure before it is executed. Procedures are beneficial for queries that are executed on a regular basis.

SQL queries can be fine-tuned in various ways. The above-mentioned points are a few of the many ways to arrive at the search results at a faster pace, thereby, optimising the solution.

High-Impact dashboard. Fast and affordable.

Starts at $1500