Schema Design

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).