Introduction
The SQL query is a critical part of any software development and needs by many resources like
– Developer
– Tester
– Lead
– Manager
– Management (Some Time)
– Reporting Team
All needs SQL query to get the data from the database to verify their particular task. Seldom, they compose very big SQL with joining multiple tables and it retrieved the records from multiple tables based on a precondition.
Performance is a key factor and everyone should use the best practices while writing these kinds of SQL query.
Please find some tips to improve the SQL query performance below –
Use INNER JOIN in place of JOIN as it increases the performance of SQL execution.
Use the filtering condition along with the INNER JOIN in place of using WHERE Condition.
There should be indexes on all fields used in the WHERE and JOIN portions of the SQL statement.
Remove unwanted column from the return SQL statement as it helps to increase the performance of the SQL query.
Remove unwanted tables from the SQL statement as it helps to increase the performance of the SQL query.
Try to avoid correlated sub queries, because these can significantly decrease the speed of execution. Instead of a correlated sub query, you can use tables with a JOIN operator.
Avoid the use of IN, use the EXISTS clause in the main query instead of a sub query, and when you use UNION, try to use the UNION ALL clause instead of UNION alone.
There are four clauses and keywords where user can expect performance issues to occur: The WHERE clause, Any INNER JOIN or LEFT JOIN keywords; The HAVING clause;
The SELECT DISTINCT statement is used to return only distinct (different) values. DISTINCT
is a clause that you should definitely try to avoid if you can. As you
have read in other examples, the execution time only increases if you
add this clause to your query. It’s therefore always a good idea to
consider whether you really need this DISTINCT operation to take place to get the results that you want to accomplish.
When you use the LIKE operator in a query, the index isn’t used if the pattern starts with % or _. It will prevent the database from using an index (if it exists). Of course, from another point of view, you could also argue that this type of query potentially leaves the door open to retrieve too many records that don’t necessarily satisfy your query goal.
When you cannot avoid filtering down on your SELECT statement, you can consider limiting your results in other ways. Here’s where approaches such as the LIMIT clause and data type conversions come in. TOP, LIMIT And ROWNUM Clauses
You can add the LIMIT or TOP clauses to your queries to set a maximum number of rows for the result set. Here are some examples: SELECT TOP 3 * FROM Sample;
SELECT TOP 40 PERCENT * FROM Sample;
SELECT SampleNumber, SampleName FROM Sample LIMIT 2;
SELECT * FROM Sample WHERE SampleData = 582454 AND ROWNUM <= 30;
Don’t Use Data Type Conversions in the SQL Query if possible otherwise use most efficient data type. You should always use the most efficient, that is, smallest, data types possible. There’s always a risk when you provide a huge data type when a smaller one will be more sufficient.
When you use the OR operator in your query, it’s likely that you’re not using an index. The user should use alternative ways to avoid the OR operator in the SQL Query.
The Order Of Tables are very important, When you join two tables, it can be important to consider the order of the tables in your join. If you notice that one table is considerably larger than the other one, you might want to rewrite your query so that the biggest table is placed last in the join.
To Be Continue…..