Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.


[Home] [Table of Contents] [Previous Section] [Next Section]

Breck Carter
Last modified: February 12, 1998
mail to: bcarter@bcarter.com


Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques


21 - Be Reasonable With The Indexes

The Rule of Thumb says "Don't create more than half a dozen indexes for a single table" but what if you really, really need indexes on every column in a huge table? Well, if it's a read-only table then go ahead, all you care about is optimizing queries. But if the table is updated as well as queried then you must understand that extra indexes will slow things down. That's when this Rule of Thumb kicks in: "If you create an index make sure you need it, and then make sure it helps where it should and doesn't hurt too much elsewhere."

Figure 21 shows that you do pay a price for multiple indexes but after the first few indexes the effect is more or less linear rather than exponential. In other words, you pay for what you get.

Figure 21 - UPDATE Slowed by Multiple Indexes 
CREATE TABLE multi1

   ( primary1 INTEGER NOT NULL,

     s01 INTEGER NOT NULL, s02 INTEGER NOT NULL,

     s03 INTEGER NOT NULL, s04 INTEGER NOT NULL,

     ...

     s17 INTEGER NOT NULL, s18 INTEGER NOT NULL,

     s19 INTEGER NOT NULL, s20 INTEGER NOT NULL,

     PRIMARY KEY ( primary1) );


BEGIN

DECLARE i INTEGER;

SET i = 1;

WHILE i <= 1000 LOOP

   INSERT INTO multi1

      ( primary1, s01, s02, s03, s04, s05, s06, s07, s08, s09, s10,

                  s11, s12, s13, s14, s15, s16, s17, s18, s19, s20 )

      VALUES ( i, i, i, i, i, i, i, i, i, i, i,

                  i, i, i, i, i, i, i, i, i, i, );

   SET i = i + 1;

END LOOP;

END


CREATE INDEX is01 ON multi1 ( s01 ); CREATE INDEX is02 ON multi1 ( s02 );

CREATE INDEX is03 ON multi1 ( s03 ); CREATE INDEX is04 ON multi1 ( s04 );

...

CREATE INDEX is17 ON multi1 ( s17 ); CREATE INDEX is18 ON multi1 ( s18 );

CREATE INDEX is19 ON multi1 ( s19 ); CREATE INDEX is20 ON multi1 ( s20 );


UPDATE multi1 SET

   s01=s01+1, s02=s02+1, s03=s03+1, s04=s04+1, s05=s05+1,

   s06=s06+1, s07=s07+1, s08=s08+1, s09=s09+1, s10=s10+1,

   s11=s11+1, s12=s12+1, s13=s13+1, s14=s14+1, s15=s15+1,

   s16=s16+1, s17=s17+1, s18=s18+1, s19=s19+1, s20=s20+1;


UPDATE All 20 Columns                        I/O  Seconds

---------------------                       ----  -------

1  No extra indexes, just the primary key:  1436    9.35

2  After 2 indexes created:                 1666   11.62

3  After 6 indexes created:                 2126   15.55

4  After all 20 indexes created:            4302   54.46

[Home] [Table of Contents] [Previous Section] [Next Section]