SQL Performance for the Common Developer
getting started in SQL performance
by
Brandon Moser

About Me - http://about.me/brandonmoser
Currently working as a CFML and SQL Developer for OLSONdenalî in Minneapolis
Current project uses CFWheels framework w/large extensions, SQL Server & Java Web Services, Load Balanced
Other projects: organic framework, Java Web Services, SQL Server
CFML since version 5
MS SQL Server 7.0 -> 2008 (v10.0)
MySQL SQL engine on OSS & side projects
When I'm not developing:
Computer Geek/Nerd/Etc.
Drummer
Downhill skiing/Slalom skier
Help write the CFML OSS Update for CFFocus.com

About You? A Quick Poll
MySQL, MS SQL Server, ORACLE, PostGRES, ?
MySQL specifically:
InnoDB, MyISAM
Version 4 (hopefully not), 5.0, 5.1, 5.5
Default: InnoDB v5.5.5+, MyISAM before v5.5.5
MS SQL Server specifically:
version 7.0, 2000, 2005, 2008 (R2)
Others/I Don’t Know

Presentation Overview
SQL Performance Review
Quick SQL Intro
Set Operations vs. Looping
Reduce Locking & Blocking
Tricks & Tips
Questions / Resources
Note: Short Question, please interrupt / Long Question, please wait until the end

What we will Not cover
Server Configuration
Index Configuration
Understanding a Explanation Plan

Performance Review
What can cause a Performance Issue

Common SQL Performance Issues
Slow Load
Reporting
ETL (Extract-Transform-Load)
Slashdotted/Digg Dugg
Server Overload
Query Blocking
Host System Hardware & Software Issues

Remember About Your SQL Server
It’s an Application Server (just like CF/Railo/OpenBD, HTTPd/IIS, etc.):
Requires proper configuration:
Defaults are poor choices (commonly)
High Availability settings not necessarily good for small sites
If using MySQL, learn the different Engine options
If use MS SQL, size of database will determine options
Has its own development language(s)
(server/version specific):
T-SQL
DDL

What Can Cause Performance Issues within your SQL Server
Bad/Incorrect Configuration
File I/O
Server overload from other processes
Ultimately not enough resources
Poorly Written Queries

Quick SQL intro/REVIEW

Intro to Joins - INNER JOINS
Inclusive
Can be used to link 1+ columns
Example:
returns 0+ rows depending on number of records in MemberAddress table

Intro to Joins - OUTER JOIN
LEFT OUTER JOIN / LEFT JOIN /RIGHT OUTER JOIN
only inclusive for “left” or “right” table (Members)
Left/Right is table on Left or Right of JOIN clause
EXAMPLES:
returns same set of ALL members in WHERE + Address (if present)

How to Run SQL Explanation
MySQL Server
Usage: Append EXPLAIN to any query
EXPLAIN
SELECT *
FROM tbl_users
WHERE user_id = 1;
MS SQL Server
Usage: SET SHOWPLAN_[ALL | TEXT | XML] ON
USE AdventureWorks2008R2;
GO
SET SHOWPLAN_ALL ON;
GO
SELECT BusinessEntityID
FROM HumanResources.Employee
WHERE NationalIDNumber = '509647174';
GO
SET SHOWPLAN_ALL OFF;
GO

Database Query Explain Terms
Table Scan
Searches Table row-by-row for data requested (slowest)
Pulls data from Table row
Index Search
Searches Index row-by-row for data requested (better, still slow)
Pulls data from Table row
Index Seek
Searches Index by reference for data requested
Pulls data from Index, not hit on the Table

EXPLAIN PLAN EXAMPLE - MS SQL

EXPLAIN PLAN EXAMPLE - MySQL

Thinking like a SQL Developer

Joins vs. Sub-selects
Demo
Why the performance gain?
each sub-query must finish for outer query to complete
each sub-query has a execution plan

Set-Based SQL Thinking
“Show the last price that each product was sold, along with the product name for each product”

Reducing Locking & Blocking
Transaction Isolation Levels

Transaction Isolation Levels
Why Care?
During a single transaction
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL [iso_level]
[query]
COMMIT (or ROLLBACK)
SELECT *
FROM table WITH(NoLock) << MSSQL only
WHERE a LIKE ‘someterm%’
On the server level
In Hibernate (and CF ORM)
Example:
http://blog.coldbox.org/post.cfm/cf9-orm-isolation-levels

Transaction Isolation Levels
SERIALIZABLE
Most Restrictive, Highest Isolation
Only Allows 2 or more transactions only if the illusion of serial execution can be maintained
REPEATABLE READS
Locks data during transaction, both Read & Write
READ COMMITTED
Restricted to Committed Data
Locks only on Write Data during full transaction
Read Locks release after SELECT completes, even within a transaction

Transaction Isolation Levels
READ UNCOMMITTED
Least restrictive, Lowest Isolation
“Dirty” Read
Transaction can “see” not-yet-committed changes
READ SNAPSHOT (new in MSSQL 2005)
Uses Row Versioning
Stores rows in TempDB

Some Tricks & Tips

Query Design Tips
Breaking down queries into smaller, more efficient queries
Bonus: Easier to debug
Summary Tables for Big Data reference
Weekly, Daily, Bi-Daily, Hourly updates for quick reporting and reference
Using temporary tables to process large data vs. complex joins
Takes data out of immediate production and into memory
Add Statistics to your Table rows, instead of querying for Details
In a Categories table, add NumItems, instead of getting “real-time” data

Data Search Example
Store Email Address in Reverse for quick lookups
One column EmailAddress (or email_address) for display
One column RevEmailAddr for quick lookups
Think how often,

“moc.liamg@...” or “moc.oohay@...” or “moc.liamtoh@...”,

would existing in your database
Searching LIKE ‘[email_address]%’ is uses the index
Searching LIKE ‘%[email_address]%’ does not use an index

CFQuery Tips
Reduce calls to database
Multiple queries vs Qry of Qry
Timeouts are not respected
Cache common data

Recap
If you have to maintain the DB Server, understand configuration options
Learn the different Joins and their use
If you can get/set all of the data in a single query, use it to your advantage
Use EXPLAIN (MySQL) or Explanation Plan (MS SQL)
If your web/CFML server can support it, cache your unchanging queries

Questions?

SQL Resources
MSSQL Server:
http://www.sql-server-performance.com
http://msdn.microsoft.com/en-us/library/bb545450.aspx (SQL Server Ref)
http://www.brentozar.com
http://www.sqlserverpedia.com
http://www.delicious.com/popular/sqlserver
MySQL:
http://www.mysqlperformanceblog.com
http://dev.mysql.com
http://www.delicious.com/search?p=mysql+performance

Contact Info
Email:            brandon@brandonmoser.com
Blog:            http://www.brandonmoser.com
Everything else:   http://about.me/brandonmoser
CF Focus Site:      http://www.cffocus.com/
My Company:      http://www.olsondenali.com
               http://www.oco.com