SQL Performance Tuning

 
   
  Table of Contents
SQL Performance Tuning
By Peter  Gulutzan, Trudy  Pelzer
 
Publisher : Addison Wesley
Pub Date : September 10, 2002
ISBN : 0-201-79169-2
Pages : 528
Slots : 1    


SQL Performance Tuning is a handbook of practical solutions for busy database professionals charged with managing an organization's critically important data. Covering today's most popular and widely installed database environments, this book is an indispensable resource for managing and tuning SQL across multiple platforms.

Packed with tips, techniques, and best practices, this hands-on guide covers SQL commands and queries over a wide range of conditions and environments. Every facet of the SQL environment is covered, from concurrency control to optimizationand every issue in between. The book includes an overview of each of the most widely used database management systems (DBMS) and provides tuning tips for common day-to-day situations.

Topics covered include:

  • General and specific tuning

  • General sort considerations and options

  • Joins and subqueries

  • Columns and tables

  • Indexes, including B-trees and bitmaps

  • Constraints and stored procedures

  • ODBC and JDBC

  • Data changes and concurrency control (locking)

  • Clients, servers, and middleware

Whether you are a programmer, consultant, or user , you will find SQL Performance Tuning an indispensable guide to optimizing SQL performance across today's popular database environments.

Throughout SQL Performance Tuning , ideas and techniques are tested on a variety of popular SQL environments, including IBM DB2, Informix, Ingres, InterBase, Microsoft SQL Server, MySQL, Oracle, and Sybase ASE. The book's practical examples are written in ANSI/ISO Standard SQL: 1999.

 
   
   
  Table of Contents
SQL Performance Tuning
By Peter  Gulutzan, Trudy  Pelzer
 
Publisher : Addison Wesley
Pub Date : September 10, 2002
ISBN : 0-201-79169-2
Pages : 528
Slots : 1     Copyright
      Preface
        You Don't Know that Your DBMS Is Good
        You Know that Even Good Tools Work Better in Skilled Hands
      Acknowledgments
      Chapter 1.   Facilis Descensus Averni
        This Subject Is Important
        The Big Eight
        Test Results
        Portability
        Terminology and Expectations
        Generalities
      Chapter 2.   Simple Searches
        General Tuning
        Specific Tuning
        Style Notes
        Parting Shots
      Chapter 3.   ORDER BY
        General Sort Considerations
        Character Sorts
        Other Options
        Parting Shots
      Chapter 4.   GROUP BY
        Refresher
        Optimal GROUP BY Clauses
        Sorting
        Set Functions and Summary Aggregates
        Parting Shots
      Chapter 5.   Joins
        Join Plan Strategies
        Avoid the Join Strategies
        Three-Way Joins and Beyond
        Old Style versus ANSI Style
        Parting Shots
      Chapter 6.   Subqueries
        Refresher
        Join versus Subquery
        Syntax Choices
        Parting Shots
      Chapter 7.   Columns
        How Big Is the Size Factor?
        Characters
        Temporals
        Numbers
        Bits
        Large Objects
        NULLs
        Column Order Within Rows
        Parting Shots
      Chapter 8.   Tables
        The Storage Hierarchy
        Heaps
        Clusters
        The Normal Forms
        Views
        Parting Shots
      Chapter 9.   Indexes
        Refresher
        B-trees
        Types of Indexes
        Bitmap Indexes
        Other Index Variations
        Index Key Values
        Parting Shots
      Chapter 10.   Constraints
        NOT NULL
        CHECK
        FOREIGN KEY
        PRIMARY KEY
        UNIQUE
        Triggers
        Disabling Constraints
        Client Validations
        Redundant SELECT Clauses
        Parting Shots
      Chapter 11.   Stored Procedures
        Refresher
        Advantages of Stored Procedures
        Parting Shots
      Chapter 12.   ODBC
        Refresher
        SQLPrepare
        Fetch Loops
        Data-Change Statements
        Catalog Functions
        Parting Shots
      Chapter 13.   JDBC
        Connections
        Query Prepping
        Result Sets
        Data Changes
        Parting Shots
      Chapter 14.   Data Changes
        Logs
        INSERT
        UPDATE
        DELETE
        Ugly Updates
        FETCH and Data Changes
        COMMIT and ROLLBACK
        Parting Shots
      Chapter 15.   Locks
        What Is a Lock?
        Isolation Levels
        Index Locks
        Hot Spots
        Optimistic Locking
        The Scan Trick
        Parting Shots
      Chapter 16.   Clients and Servers
        Middleware
        Server Processes and Threads
        What Should the Client Do?
        Parting Shots
      Chapter 17.   Cost-Based Optimizers
        Cost
        Statistics and Histograms
        EXPLAIN
        Hints
        Parting Shots
      Appendix  A.   Further Reading
      Appendix  B.   Glossary