Data Model Recommendations

Good database performance begins with good database design. Take the time to incorporate Sybase IQ's unique design features into your schema during development for better response time and faster query results.

• Indexing Tips
  Choose the correct column index type to make your queries run faster.
• When and Where to use Indexes
  Indexes are the primary tuning mechanisms inside Sybase IQ. Knowing when and where to use indexes can make your queries run faster.
• Simple Index Selection Criteria
  Answers to some simple questions can help you choose the right index for a column.
• HG Index Loads
  Relative to other IQ indexes, the HG indexes are more expense to maintain during data loads and deletions. A main contributor to the performance of the HG index is the location of the data within the HG index structure: the sparsity or density of the operation.
• Multi-Column Indexes
  Currently, only HG, UNIQUE HG, UNIQUE CONSTRAINT, and PRIMARY KEY indexes support multiple columns in index creation, but multi-column indexes are also useful for GROUP BY and ORDER BY statements.
• 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 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 IQ one more piece of information on how tables are joined and the statistics behind those joins. 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.
• IQ UNIQUE and MINIMIZE_STORAGE
  Using IQ UNIQUE and MINIMIZE_STORAGE can save disk space and improve performance.
• 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
  Although denormalizing your database can improve performance, 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.