Tuning the Database Design Schema tuning Vertically partition relations to isolate the data that is accessed most often--only fetch needed information. E.g.,split account into two,(account-number,branch-name)and (account-number,balance). Branch-name need not be fetched unless required Improve performance by storing a denormalized relation E.g.,store join of account and depositor;branch-name and balance information is repeated for each holder of an account,but join need not be computed repeatedly. -Price paid:more space and more work for programmer to keep relation consistent on updates Better to use materialized views (more on this later..) Cluster together on the same disk page records that would match in a frequently required join Compute join very efficiently when required. Database System Concepts-6th Edition 24.12 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 6 24.12 ©Silberschatz, Korth and Sudarshan th Edition Tuning the Database Design Schema tuning Vertically partition relations to isolate the data that is accessed most often -- only fetch needed information. E.g., split account into two, (account-number, branch-name) and (account-number, balance). – Branch-name need not be fetched unless required Improve performance by storing a denormalized relation E.g., store join of account and depositor; branch-name and balance information is repeated for each holder of an account, but join need not be computed repeatedly. – Price paid: more space and more work for programmer to keep relation consistent on updates Better to use materialized views (more on this later..) Cluster together on the same disk page records that would match in a frequently required join Compute join very efficiently when required
Tuning the Database Design (Cont.) Index tuning Create appropriate indices to speed up slow queries/updates Speed up slow updates by removing excess indices(tradeoff between queries and updates) Choose type of index(B-tree/hash)appropriate for most frequent types of queries. Choose which index to make clustered Index tuning wizards look at past history of queries and updates(the workload)and recommend which indices would be best for the workload Database System Concepts-6th Edition 24.13 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 24.13 ©Silberschatz, Korth and Sudarshan th Edition Tuning the Database Design (Cont.) Index tuning Create appropriate indices to speed up slow queries/updates Speed up slow updates by removing excess indices (tradeoff between queries and updates) Choose type of index (B-tree/hash) appropriate for most frequent types of queries. Choose which index to make clustered Index tuning wizards look at past history of queries and updates (the workload) and recommend which indices would be best for the workload
Tuning the Database Design(Cont.) Materialized Views Materialized views can help speed up certain queries Particularly aggregate queries Overheads Space Time for view maintenance Immediate view maintenance:done as part of update txn time overhead paid by update transaction Deferred view maintenance:done only when required - update transaction is not affected,but system time is spent on view maintenance until updated,the view may be out-of-date Preferable to denormalized schema since view maintenance is systems responsibility,not programmers Avoids inconsistencies caused by errors in update programs Database System Concepts-6th Edition 24.14 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 24.14 ©Silberschatz, Korth and Sudarshan th Edition Tuning the Database Design (Cont.) Materialized Views Materialized views can help speed up certain queries Particularly aggregate queries Overheads Space Time for view maintenance Immediate view maintenance: done as part of update txn – time overhead paid by update transaction Deferred view maintenance: done only when required – update transaction is not affected, but system time is spent on view maintenance » until updated, the view may be out-of-date Preferable to denormalized schema since view maintenance is systems responsibility, not programmers Avoids inconsistencies caused by errors in update programs
Tuning the Database Design (Cont.) How to choose set of materialized views Helping one transaction type by introducing a materialized view may hurt others Choice of materialized views depends on costs Users often have no idea of actual cost of operations Overall.manual selection of materialized views is tedious Some database systems provide tools to help DBA choose views to materialize “Materialized view selection wizards” Database System Concepts-6th Edition 24.15 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 24.15 ©Silberschatz, Korth and Sudarshan th Edition Tuning the Database Design (Cont.) How to choose set of materialized views Helping one transaction type by introducing a materialized view may hurt others Choice of materialized views depends on costs Users often have no idea of actual cost of operations Overall, manual selection of materialized views is tedious Some database systems provide tools to help DBA choose views to materialize “Materialized view selection wizards
Tuning of Transactions Basic approaches to tuning of transactions Improve set orientation Reduce lock contention Rewriting of queries to improve performance was important in the past, but smart optimizers have made this less important Communication overhead and query handling overheads significant part of cost of each call Combine multiple embedded SQL/ODBC/JDBC queries into a single set-oriented query Set orientation -fewer calls to database E.g.,tune program that computes total salary for each department using a separate SQL query by instead using a single query that computes total salaries for all department at once(using group by) Use stored procedures:avoids re-parsing and re-optimization of query Database System Concepts-6th Edition 24.16 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 24.16 ©Silberschatz, Korth and Sudarshan th Edition Tuning of Transactions Basic approaches to tuning of transactions Improve set orientation Reduce lock contention Rewriting of queries to improve performance was important in the past, but smart optimizers have made this less important Communication overhead and query handling overheads significant part of cost of each call Combine multiple embedded SQL/ODBC/JDBC queries into a single set-oriented query Set orientation -> fewer calls to database E.g., tune program that computes total salary for each department using a separate SQL query by instead using a single query that computes total salaries for all department at once (using group by) Use stored procedures: avoids re-parsing and re-optimization of query