Fastest Informix DBA
Contest II: How Did They Do It?
The winners of the latest contest tuned
a process from 40 hours to 1 minute.
Here are their techniques.
Over the summer, we ran another Fastest Informix DBA contest based on our very suc- cessful contest at the IIUG Informix Conference in April 2009. We enhanced the rules, made the benchmark process harder, and doubled the
size of the database: a customer billing process
that had lots of unnecessary SQL, missing
indexes, and an ONCONFIG file with some really
bad configuration settings. The billing process
took 40 hours to complete, and we challenged
the participants to make it run faster.
We had more than 70 participants, of whom
8 tuned the process to run in less than 6 minutes.
The fastest made it run in a little under 1 minute.
And to add to the excitement, only a 5-second
difference separated the top three places.
So, congratulations to the new winners!
The results were announced at the IBM
Information On Demand 2009 Global
Conference and updated in a Webcast on
November 16. I made a mistake and missed
the fastest entry, so we decided to give out
two grand prizes. To qualify for the grand
prize, the user must be a DBA employed at
a company using Informix internally, not a
consultant, and not an IBM employee.
� First Runner-up User DBA: Yunyao (Frank) Qu
� Second Runner-up User DBA: Tammy
Frankforter
� Fastest Youngest DBA: Riya Kariath
In this column, I want to highlight what
Tatiana, Eric, and Warren did to take a 40-hour
process and make it run in 1 minute. They all
highlight great examples of what a DBA must do
to produce fast code and fine-tuned databases.
Lester Knutsen
( lester@advancedatatools.com)
is president of Advanced
Data Tools Corporation, an IBM
Informix consulting and training
partner specializing in data
warehouse development,
database design, performance
tuning, and Informix training
and support. He is president
of the Washington, D.C. Area
Informix User Group, a
founding member of IIUG, an
IBM Gold Consultant, and an
IBM Data Champion.
� Grand Prize and Fastest Overall Time—
Fastest User DBA: Tatiana Saltykova
� Grand Prize—Fastest User DBA: Eric Rowell
� Fastest Consultant: Warren Donovan
� Fastest International DBA: Malte
Sukopp, Germany
� Runner-up Consultant: Jeff Filippi
Create the right indexes
The database had four tables, each with a
primary key, but no other indexes. One of the
tricks to database performance is identifying
the right number and placement of indexes.
Missing indexes will slow down reads, but too
many indexes will slow down inserts, updates
on indexed fields, and deletes.
The billing process was missing one key
index. After the bills were created, the customer
table was updated with a new balance, which
required the bills table and customer table to be
joined (see Figure 1). However, while the customer
table had an index on the customer number
field because it was the primary key, the bills
table did not. Without this index, the only way
to find a customer was to do a sequential scan
of more than 600,000 bills. Updating 101,000
customers would require 101,000 x 605,280 bills
or 61,133,280,000 scans of the bills table. Simply
adding this index on the customer number field
in the bills table reduced the processing time
from 40 hours to about 30 minutes.
