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


11 - Always Define A Primary Key

If a primary key, a UNIQUE NOT NULL index or a UNIQUE constraint (which does not allow nulls) do not exist for a table then UPDATE and DELETE statements must log all columns instead of just the key and updated columns. This can slow things down and inflate the size of the log file.

Even if you don't need a primary key for reasons of referential integrity or query optimization it still might be a good idea to define one. Figure 11 shows that an UPDATE statement runs faster if the table has a primary key, regardless of the database cache size.

Figure 11 - Primary Key Speeds Update 
CREATE TABLE unkeyed1

   ( data1 INTEGER NOT NULL,

     data2 char(1000) NOT NULL );


CREATE TABLE keyed1

   ( primary1 INTEGER NOT NULL,

     data1 INTEGER NOT NULL,

     data2 char(1000) NOT NULL,

     PRIMARY KEY ( primary1) );


BEGIN

DECLARE i INTEGER;

SET i = 1;

WHILE i <= 1000 LOOP

   INSERT INTO unkeyed1

      ( data1, data2 )

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

   INSERT INTO keyed1

      ( primary1, data1, data2 )

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

   SET i = i + 1;

END LOOP;

END


Test 1 - No primary key defined...

UPDATE unkeyed1 SET data1 = data1 + 1;

   small cache: I/O 5424, time 38.72 seconds

   large cache: I/O 3221, time 22.40 seconds


Test 2 - Primary key exists...

UPDATE keyed1 SET data1 = data1 + 1;

   small cache: I/O 3061, time 25.84 seconds

   large cache: I/O 1988, time 13.41 seconds

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