Consideration when writing an SQL statement is that it returns a correct result. The second is that it be the most efficient for a given situation. You can use many different SQL statements to achieve the same result. It is often the case that only one statement will be the most efficient choice in a given situation. Remember that processing SQL is a sequence of Parse (syntax check and object resolution), Execution (required reads and writes), and Fetch (row results retrieved, listed, sorted, and returned). SQL “tuning” consists, quite simply, of reducing one or more of them. Note: generally Parse is the greatest time and resource hog. Parse overhead can be minimized by the use of Procedures, Functions, Packages, Views, etc. Inadequate performance can have a significant cost impact on your business. A poor performing system and application can result in customer dissatisfaction, reduced productivity, and high costs. It is absolutely critical that the system’s performance is oper
Follow 17 Best practices for writing optimized SQL queries: 1. Use EXISTS in place of IN wherever possible 2. Use table aliases with columns when you are joining multiple tables 3. Use groupby instead of distinct. 4. Add useful comments wherever you write complex logic and avoid too many comments. 5. Use joins instead of subqueries when possible for better performance. 6. Use WHERE instead of HAVING to define filters on non-aggregate fields 7. Avoid wildcards at beginning of predicates (something like '%abc' will cause full table scan to get the results) 8. Considering cardinality within GROUP BY can make it faster (try to consider unique column first in group by list) 9. Write SQL keywords in capital letters. 10. Never use select *, always mention list of columns in select clause. 11. Create CTEs instead of multiple sub queries , it will make your query easy to read. 12. Join tables using JOIN keywords instead of writing join condition i