3150 Optimization II: Optimizing InterBase SQL and Metadata

1
3150Optimization II Optimizing InterBase SQL
and Metadata

• Craig Stuntz
• TeamB / Senior Developer
• Vertex Systems Corporation

2
About this Course

• How to design DB metadata for optimum
  performance.
• How to write optimal SQL statements.
• Will not cover optimizing applications (thats
  course 3154)
• Will not cover optimizing server configurations
  (thats course 6104 CD only)

3
The Golden Rule of Optimization

• Optimize the slow stuff first.
• In other words, it is important to optimize use
  of your own time as well as your application.

4
Corollary to the Golden Rule

• Always develop database applications using a
  database populated with real data.

• Use real customer data, if available.
• Scramble to protect customer privacy.
• Use a test data generator if real data is not
  available.
• Avoid using a local server.

5
Fundamentals

• Understand query execution
• Good metadata design
• Good SQL design
• A well-maintained database

6
Some Useful Tools

• IBConsole features.
• Query analyzer.
• Standalone performance monitoring.
• Gstat.

7
Understand Query Execution

• Client view Prepare, Execute, Fetch
• Server view Parse, Optimize, Execute, wait for
  Fetch.
• BLR stored for some objects, optimization PLAN
  never stored.
• Result set size
• Records read vs. records returned
• Indexed vs. sequential table reads

8
Analyzing Statement Execution

• PLAN statement easier to understand when in
  graphical form.
• Fetch statistics.
• Performance Monitoring.

9
Explicit PLAN Statements

• Can specify a PLAN in a SELECT statement.
• Not a hint, overrides optimizer altogether.
• Doesnt work with indices generated for
  primary/foreign keys.
• You can hint to the optimizer by using some SQL
  tricks.
• Avoid whenever possible.

10
Understanding Indices

• UNIQUE vs. non-unique.
• ASCENDING vs. DESCENDING.
• Single-column vs. compound.
• Can be combined during execution.
• Selectivity.
• Generally not useful for ORDER BY.
• Prefix compression.
• Slower DML.

11
Compound Indices

• No execution-time penalty for combining columns.
• Useless if columns in wrong order in index.
• Can confuse optimizer prior to InterBase 7.1
  Service Pack 1

12
Indices and JOINs

• JOIN vs. MERGE in PLAN.
• Cannot use an index to perform a JOIN with the
  result set of a SELECTable stored procedure.
• Can use an index to perform a JOIN with the
  result set of a VIEW, but it can get complicated.
• INNER vs. OUTER JOINs.

13
Indices and WHERE Clauses

• Look for NATURAL vs. INDEX in PLAN (but PLAN
  doesnt show everything.
• Consider real selectivity.
• LIKE, STARTING WITH, and parameters.
• SQL functions and UDFs.
• How to defeat index use.

14
Indices and ORDER BY

• Total time to execute a query and fetch a result
  set is usually slower when an index is used to
  optimize the ORDER BY clause. Really!
• On the other hand, the execution (without
  fetching) is faster with the index.
• How to defeat index use for ORDER BY.

15
Statements with Multiple PLANs

• Statements with subqueries and UNIONs will have
  multiple PLANs.
• You can optimize each SELECT separately, but
  watch out for correlated subqueries.
• Subqueries with lists of constants.
• Understand the difference between UNION and UNION
  ALL.

16
Understanding the RDBDB_KEY

• Mostly useful in stored procedures.
• Faster than the primary key for locating a single
  record.
• Especially useful for UPDATE statements which
  would affect many rows replace with a FOR
  SELECT and an inner UPDATE for each row.

17
Database Maintenance

• Index statistics.
• Rebalancing indices.
• Data page fill.
• Backup and restore.

18
Questions?
19
Thank You
2124 InterBase Performance Monitoring Vision and
Control Please fill out the speaker
evaluation You can contact me further by posting
questions on the Borland newsgroups