Tip 77 - Overview

     SELECT * FROM child1, parent1

      WHERE child1.primary1 = parent1.primary1
        AND parent1.primary1 = 44

     PLAN> parent1 (parent1), child1 (parent1_child1)

     table-name (seq)            table scan (bad)
     table-name (table-name)     primary key index (good)
     table-name (role-name)      foreign key index (good)
     table-name (index-name)     index (good)
     TEMPORARY TABLE table-name  temp table (bad)
     table-name (table-name      constraint (good)
          UNIQUE (column-name))
     table-name (seq):           nested, UNION'ed queries
          table-name (seq)

4 - Run ISQL In Batch Mode

ISQL -c "UID=DBA;PWD=SQL" c:\test\test.sql Input c:\test\test.sql

     CREATE INDEX iquery1 ON query1
        ( search1, search2 );

     SELECT * FROM query1
      WHERE search1 = 44 >&c:\test\isql.txt;

     SELECT * FROM query1
      WHERE search2 = 44 >>&c:\test\isql.txt;

Output >&c:\test\isql.txt

     % Executing command:
     % SELECT * FROM query1
     %     WHERE search1 = 44;
     % Estimated 1 rows in query
           (I/O estimate 2)
     % PLAN> query1 (iquery1)
     44,44,44,44
     % 1 record(s) selected -- actual I/O 0
     % select time including I/O 0.0 seconds

     % Executing command:
     % SELECT * FROM query1
     %     WHERE search2 = 44;
     % Estimated 3 rows in query
           (I/O estimate 404)
     % PLAN> query1 (seq)
     44,44,44,44
     % 1 record(s) selected -- actual I/O 1
     % select time including I/O 0.8 seconds

5 - The Prime Directive: Increase The Cache

Suggestion Number 1: "Make the cache bigger."

Number 2: "Buy more RAM if you have to."

Rules of Thumb:

"Make the cache as big as possible."
"The bigger it is the faster it will run."
"10 to 15% as big as the database."

The Effect Of A Larger Cache

     DBSRV50 -c 400K -xNamedPipes c:\test\tiny.db
     INSERT INTO test2 SELECT * FROM test1;
     I/O 9504, time 82.74 seconds

     DBSRV50 -c 4096K -xNamedPipes c:\test\tiny.db
     INSERT INTO test2 SELECT * FROM test1;
     I/O 682, time 2.4 seconds

6 - Use A Large Page Size

Reduced number of index levels
Use DBINFO
Use -gp

          DBINIT -p 4096 c:\test\big.db
          DBINIT -p 2048 c:\test\small.db
          DBSRV50 -xNamedPipes -gp 4096 -nTesting c:\test\small.db -nSmall

7 - Use A Log File

Improves inserts, updates, deletes
Fewer Checkpoints
Use DBLOG
Beware RTDSK50
10+ seconds to less than 1...

          FOR ll_primary1 = 1 TO 100
             UPDATE test1 SET search1 = search1 + 1
              WHERE primary1 = :ll_primary1 USING SQLCA;
             IF SQLCA.SQLCode <> 0 THEN... error
             COMMIT USING SQLCA;
             IF SQLCA.SQLCode <> 0 THEN... error
          NEXT

8 - Think Sets

Shorter, Faster, Easier

Two tenths of a second...

     UPDATE test1
        SET salary = salary + 10000
      WHERE dept_id = 5 USING SQLCA;
     COMMIT...

8 seconds...

     DECLARE CURSOR FOR SELECT
     OPEN
     FETCH INTO
     DO WHILE
        UPDATE WHERE CURRENT OF
        FETCH INTO
     LOOP
     COMMIT

9 - Create Indexes

Faster WHERE
Faster ORDER BY, GROUP BY
Slower INSERT, DELETE, UPDATE
Rules of Thumb...

Almost every table can benefit from an index
Always define a primary key
The primary key is almost never the only column that could benefit from an index
Don't create indexes on primary, foreign keys
Don't create more than half a dozen indexes for a single table
If an index is truly unique then declare it so

