SQL: 10 Tips to Improve the Performance of Search Query
a thread...
a thread...
1. Use Limited Joins
Make a habit of using as less join as possible, using more joins degrade the query performance.
Use only when it's absolutely required.
Make a habit of using as less join as possible, using more joins degrade 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.
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 depends on DB to DB.
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 depends 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.
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.
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.
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 much subquery can degrade the performance very badly.
They get executed for each row fetched so it is advisable to limit their use.
Too much subquery 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 exist or not, we use join with the main table.
For eg:
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!
Sometimes, to check if a record exist or not, we use join with the main table.
For eg:
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 optimised for searching, whenever possible always use primary key in where clause Instead of using any non primary key column.
Primary keys are optimised 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 which can solve the performance issue.
Use it as last resort.
You should consider restructuring if you find some glitch with the DB design.
Or If you later found there is a better design available which can solve the performance issue.
Use it as last resort.
That's it for now!
If you enjoy reading this thread then you might also like my previous thread:
If you enjoy reading this thread then you might also like my previous thread:
Small Announcement! 📢
I've opened up my calendar for my audience and love to interact with you over 1:1 sessions on:
1. Career Guidance/Mentorship
2. Candid Talks
3. Content Creation
For more details visit:
calendly.com
I've opened up my calendar for my audience and love to interact with you over 1:1 sessions on:
1. Career Guidance/Mentorship
2. Candid Talks
3. Content Creation
For more details visit:
calendly.com
Namaste, I'm Vikas!
I write a thread every Mon, Wed & Fri on
Java, Javascript & Fullstack Development.
Follow @vikasrajputin to read all my future threads!
I write a thread every Mon, Wed & Fri on
Java, Javascript & Fullstack Development.
Follow @vikasrajputin to read all my future threads!
**Correction**
In Point#8, It is not always true that a EXISTS Query will be faster than Joins.
In my personal exp, I've found but that's not completely true.
It depends on your DB server on which Query plan it has picked to execute the query.
1/2
In Point#8, It is not always true that a EXISTS Query will be faster than Joins.
In my personal exp, I've found but that's not completely true.
It depends on your DB server on which Query plan it has picked to execute the query.
1/2
So when facing performance issues with Join, try to use EXISTS in that query and check if the query is using an optimised Query Plan or not.
If yes move ahead, else think of some other way.
2/2
If yes move ahead, else think of some other way.
2/2
Thank you @JamieTheMashMan for correcting me here.
Really appreciate your efforts in bringing the above points.
Really appreciate your efforts in bringing the above points.
جاري تحميل الاقتراحات...