SQL - Tips To Improve The Performance Of Search Query

1. Use Limited Joins

Make a habit of using as little join as possible, using more joins degrades the query performance. Use only when it's absolutely required.

2. Indexing and Primary Key

Make sure to create indexes for the columns being frequently searched.

Make sure to put the primary key in the table, it also helps in boosting the performance.

3. Use Query Optimization/Plan Report

This report breaks the query processing into multiple parts and tells you how much time each part is taking.

Helps a lot in identifying the issue, and you can take necessary actions accordingly.

Note: Name might depend on DB to DB.

4. Restrict Columns in Select

Avoid fetching all columns or any unused columns, focus on the columns you need to accomplish the given task.

Don't try to reuse same query everywhere in which you're using unused columns. 

Create another method, and write a separate query.

5. Avoid Creating Too Long Horizontal Tables

Avoid creating 100s of columns in single table, use normalization. 

Break down the table into different tables.

Map them with primary/foreign keys.

6. Avoid Wildcard-based Search

The wildcard-based search slows down the query performance.

It scans all the records to find correct results.

Avoid them if the same job can be done with an alternate approach.

7. Avoid Subqueries

Too many subqueries can degrade the performance very badly. 

They get executed for each row fetched so it is advisable to limit their use.

8. Prefer EXISTS over Joins

Sometimes, to check if a record exists or not, we use join with the main table.

For example, if you are not retrieving the columns of the other table which you've joined, then use EXISTS instead of Join.

EXISTS is blazing fast!

9. Prefer Searching on Primary Key

Primary keys are optimized for searching, whenever possible always use primary key in where clause instead of using any non primary key column.

10. Consider, Restructuring Your Tables

You should consider restructuring if you find some glitch with the DB design.

Or if you later found there is a better design available that can solve the performance issue. Use it as last resort.