SQL/MX Release 2.0 Best Practices

Design Guidelines and Considerations 17
In the absence of query requirements, designers often optimize for the requirements they understand,
usually database loading. Although such a design is not necessarily bad, it often produces good
performance for database loading, but poor performance for query processing.
Whenever possible, try to anticipate the query requirements in your design decisions. At the very least, set
the expectation that portions of the database might need to be modified (and programs changed)
once query requirements are known. Consider implementing a full-scale (or near-full-scale) pilot of the
proposed design, and have alternative designs ready in case they are needed.
Built-in Application Bottlenecks: Inadequate Design Analysis
and Testing
During project planning, provide enough time for adequate testing, planning, and investigation of
alternative database designs. Take time to create a large or full scale test database, partitioned and
organized as it would be in production. Construct typical queries and analyze their DISPLAY_EXPLAIN
output to determine whether the database is properly structured. Load the tables with test data and use
the Measure product to analyze both load and query performance. Load-only design is another
potential issue; see the “Load-only optimized design” section above.