Getting ready

Here, we will look at some basic rules that need to be kept in mind when executing queries:

• Know your data: Understand the relationships between tables, know your data, and fetch only the required columns.
• Visualize the answer set: Don't just keep on pulling the handle till you hit the jackpot; always try to know the count of possible rows expected.
• Break your queries: Use the various features of the database. Try volatile tables to do the calculations and then join it with the main query, or explore the option of breaking date range that turns YEARs queries into MONTHs.
• JOINS: Be reasonable with CROSS JOIN or LEFT joins. Bad joins with skew values result in high CPU and IO queries.
• Data types: Ensure appropriate data types for columns on the table(s). For example, don't define a Character data type for a Salary column.
• Pre-aggregate data: It's an old school technique. Do the aggregation in a volatile table and then you can join them to the main query. It avoids carrying large number of rows in joins.
• Repeating queries: The frequency of execution should be given consideration. A query executed thousands of times with 1,000 CPU seconds is a better tuning candidate than a 100,000 CPU second query which is only executed once.