Query Optimization
Presented by Mary Radnor
Overview of Query Optimization
•Purpose
•Steps to Optimizing Your Own Queries
•General Techniques
•MS Access
•ORACLE
•Example
•Summary
Purpose of Query Optimization
•To optimize the performance of the database
Because…
•Queries having the same final output will vary in the amount of
time it takes to execute them
•No single optimization technique is best for every query
Steps to Optimizing Your Own Queries
1. Develop multiple ways to run it
2. Test and measure performance
3. Choose the one that is the fastest
Over-Optimization: Where more resources are used to
determine the best plan than is required to execute the
plan
General Query Optimization Techniques
•Use short table and field names instead of long names
•Use numeric primary keys instead of text
•Use primary keys or unique indexes when possible
•And many, many more….
Query Performance is Also Affected by
•Tables and/or queries that underlie it
•Complexity of the query
Methods the MS Access Query Optimizer Uses
•Display the minimum number of fields in a query
•Use non blank unique fields
•Careful use of Between and Equal to, rather than > or <
•Use numeric rather than text primary keys
•Use primary keys or unique indexes whenever possible
•Many more!
The ORACLE Query Optimizer
•Is more complex than the MS Access Query Optimizer
•Uses many different techniques to optimize queries since
no single technique is best for everything
•Ex. Full-Table Scan, B-Tree, Reverse-Key Index, etc
Example Query
Products table contains 77 rows
SQL:
SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE (UnitPrice > 12.5) AND (UnitPrice < 14)
Performs a full table scan
Optimize Query by Using an Index
SQL:
CREATE INDEX [IDX_UnitPrice] ON Products (UnitPrice)
SELECT ProductID, ProductName, UnitPrice
FROM PRODUCTS
WHERE UnitPrice BETWEEN 12.5 AND 14
This sorts the values of UnitPrice into a B-tree
The database quickly narrows the number of records it
must look at to a minimum
Summary
•The goal is to optimize the performance of the database
•There are many ways to execute a query
•Even the best of query optimizers may not always find the ideal
way to execute any given query
•This presentation is a small overview of the query optimization field
References
Advent-It.co.uk. 6 Nov 2005. Microsoft Access Query Optimization.
<http://www.advent-it.co.uk/queryop.html>
Jacobs, Ken. “Query Optimization.” ORACLE Magazine. Jul/Aug 2002
<http://www.oracle.com/technology/oramag/oracle/02-
jul/o42dba.html>
Ioannidis, Yannis. “Query Optimization.” ACM Computing Surveys 28:1
(March 1996): 121-3.
OdeToCode.com. 9 Nov 2005. SQL Server Indexes.
<http://odetocode.com/Articles/70.aspx>
Riccardi, Greg. Principles of Database Systems with Internet and Java
Applications. Boston: Addison Wesley, 2001.
SQLSummit.com. 6 Nov 2005. Query Optimization.
<http://www.sqlsummit.com/Optimization.htm#real%20world>
