SQL/MX Release 2.0 Best Practices

Project Activities Guideline for Database Activities 12
• Trickle compared to batch updates. Compare trickle against batch. What is the impact on
rerunning queries and on query performance? Consider the size of the load and update window
and the impact on aggregates.
• Custom versus DataLoader/MX versus tool. Consider the need for parallelism and for robust
architecture. Compare flexibility against a “canned” approach, partition management needs, and
so on.
• Coded data insert and update processes. Coded data insert and update processes are resource
intensive and require a thorough knowledge of the database management system (DBMS),
regardless of the tool or approach used.
• Initial data load. Consider the load dimension tables, the initial load of fact tables, and the initial
build of aggregate tables.
Manageability
• Catalog and schema statistics. Update statistics for all tables. Modify statistics as needed for skewed
data.
• ESP management. Control the number and placement of ESPs through the use of a partitioning
scheme for dimension tables.
• Partition management. Define the schedule and create the scripts for partition reorganization and
growth and splits.
• Memory management. Configure the cache while keeping in mind the use or non-use of cache for
sequential I/O. Consider the hash memory requirements.
• Resource accounting. Evaluate and implement resource accounting within ODBC.