//|||| \\\       //  || \\  \\      //   || \\     //    ||   \\    //     ||     \\   ////////||      \\  //       || \\  \\\ //       ||||  \\\

ABLE Information Services Database Index Design

Poor database performance costs money in so many ways:
   · capacity planners push for bigger CPU's and software pricing jumps;
   · customers are annoyed and sales are lost;
   · call-center staffers are frustrated and show low productivity;
   · sales and accounting staff can't get information instantly;
   · managers see budgets increase and missions delayed;
   · database specialists are overloaded with performance problems;
   · and poorly trained programmers can't help: they usually make the problems worse.

Charge-backs for CPU time  may also be an issue.
One DBA saved his firm $67,000 in one year by applying these techniques.

Not just your DBA's need to worry about performance.
Your programmers should be saving you money by understanding how indexes and optimizers
work in your database.
Programmers shouldn't cost you money by adding to production problems -- they slow your customers
and staff. Those problems need costly experts to resolve, usually experts who are overworked!

The small incremental cost of a new index or an improved index design can save you money
over and over:  for each click, each transaction, each batch job!
Each time someone calls, visits your web site or interacts with your organization and its database
. . . thousands of times an hour. . .millions of times each month. . .you'll save money.

And this is regardless of which database you use:  Oracle,  DB2 for z/OS,  
DB2 for Unix and Linux,  Microsoft SQL Server,  Sybase and  Informix.

ABLE's 2-day class fee is just US $621 each for two or more
students [regular price US $690 ]. It's a great investment with a high R.O.I.
For example, if you have just one big query that runs twice an hour, day and night,
then perhaps our techniques can save you one half second of CPU cost
each time it runs. When you include software costs, CPU lease and
operating costs, the hourly CPU rate is about US $500.
     So that half-second saved means you'd save US $100 each month!
[That doesn't include the effects on other programs: they run faster,
giving staff users better productivity and web customers faster click-throughs.]

Here's a stunning example from Europe:
     a large organization had 2 Database Specialists, 20 application DBA's
     and 200 developers. After all developers received training in the techniques taught in this course, they
     made measurements. While development time per program increased about 5%, say from 19 to 20 hours,
     production tuning costs were reduced 95%. Those fast-running programs kept the whole system humming!

Send one lead programmer and one application DBA to try out our 2-day class,
TL120 Cost-Saving Database Index Design. We think you'll want your whole staff trained at a highly discounted
cost per student!  
                                           See our class schedules          Consider in-house classes

One of our students [ a DBA ] wrote:
     "I'm exploiting some insight I got from taking Larry Kintisch's class on Index Design -- it looks like
     we'll really be able to save lots of time on one of our highest-frequency transactions. ...
     This statement runs 70,000-or-so times a week, a couple of seconds every time, and I think
     we're going to cut about 9 CPU hours out of the total weekly runtime, with corresponding
     improvements in the response times."

From course author and expert database consultant Tapio Lahdenmäki:

This course is directly based on the text book written by my co-authors and me
and revised in 2005.  I feel that the topic of our book, "Relational Database
Index Design and the Optimizers," is quite important. Most database specialists
-- and sophisticated application developers as well -- will benefit from our
Index Design techniques, taught in our class, for the following reasons.

1. A Common Performance Problem.
     Inadequate indexing is the most common reason for performance problems with database
applications. Other common reasons are poor table design, an imperfect SQL optimizer (does not
always select the best access path -- needs help) and long lock waits. The first two are related
to index design (and discussed in the book and the course). An example from the course shows
a simple query that takes 10,000 times longer if the correct index is not available or is not used.

2. RDBMS Independent.
     Index Design is essentially independent of the database management system
(Oracle, Microsoft SQL Server, and IBM DB2...). They all use B-trees, a structure
that was already common in the nineteen-sixties, before relational databases.

3. Not Well Described.
     Database product manuals and books cover indexing very briefly -- perhaps because
the index design issues are not product specific.

4. Huge Number of Choices.
      Index design is comparable to playing chess: the search space is enormous.
However, pragmatic methods (covered in the book and the course) have evolved over the years.
Useful index design tools are in the market, but their proposed indexes require a
review by a database specialist. Dozens of companies and agencies have used
the methods recommended in the book and the course with good results.

5. DASD Slows Transactions.
      Good index design is crucial for the performance of any operational system whose database
does not fit in central storage. Today, it is economically feasible to have a few gigabytes of database
in central storage. Most databases for medium-size companies contain hundreds of gigabytes.
It’s unlikely that in-storage enterprise databases will be common before 2010.

6. Index Improvement R.O.I.
     While very large central storage (RAM) will eventually make index design less critical, the current
price levels of RAM and disk space make index improvements rewarding. It is now often feasible to
reduce response time and processor cost by adding indexes or by adding columns to existing indexes.
This trade-off is not recommended by most of the older database books and courses. The benefits of
faster transactions are reduced server and DASD loading, lower CPU charges, better
customer service and higher employee productivity.

7. Operational Improvement.
     Those who build new systems use table design methods. Index design methods are imperative
for anybody who wants to improve the performance of an operational system. Unlike table design,
index design can be improved relatively easily while the application is already in production, often
without changing program code.

8. Spreading the Scarce Skills.
     Many companies have a limited number of highly skilled database specialists. Most specialists
don’t have time to do thousands of adequate index designs. A new trend moves responsibility to the
application developers (or application database administrators) for predicting poor query performance
and improved index requirements. This trend both reduces the quantity of poorly performing queries,
and reduces the problem workload of the specialists. However, to accomplish this your application
developers need to be trained. They are in the training audience our book and course addresses.

About the author ….    See our class schedules….    Consider in-house training       [v080311]