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


28 - Understand DEFAULT AUTOINCREMENT

When a column is defined with DEFAULT AUTOINCREMENT the actual maximum value is determined once by looking at the data as soon as that value is first needed after the database is started. This means the speed of the first INSERT after database startup depends on how quickly SQL Anywhere can find the current maximum value. And that is the reason the manual says, "For performance reasons, it is highly recommended that DEFAULT AUTOINCREMENT only be used with columns defined as a PRIMARY KEY or with a UNIQUE constraint, or columns that are the first column of an index. This will allow the maximum value determined at startup time to be found without scanning the entire table."

It is also the reason that SQL Anywhere's handling of gaps created in a DEFAULT AUTOINCREMENT sequence by DELETE statements differs depending on whether the database is stopped and started or not, but that is left as an exercise for the reader.

Figure 28 shows that the first INSERT after startup takes less than two tenths of a second when DEFAULT AUTOINCREMENT is defined for the first column in a primary key or index (Tests 1 and 3) but that it takes over six seconds when the DEFAULT AUTOINCREMENT column is not indexed at all (Test 2).

Figure 28 - Startup Performance Of DEFAULT AUTOINCREMENT 
CREATE TABLE auto1

   ( primary1 INTEGER NOT NULL DEFAULT AUTOINCREMENT,

     data1 INTEGER NOT NULL,

     PRIMARY KEY ( primary1 ) );


CREATE TABLE auto2

   ( primary2 INTEGER NOT NULL,

     data2 INTEGER NOT NULL DEFAULT AUTOINCREMENT,

     PRIMARY KEY ( primary2 ) );


CREATE TABLE auto3

   ( primary3 INTEGER NOT NULL,

     data3 INTEGER NOT NULL DEFAULT AUTOINCREMENT,

     PRIMARY KEY ( primary3 ) );


CREATE INDEX iauto3 ON auto3 ( data3 );


Test 1 - DEFAULT AUTOINCREMENT column is the primary key...

INSERT INTO auto1 ( data1 ) VALUES ( 99998 );

INSERT INTO auto1 ( data1 ) VALUES ( 99999 );

   first INSERT after startup: I/O 3, time 0.14 seconds

   second INSERT:              I/O 0, time 0.00 seconds


Test 2 - DEFAULT AUTOINCREMENT column is an ordinary column...

INSERT INTO auto2 ( primary2 ) VALUES ( 99998 );

INSERT INTO auto2 ( primary2 ) VALUES ( 99999 );

   first INSERT after startup: I/O 10, time 6.51 seconds

   second INSERT:              I/O 0,  time 0.10 seconds


Test 3 - DEFAULT AUTOINCREMENT column is indexed...

INSERT INTO auto3 ( primary3 ) VALUES ( 99998 );

INSERT INTO auto3 ( primary3 ) VALUES ( 99999 );

   first INSERT after startup: I/O 8, time 0.16 seconds

   second INSERT:              I/O 0, time 0.00 seconds

You may or may not care about how long the first INSERT takes. The overall performance of DEFAULT AUTOINCREMENT is excellent, and it outshines alternatives to this feature. In particular, Figure 28 shows that later INSERTs work with rocket speed even if the column isn't indexed.

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