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


15 - Consider An Explicit Primary Key Index

The special index you get automatically with every primary key and unique constraint might not help in certain situations. If this happens then maybe it's time to explicitly create a second index on the primary key.

An explicit index can help when the structure of the automatic index is all wrong; e.g., when the columns are placed in the wrong order for certain sorting or searching criteria or the default ASC sort sequence in a multi-column primary key doesn't help with a particular ORDER BY clause.

Figure 15A shows that a primary key ORDER BY clause containing both ASC and DESC keywords takes longer than clauses with two ASC or two DESC keywords unless a second index is created to match the ORDER BY sort order. In this case it doesn't matter whether the index is declared UNIQUE or not. SQL Anywhere will use the UNIQUE index if both exist, however, so it's still OK to follow the Rule of Thumb "If an index is truly unique then declare it so."

Figure 15A - ASC Assumed For Primary Key Index 
CREATE TABLE parent1

   ( primary1 INTEGER NOT NULL,

     primary2 INTEGER NOT NULL,

     data1 INTEGER NOT NULL,

     PRIMARY KEY ( primary1, primary2 ) );


BEGIN

DECLARE i INTEGER;

SET i = 1;

WHILE i <= 10000 LOOP

   INSERT INTO parent1 ( primary1, primary2, data1 )

      VALUES (i, i, i );

   SET i = i + 1;

END LOOP;

END


The same sort order for the primary key and the ORDER BY is OK:


   SELECT * FROM parent1 ORDER BY primary1 ASC, primary2 ASC;


   Estimated 10000 rows in query (I/O estimate 410)

   PLAN> parent1 (parent1)

   10000 record(s) selected -- actual I/O 970, time 5.70 seconds


Exactly the opposite sort order is also OK:


   SELECT * FROM parent1 ORDER BY primary1 DESC, primary2 DESC;


   Estimated 10000 rows in query (I/O estimate 410)

   PLAN> parent1 (parent1)

   10000 record(s) selected -- actual I/O 730, time 7.25 seconds


Mixed sort order with no explicit index is slow:


   SELECT * FROM parent1 ORDER BY primary1 ASC, primary2 DESC;


   Estimated 10000 rows in query (I/O estimate 12607)

   PLAN> TEMPORARY TABLE parent1 (seq)

   10000 record(s) selected -- actual I/O 3036, time 9.52 seconds


Mixed sort order with non-unique index is OK:


   CREATE INDEX iparent1 ON parent1 ( primary1 ASC, primary2 DESC );

   SELECT * FROM parent1 ORDER BY primary1 ASC, primary2 DESC;


   Estimated 10000 rows in query (I/O estimate 7281)

   PLAN> parent1 (iparent1)

   10000 record(s) selected -- actual I/O 733, time 5.64 seconds


Mixed sort order with unique index is also OK:


   CREATE UNIQUE INDEX uiparent1 ON parent1 ( primary1 ASC, primary2 DESC );

   SELECT * FROM parent1 ORDER BY primary1 ASC, primary2 DESC;


   Estimated 10000 rows in query (I/O estimate 4733)

   PLAN> parent1 (uiparent1)

   10000 record(s) selected -- actual I/O 731, time 5.26 seconds

Another situation where an explicit index might help is when a huge number of child table rows exist for each parent row. A large "fan-out" from primary-to-foreign key values can cause the special primary-and-foreign-key index to grow fat and slow. For example, if there are 1000 rows in a child table for every parent row then a query based on the primary key must deal with 1000 index entries for every different primary key value.

Figure 15B shows that a primary key query can take twice as long when the fan-out is 1000 to 1 unless an explicit index is created. In this case the index must be declared as UNIQUE or SQL Anywhere will ignore it in favor of the slower but unique primary key index. This proves the Rule of Thumb "When an index is truly unique then declare it so."

Figure 15B - Foreign Key Fan-Out 
CREATE TABLE parent2

   ( primary2 INTEGER NOT NULL,

     data2 INTEGER NOT NULL,

     PRIMARY KEY ( primary2 ) );


SELECT * FROM parent2 WHERE primary2 = 44;


Select before child2 exists - fast:


   Estimated 1 rows in query (I/O estimate 2)

   PLAN> parent2 (parent2)

   1 record(s) selected -- actual I/O 3, time 0.4 seconds


CREATE TABLE child2

   ( primary2 INTEGER NOT NULL,

     primary3 INTEGER NOT NULL,

     data2 INTEGER NOT NULL,

     PRIMARY KEY ( primary2, primary3 ),

     FOREIGN KEY ( primary2 )

        REFERENCES parent2 ( primary2 ) );


SELECT * FROM parent2 WHERE primary2 = 44;


Select when child2 exists but is still empty - fast:


   Estimated 1 rows in query (I/O estimate 2)

   PLAN> parent2 (parent2)

   1 record(s) selected -- actual I/O 3, time 0.4 seconds


BEGIN

DECLARE i INTEGER;

DECLARE j INTEGER;

SET i = 1;

WHILE i <= 100 LOOP

   SET j = 1;

   WHILE j <= 100 LOOP

      INSERT INTO child2

         ( primary2, primary3, data2 )

         VALUES ( i, j, j );

      SET j = j + 1;

   END LOOP;

   SET i = i + 1;

   COMMIT;

END LOOP;

END


SELECT * FROM parent2 WHERE primary2 = 44;


Select when child2 has 1000 rows for every row in parent2 - slow :


   Estimated 1 rows in query (I/O estimate 2)

   PLAN> parent2 (parent2)

   1 record(s) selected -- actual I/O 5, time 0.10 seconds


CREATE UNIQUE INDEX iparent2 ON parent2 ( primary2 );

SELECT * FROM parent2 WHERE primary2 = 44;


Select after an explicit unique index is created - fast:


   Estimated 1 rows in query (I/O estimate 3)

   PLAN> parent2 (iparent2)

   1 record(s) selected -- actual I/O 3, time 0.5 seconds

The performance improvement might not be worth the effort of creating a second primary key index. If your database is lightly loaded, if you're using 4K pages or if you're running with a large cache you might not notice any problem at all. It's important to keep in mind the Rule of Thumb: "If it's fast enough then don't make it faster" or "If it ain't broke don't fix it."

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