RDBMS:ProgramOptimization
lastmod 2021-04-28created 2021-04-285 edits
A summary of RDBMS optimization methods.
Parameter tuning
Join strategy
When a query is executed, query performance can vary greatly depending on which of the various join methods the optimizer adopts. In general, a Nested loops join is used when the number of rows is small, and a Hash join is used when the number of rows is large. In addition, better performance can be guaranteed only if the table with fewer rows among the tables being joined is accessed first.
Index strategy
Data architecture strategy
- Data requirements analysis
- Database selection (ex : RDBMS, NoSQL, graph DB, embedded DB, etc.)
- Server/cluster configuration (ex: single server, HA configuration, cluster configuration, RAC, Max scale, etc.)
- File system selection (Oracle : OS Filesystem vs ASM vs Raw device)
- Database storage engine selection (MariaDB : Innodb, Aria, XtraDB, etc.)
- Data standardization (words/terms/domains)
- Data modeling (conceptual/logical/physical, subject areas)
- Defining relationships between data
- Defining data interfaces
- Data collection methods and scope (ex : App.-generated data, external integration, statistical data, system operation data, etc.)
- Object usage policy (ex: whether to use triggers, whether to use PL/SQL, whether to set FKs, maximum number of indexes allowed, etc.)
- Naming conventions (ex: table names, tablespace names, index names, user account names, etc.)
- Database user account/privilege policy
- Database access control policy
- Data retention period
- Data log management
- Data backup strategy
SQL tuning
See also
Favorite site
References
-
RDBMS_performance_optimization_strategy.pdf ↩