Home About Courses Schedule Services Webinars Contact Search

SQL Top 25+ Tuning Tips for Developers

SEE SCHEDULE

Duration: 2 Days

Method: Instuctor-led. Hands-on workshops.

Price: $1195.00

Course Code: SQ1005


Audience

This class is ideal for Programmers, Analysts, Database Administrators, IT Managers, or anyone writing SQL queries regularly. It covers SQL tips for any relational database management system, however queries within the material are specific to Db2, SQL Server, Oracle, and PostgreSQL.

Description

Like all programming languages, Structured Query Language (SQL) can be written in an efficient way or not. SQL developers may see a drastic improvement in performance if they learn a few simple coding techniques. Embedding SQL statements in a program introduces even more performance implications to consider.This is a class that anybody writing SQL on any relational database system should take to learn to write more efficient SQL. By following a good set of SQL standards and guidelines and understanding the inefficiencies of the language, response times can be reduced which can lead directly to CPU savings and lower overall IT costs.Students will leave with a will better understanding of the SQL language and a knowledge of coding techniques to use and avoid. They will learn how to code queries in multiple ways that return the same set and evaluate each for performance. They will learn about how database optimizers evaluate and cost queries and where to start when analyzing performance problems. In short, they will be better SQL developers! Do most developers know where to start and look when it comes to a performance issue? They will after this class.

Prerequisites

Basic SQL. Our SQ1000 class or equivalent experience.

Topics

  • I. Optimizer
    • What is the optimizer?
    • How does it come up with an access plan?
    • What does cost based optimization mean?
  • II. SQL Tuning Tips
    • Indexable vs Non-Indexable predicates
    • Sargeable vs Non-Sargeable predicates (terms in some RDBMS)
    • Other predicate types
    • What makes an inefficient query
    • What makes an inefficient program
    • What causes SQL sorts
    • Query rewrites
  • III. Indexes and Table Scans
    • Why indexes are chosen
    • Why a table scan is chosen
    • Why table scans are typically bad
    • Predicates that will cause table scans
    • Predicates to take advantage of indexes
  • IV. DBMS System Catalog and Data Statistics
    • What are data statistics
    • Why are statistics important
    • Types of data statistics
    • What are the system catalog tables
  • V. Take Aways
    • SQL predicate rewrites
    • SQL Standards and Guidelines specific to performance
    • Top 10+ steps to tuning a query