10 - Understand Index Column Placement

     CREATE INDEX iquery1 ON query1( search1, search2 );

     SELECT * FROM query1 WHERE search1 = 44;
        Estimated 1 rows in query (I/O estimate 2)
        PLAN> query1 (iquery1)

     SELECT * FROM query1 WHERE search1 = 44
                            AND search2 = 44;
        Estimated 1 rows in query (I/O estimate 2)
        PLAN> query1 (iquery1)

     SELECT * FROM query1 WHERE search2 = 44;
        Estimated 3 rows in query (I/O estimate 220)
        PLAN> query1 (seq)

     CREATE INDEX iquery2 ON query1 ( search2 );

     SELECT * FROM query1 WHERE search2 = 44;
        Estimated 1 rows in query (I/O estimate 2)
        PLAN> query1 (iquery2)

11 - Always Define A Primary Key

(or a UNIQUE NOT NULL index)
(or a UNIQUE constraint)

Otherwise...

UPDATEs log all columns
Same for DELETEs
Fat log
Slow app

12 - Trust Your Keys

Special index for each primary key

Faster key changes
Faster RI checking
Faster key joins
Faster key lookups

Includes foreign key values
Same for UNIQUE constraints
Don't index primary, foreign keys
Beware overzealous tools

13 - Understand Primary Key Column Placement

PRIMARY KEY clause order?

Next contestant please!

CREATE TABLE order?

Winner! Winner!

          CREATE TABLE parent1
             ( primary2 INTEGER NOT NULL,
               primary1 INTEGER NOT NULL,
               data1 INTEGER NOT NULL,
               PRIMARY KEY ( primary1, primary2 ) );

          SELECT * FROM parent1 WHERE primary1 = 44;
             Estimated 3 rows in query (I/O estimate 182)
             PLAN> parent1 (seq)

          SELECT * FROM parent1 WHERE primary2 = 44;
             Estimated 8 rows in query (I/O estimate 2)
             PLAN> parent1 (parent1)

14 - Understand ASC And DESC

Will this index help an ORDER BY?

Single column: Always
Multiple columns: It Depends
Exact same direction OK
Exact opposite direction OK

15 - Consider An Explicit Primary Key Index

When the column order doesn't help
When the ASC default doesn't help
When there is a large fan-out; for example:

20 rows in primary table
92 foreign keys
3 foreign tables > 500,000 rows
... 15 seconds row-to-row!
Fixed in ASA 6

Explicit index must be UNIQUE
Rule of Thumb: If it's fast enough then don't make it faster

16 - Use Indexes for Max() and Min()

     CREATE INDEX iquery2 ON query1 ( search2 );
     SELECT Max ( search1 ) FROM query1;
        PLAN> query1 (seq)
        Estimated - rows 1, I/O 626
        Actual    - rows 1, I/O 347, time 1.91 seconds
     SELECT Max ( search2 ) FROM query1;
        PLAN> query1 (iquery2)
        Estimated - rows 1, I/O 7222
        Actual    - rows 1, I/O 6, time 0.10 seconds

Your mileage may vary

17 - Use LIKE 'xx%'

Index used for LIKE 'xx%'
But not for...

LIKE '%xx'
Right()
Substr()
...or even Left()

          SELECT * FROM query1 WHERE search1 LIKE '5.%';
             Estimated 1 rows in query ( I/O estimate 2)
             PLAN> query1 (iquery1)
          SELECT * FROM query1 WHERE Left ( search1, 2 ) = '5.';
             Estimated 500 rows in query ( I/O estimate 200)
             PLAN> query1 (seq)

18 - Use EXISTS Instead Of COUNT(*)

"Does this exist?"

     SELECT COUNT(*) INTO :ll_count FROM xref_info
      WHERE short_event LIKE '%open%'

     SELECT IF EXISTS (
               SELECT short_event
                 FROM xref_info
                WHERE short_event like '%open%' )
            THEN 1
            ELSE 0
            ENDIF
       INTO :ll_yes_no FROM SYS.DUMMY

"Are There Any Rows At All?"

     SELECT COUNT(*) FROM test1;
        PLAN> test1 (seq)
        I/O 256, time 0.87 seconds

     SELECT IF EXISTS ( SELECT * FROM test1 )
            THEN 1
            ELSE 0
            ENDIF;
        PLAN> DUMMY (seq): test1 (seq)
        I/O 1, time 0.5 seconds

