SQL performance primer

Discover how to spectacularly increase the speed of SQL queries in this one-day training at ABIS.

Efficiency of data access is a main concern not only for DBAs but more so for query writers and application developers.

Luckily, there are several platform-independent "best practices" for optimally writing SQL queries.

After following this course, participants

  • will know how the optimizer prepares the execution of an SQL query;
  • will understand the importance of access paths, of indexes, and of the order in which processing can be done;
  • will be able to rewrite queries, based on this knowledge, such that they become much more efficient.

The participants will have the opportunity to practise the discussed techniques.

These topics are further explored in more depth in the platform specific courses Db2 for z/OS SQL performance and Oracle SQL performance.

Schedule a training?

Delivered as a live, interactive training – available in-person or online, or in a hybrid format. Training can be implemented in English, Dutch, or French.

REQUEST IN-COMPANY TRAINING

 

Public training calendar

No public sessions are currently scheduled. We will be pleased to set up an on-site course or to schedule an extra public session (in case of a sufficient number of candidates). Interested? Please let us know.

Intended for

SQL power users, application developers, analysts, and anyone who wants to optimize SQL queries in e.g. an infocenter environment, or in the context of a Data Warehouse.

Background

A good knowledge of the SQL SELECT syntax is needed, more specifically the subjects of our SQL fundamentals and our SQL workshop training.

You can test for yourself to see if you have enough background by filling out the on-line self-test "SQL Intermediate".

Main topics

  • Overview
    • The role of the optimizer and access paths
    • Common causes of slow SQL
    • Optimizing filtering—for example, avoiding functions and other formulas within WHERE predicates
    • Using indexable (SARGable) predicates
  • Joins, aggregation, and materialization
    • EXISTS and subqueries as alternatives to JOIN: when and how to use them
    • Optimizing JOINs: table order in a JOIN; types of JOIN implementations
    • Applying GROUP BY efficiently—for instance, by aggregating as early as possible
    • Using COUNT, SUM, and AVG intelligently, and combining aggregations where feasible
    • Avoiding to sort large volumes of data
    • Identifying hidden sorting in a query, such as those caused by DISTINCT or UNION
  • (Re)writing queries: when and why
    • EXISTS vs. IN vs. JOIN; recognizing and effectively using correlated subqueries
    • Caution regarding negative conditions (e.g., NOT IN)
    • CTEs as an alternative to subqueries
    • The importance of readability, logical flow, and standardization when writing SQL
  • Best practices – Tips and tricks
    • Writing SQL that is both readable and efficient: some rules of thumb
    • Avoiding unnecessary calculations and deferring complex ones—how and when
    • Minimizing data processing through early filtering
    • A performance checklist for every query

Training method

Live training by an instructor, with ample time for practical exercises and group discussions.

Certificate

At the end of the session, the participant receives a "Certificate of Completion".

Duration

1 day.

Course leader

Peter Vanroose (ABIS), Kris Van Thillo (ABIS).


SESSION INFO AND ENROLMENT