TechWare

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 ...

  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 ...

What's the logical order of SQL query operations? SQL Query Execution Order: 1. FROM & JOIN: The DB looks at the tables we specified in FROM and in the JOINs, and then merges this data - this merged data is our starting "working set". 2. WHERE: Perhaps we don't want the whole merged working set - perhaps we want to filter this data, so this means we end up with a subset of our working set - we've retained some rows and discarded other rows. 3. GROUP BY: Time to make some buckets! When we're grouping data, we're creating buckets (groups). Each bucket has a key and the associated rows. These buckets help us perform aggregations. 4. HAVING: Perhaps we don't want all our buckets. This is where we can specify some conditions, so that we choose just some buckets, and discard others. (This is similar to the WHERE clause - in WHERE, we are filtering rows, whereas in HAVING we are filtering buckets). 5. SELECT: We can select column names as well...