19 - Use UNION ALL

     SELECT * FROM part1
     UNION
     SELECT * FROM part2;
        PLAN> TEMPORARY TABLE part1 (seq): part2 (seq)
                Estim. - rows 2000, I/O 2695
        Actual - rows 2000, I/O 401, time 1.80 seconds

     SELECT * FROM part1
     UNION ALL
     SELECT * FROM part2;
        PLAN> part1 (seq): part2 (seq)
            Estim. - rows 2000, I/O 94
        Actual - rows 2000, I/O 50, time 1.10 seconds

20 - Replace OR With UNION

OR may do slow sequential scan

     SELECT * FROM indexed1
      WHERE search1 = 4444
         OR search2 = 5555;
     PLAN> indexed1 (seq)
     Estimated: I/O 626
     Actual:    I/O 358, time 2.95 seconds

UNION may use multiple indexes

     SELECT * FROM indexed1
         WHERE search1 = 4444
        UNION
        SELECT * FROM indexed1
         WHERE search2 = 5555;
     PLAN> TEMPORARY TABLE indexed1 (isearch1):
                           indexed1 (isearch2)
     Estimated: I/O 9
     Actual:    I/O 10, time 0.21 seconds

21 - Be Reasonable With The Indexes

Rule of Thumb 1: "Don't create more than half a dozen indexes for a single table"
If it's read-only then go wild
Extra indexes slow some updates
Rule of Thumb 2: "If you create an index make sure you need it, and then make sure it helps where it should and doesn't hurt too much elsewhere."

UPDATE Slowed by Multiple Indexes

     CREATE INDEX is01 ON multi1 ( s01 );
     ...
     CREATE INDEX is20 ON multi1 ( s20 );

     UPDATE multi1 SET
        s01=s01+1, s02=s02+1, s03=s03+1,
        s04=s04+1, s05=s05+1, s06=s06+1,
        s07=s07+1, s08=s08+1, s09=s09+1,
        s10=s10+1, s11=s11+1, s12=s12+1,
        s13=s13+1, s14=s14+1, s15=s15+1,
        s16=s16+1, s17=s17+1, s18=s18+1,
        s19=s19+1, s20=s20+1;

     UPDATE 20 Columns          I/O  Seconds
     -----------------         ----  -------
     Just a primary key        1436    9.35
     After 2 indexes created   1666   11.62
     After 6 indexes created   2126   15.55
     After 20 indexes created  4302   54.46

22 - Pick The Right Time To Create An Index

Before or after loading data

Small cache: load first, index later
Big cache: index first, load later

Create and drop for one-time query

Avoid update performance hit

23 - Watch Out For Wide Indexes

"About the first 10 bytes of data for each index entry are stored in the index pages."
Long, many columns can be slower
It's the data that counts

Not the declared size
Does it look beyond the 10th char?

Watch for rapid FullCompare changes

SELECT Property ( 'FullCompare' );
"number of comparisons beyond the hash value in an index that have been performed"

24 - Use The SQL Central Performance Monitor

25 - Use The NT Performance Monitor

26 - Diagnose Problems With Runtime Properties

     SELECT connection_property ( 'CommLink' ) as CommLink,
            db_property ( 'ConnCount' ) as ConnCount,
            property ( 'DiskRead' ) as DiskRead,
            property ( 'DiskWrite' ) as DiskWrite
       INTO :ls_CommLink,
            :ll_ConnCount,
            :ll_DiskRead,
            :ll_DiskWrite
       FROM sys.dummy USING SQLCA;
     IF SQLCA.SQLCode <> 0 THEN ...error

     MessageBox ( "Properties", &
        "CommLink:~t" + ls_CommLink + "~r~n" &
      + "ConnCount:~t" + string ( ll_ConnCount ) + "~r~n" &
      + "DiskRead:~t" + string ( ll_DiskRead ) + "~r~n" &
      + "DiskWrite:~t" + string ( ll_DiskWrite ) )

27 - Find Blocked Connections With System Functions

xx = Next_Connection ( NULL )
yy = Connection_Property ( 'BlockedOn', xx )
uxx = Connection_Property ( 'UserID', xx )
uyy = Connection_Property ( 'UserID', yy ).
xx = Next_Connection ( xx )
Repeat from Step 2 until xx is empty
Display blocked connections if any

28 - Understand DEFAULT AUTOINCREMENT

Actual maximum determined at startup
Affects speed of the first INSERT
"Highly recommended..."

