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.
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.
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.
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.
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.
7. Optimizing Queries:
Using the WHERE clause in SELECT queries narrows down the rows returned, reducing the SQL resources and decreasing the network bandwidth.
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:
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.