O'Reilly Open Source Convention.
Books Safari Bookshelf Conferences O'Reilly Network
   


Arrow Home
Arrow Registration
Arrow Speakers
Arrow Keynotes
Arrow Tutorials
Arrow Sessions
Arrow At-a-Glance
Arrow BOFs
Arrow Events
Arrow Community
 Software
Arrow Exhibitors
Arrow Sponsors
Arrow Hotel/Travel
Arrow Venue Map
Arrow See & Do
Arrow Press
Arrow Mail List


O'Reilly Open Source Convention
Hornbill.

Tutorial

Efficient SQL / Mastering SQL
Greg Sabino Mullane

Track: PostgreSQL
Date: Monday, July 07
Time: 1:45pm - 5:15pm
Location: Salon E

Applications:

  • Best practices for applications hitting the database
  • Balancing the work of the application and the database

    Schemas:

  • Normalization and speed: striking a proper balance
  • The role of indexes
  • When to rethink your design

    Database:X

    Brief discussion on database tuning, with an emphasis on things that affect the speed of your queries.

    SQL:

  • Application timing, explain plans, EXPLAIN ANALYZE
  • How to break apart a SQL statement
  • Best practices for testing. (real data vs. samples, running ANALYZE, simulating adverse conditions)
  • Breakdown of each part of a SQL statement, with an emphasis on speed and common traps and their workarounds, with examples:

  • Basic layout
  • Subselects
  • EXISTS, IN, etc.
  • UNION, EXCEPT, and INTERSECT
  • WHERE clauses
  • FROM clauses
  • DISTINCT
  • Indexes: the good, the bad, and the misleading
  • Sorting and ORDER BY
  • Cursors
  • LIMIT and OFFSET Cursors
  • CASE, COALESCE, nulls, and other things
  • Aggregate functions, GROUP BY, and HAVING
  • Dates and times
  • DDL commands
  • INSERTs and UPDATEs
  • Temporary tables
  • Views
  • Arrays
  • Triggers and functions
  • Misc. factors
  • Nested sets and other bugbears

  • Searching text fields
  • Searching non-text fields
  • Unicode and other I8N factors

    Putting it all together

    Two complex jobs will be presented; we will walk through each one and make them perform as efficiently as possible.

    SQL Variants (time permitting)

  • Dealing with SQL from another product
  • Alternatives to SQL
  • The future of SQL
  • The future of databases



  • O'Reilly Home | Privacy Policy

    © 2003, O'Reilly Media, Inc.