Good database performance begins with good database design. Take the time
to incorporate design features into your schema during development for better response time
and faster query results.
Indexing
Indexing selection and solutions for SAP Sybase IQ.
Join Column
For joins, keep the data types as narrow as possible to reduce disk I/O and memory requirements.
Primary Keys
Multi-column primary keys should have an additional LF or HG index placed on each column specified in the primary key. This must be done manually as SAP Sybase IQ only creates an HG index on the composite columns.
Foreign Keys
As with primary keys, use foreign keys to improve query join performance. This gives SAP Sybase IQ one more piece of information on how tables are joined and the statistics behind those joins. SAP Sybase IQ automatically creates an HG Index on the foreign key column, so no additional HG or LF index is necessary. A foreign key requires that a primary key exists on referenced table.
Proper Data Type Sizing
Size all data types as accurately as possible, especially character-based data types.
Null Values
Defining columns as NULL or NOT NULL helps the optimizer work more efficiently.
Unsigned Data Types
In some cases, using unsigned data types can eliminate sign comparisons and create faster queries.
LONG VARCHAR and LONG VARBINARY
Use VARCHAR() and VARBINARY() to increase column storage without using large object storage mechanisms.
Large Object Storage
Use Large Object data types for data that requires more than 32K in storage.
Temporary Tables
If you want the data to persist through transaction commits, use the ON COMMIT PRESERVE ROWS option when you create global temporary tables or declare local temporary tables.
Denormalizing for Performance
Denormalizing your database can improve performance, but there are risks and disadvantages.
UNION ALL Views for Faster Loads
UNION ALL views can improve load performance when it is too expensive to maintain secondary indexes for all rows in a table.
Hash Partitioning
Hash table partitioning distributes data to logical partitions for parallel execution, which can enhance join performance on large tables and distributed queries (PlexQ).
Created November 21, 2013. Send feedback on this help topic to Technical Publications:
pubs@sap.com