PRIMARY KEY
UNIQUE constraint
First column of index

Also explains DELETE gaps
Overall excellent performance

29 - Watch Out For Correlated Subqueries

Nested select refers to outer column
Can be truly evil - 65 seconds, rising

   SELECT primary1, type1, quantity1,
          ( SELECT Avg ( quantity1 )
              FROM test1 B
             WHERE B.type1 = A.type1 )
     FROM test1 A
    WHERE A.primary1 <= 10;

Temporary table: 3 seconds, scalable

   SELECT type1,
          Avg ( quantity1 ) as avg_quantity1
     INTO #test1 FROM test1 GROUP BY type1;

   SELECT A.primary1, A.type1, A.quantity1,
          B.avg_quantity1
     FROM test1 A, #test1 B
    WHERE A.primary1 <= 10
      AND A.type1 = B.type1;

Correlated subqueries aren't always bad

"Find all the parents with no children"

   SELECT COUNT(*)
     FROM parent1
    WHERE NOT EXISTS
             ( SELECT *
                 FROM child1
                WHERE parent1.primary1
                    = child1.primary1 );

   SELECT COUNT(*)
     FROM parent1 LEFT OUTER JOIN child1
       ON parent1.primary1 = child1.primary1
    WHERE IfNull ( child1.primary1, 1, 0 ) = 1;

Be absolutely sure there is a problem
Run some tests
Don't rely on the PLAN

30 - Beware Of Slow DDL

Might be held, might hold others

ALTER TABLE
CREATE INDEX
DROP DBSPACE
DROP INDEX
DROP TABLE

For example...

Create/drop temporary index
Change RI
Add a column

CREATE TABLE is OK

31 - Deal With Disk Fragmentation

Norton Speed Disk
Use an empty partition
ALTER DBSPACE
DB and LOG are both affected

32 - Use Stored Procedures And Triggers

Procedure language may be faster
Less app-to-DBMS communication
Procedures kept in virtual memory - may be used concurrently
Don't go crazy

33 - Limit Result Sets With SET ROWCOUNT

It's a Transact SQL thing

SET ROWCOUNT <max-row-count>
Test new code
Prevent runaway queries
Remove with SET ROWCOUNT 0

Limits rows fetched for any cursor
Affects DataWindow Retrieve()

          ls_SQL = "SET ROWCOUNT 100"
          EXECUTE IMMEDIATE :ls_SQL
            USING SQLCA;
          IF SQLCA.SQLCode <> 0 THEN...error

34 - Interrupt Processing With SET ROWCOUNT

Affects set UPDATEs, DELETEs
Execute COMMITs at regular intervals
"Change one column value to another"

SET ROWCOUNT 100;
SET not_done = 1;
WHILE not_done = 1 LOOP
   UPDATE large1 SET code1 = 15
    WHERE code1 = 5;
   COMMIT;
   SELECT @@ROWCOUNT INTO update_count;
   IF update_count = 0 THEN
      SET not_done = 0;
   END IF;
END LOOP;

Must prevent multiple updates

35 - Put Different Data On Different Drives

"Two heads are better than one"
"Put different data in different files on different drives"
.LOG and .DB on different drives
Tables in different dbspaces
Indexes versus tables
Load/dump data versus .DB
DBLOG
CREATE DBSPACE ... AS
CREATE TABLE ... IN
CREATE INDEX ... IN
Beware logical partitions

36 - Monitor Client And Server With DBWatch

Watch Checkpoint, Recovery Urgency
90%+ means server near capacity
Disconnect users

SQLCode:    -1
SQLDBCode:  -308
SQLErrText: SQLSTATE = S1000 [Sybase][ODBC Driver]
            General error: connection was
                           terminated

37 - Use -x To Limit Protocols

40 seconds longer startup without -x ...

38 - Use TCP/IP

The Geek's Choice
Faster than NetBIOS

A Final Word

"I think it is an immutable law in business that
words are words, explanations are explanations,
promises are promises
- but only performance is reality."

"Optimization is not free.
Gratuitous optimization can be translated directly
into missing features or later release dates."

"Knowing when to optimize
is as important as knowing how."
[Home] [Table of Contents]

Tip 77: Sybase SQL Anywhere Performance Tips & Techniques Overview

Tip 77: Sybase SQL Anywhere
Performance Tips & Techniques

Overview