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


23 - Watch Out For Wide Indexes

The SQL Anywhere manual says "About the first 10 bytes of data for each index entry are stored in the index pages." What this means is that indexes made up of long columns and/or many columns can sometimes be slower than narrow indexes. This performance degradation seems to happen if SQL Anywhere must look beyond the 10th character when comparing index values.

Figure 23A demonstrates the performance difference between narrow and wide index entries. Half of the entries are much shorter than 10 bytes while the other half are longer than 10 bytes with the same data in the first 10 bytes of all of these entries. The two SELECTs show that searching on a wide index value can double the number of I/O operations and greatly increase the time required.

Figure 23A - Narrow Versus Wide Indexes 
CREATE TABLE indexed1

   ( primary1 INTEGER NOT NULL,

     search1 CHAR(100) NOT NULL,

     PRIMARY KEY ( primary1) );


BEGIN

DECLARE i INTEGER;

SET i = 1;

WHILE i <= 20000 LOOP

   INSERT INTO indexed1 ( primary1, search1 )

      VALUES ( i, String ( i ) );

   INSERT INTO indexed1 ( primary1, search1 )

      VALUES ( i + 1, 'aaaaaaaaaa' + String ( i ) );

   SET i = i + 2;

END LOOP;

END


CREATE INDEX isearch1 ON indexed1 ( search1 );



Test 1 - Narrow index entry search...

SELECT * FROM indexed1 WHERE search1 = '1001';

   PLAN> indexed1 (isearch1)

   Estimate - rows 1, I/O 2

   Actual   - rows 1, I/O 5, time 0.10 seconds


Test 2 - Wide index entry search...

SELECT * FROM indexed1 WHERE search1 = 'aaaaaaaaaa1001';

   PLAN> indexed1 (isearch1)

   Estimate - rows 1, I/O 2

   Actual   - rows 1, I/O 11, time 0.16 seconds

In the real world you often need to create indexes involving several long columns, making it impossible to avoid wide indexes all the time. The good news is that it's the data that counts, not the declared size of the columns, and wide indexes aren't necessarily a bad thing. Figure 23B shows how you can determine if there actually are any problems with wide index comparisons by repeatedly displaying the 'FullCompare' property.

Figure 23B - Display FullCompare 
Test 1 - Narrow index entry search...

SELECT * FROM indexed1 WHERE search1 = '1001';

SELECT Property ( 'FullCompare' );


Test 2 - Wide index entry search...

SELECT * FROM indexed1

 WHERE search1 = 'aaaaaaaaaa1001';

SELECT Property ( 'FullCompare' );

FullCompare is defined as "the number of comparisons beyond the hash value in an index that have been performed." When the first test in Figure 23B is repeatedly run the value of FullCompare changes very slowly: 60, 63, 66, 69, and so on. In the second test FullCompare increases dramatically with each run: 90, 111, 132, 153, etc. Figure 23C shows what it looks like in ISQL.

Figure 23C - Watch For Rapid FullCompare Changes With ISQL

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