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


22 - Pick The Right Time To Create An Index

In some situations it might be better to create an index than to maintain one. For example, if you're planning to load a lot of data then it might be faster to create the indexes after the data is loaded. This applies to primary and foreign keys as well because indexes are automatically created for them.

Another example is a big expensive query that could benefit from an index but that same index will cause problem with updates. If the query is only used occasionally then it might pay to create the index every time and drop it after the query is finished.

Figure 22 compares the time spent to load one table from another (INSERT SELECT) before and after an index is created. If the database cache is small then it's faster to load the data first and then create the index. On the other hand if the database cache is larger than the amount of data being processed then it's faster to load the data while the index exists.

Figure 22 - CREATE INDEX Before And After INSERT 
CREATE TABLE load1

   ( primary1 INTEGER NOT NULL,

     search1 CHAR(100) NOT NULL,

     PRIMARY KEY ( primary1) );


CREATE TABLE load2

   ( primary1 INTEGER NOT NULL,

     search1 CHAR(100) NOT NULL,

     PRIMARY KEY ( primary1) );


BEGIN

DECLARE i INTEGER;

SET i = 1;

WHILE i <= 5000 LOOP

   INSERT INTO load1 ( primary1, search1 )

      VALUES ( i, Repeat ( String ( i ), 10 ) );

   SET i = i + 1;

END LOOP;

END


Test 1 - CREATE INDEX then INSERT...

   CREATE INDEX iload2 ON load2 ( search1 );

   INSERT load2 SELECT * FROM load1;


Test 2 - INSERT then CREATE INDEX...

   INSERT load2 SELECT * FROM load1;

   CREATE INDEX iload2 ON load2 ( search1 );


Test 1 time in seconds   Test 2 time in seconds

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

        small   large            small   large

        cache   cache            cache   cache

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

CREATE   0.27    0.26    INSERT  34.00    7.10

INSERT  46.13    6.77    CREATE   6.72    0.50

        =====   =====            =====   =====

Total   46.40    7.03    Total   40.72    7.60

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