Author: (US) Tony Andrews Translator: Chen Yong Yang Health Series name: Chapter Hua programmer library Publishing House: Machinery Industry Publishing House ISBN: 9787111425021 shelving time: -6-4 published on: July 4,: 16 webpage: 86 versions: 1-1 category: more about computers> DB2 SQL Performance Tuning tips computer books DB2 SQL Performance Tuning tips are rare DB2 database performance tuning tips, written by a senior Database Expert with more than 20 years of DB2 experience, Amazon has a five-star rating best-selling book. This book not only elaborates on more than 100 SQL statement optimization techniques and best practices, standards and principles for writing high-performance SQL statements, but also the "15-step" approach to DB2 database performance optimization ", there are also a large number of cases that provide solutions to solve a variety of complex DB2 performance problems. DB2 SQL Performance Tuning tips: Chapter 7 summarizes the skills and best practices for optimizing 1st SQL statements; chapter 2nd describes the writing methods and skills prompted in SQL statements. Chapter 3rd describes the SQL standards and principles to be followed in writing high-quality SQL statements. Chapter 4th describes SQL program queries; chapter 5th describes how to check the existence with two instances; Chapter 6th describes runstats for some statistical information; Chapter 7th describes the "15-step tuning method" for DB2 performance optimization ", it is known as the invaluable benefit of the DB2 Performance Tuning field and is suitable for performance tuning problems in various situations. There are two appendices at the end of this book, which respectively introduce the predicate rewriting example and the terminology in DB2 SQL. DB2 SQL Performance Tuning tips Chapter 1 SQL optimization tips 1st + 1. remove any scalar functions written in the predicate column. 2. remove any mathematical operations written in the predicate column. the Select part of the SQL statement only writes the necessary columns 4. try not to use distinct5. try to rewrite the in subquery to the exists subquery 6. make sure that the host variable is defined to match the column data type. because there may be problems with the optimization tool processing "or" logic, try to rewrite it in other ways. 8. make sure that the data distribution and other statistics in the processed table are correct and reflect the current situation 9. try to replace union10with Union all. Consider hard encoding or host variable 11. reduce DB2 SQL requests as much as possible 12. try to rewrite the interval predicate to between predicate 13. consider using a global temporary table 14. use Stage 1 predicate instead of stage 2 predicate 15. remember (some) the order of predicates is very important 16. sorts multiple subqueries. 17. index associated subquery 18. understand the DB2 explain tool 19. use tools for monitoring 20. adopt the submission and restart policies 21. achieve excellent index Design 22. avoid inconsistencies with non-column expressions 23. all filtering logics are placed outside the application code 24. make sure that the sub-query predicates involving min and Max must process the situations where NULL may be returned. 25. if only data is selected for a query, you must write the cursor to for Fetch only (get only) or for read only (read only) 26. avoid selecting a row from the table to help determine whether the code logic should be updated or inserted. avoid selecting a row 28 from the table just to get the update value. use dynamic SQL statements to cache 29. avoid using select * 30. be careful about columns that can be null, and be careful that SQL statements may return null31from the Database Manager. Minimize the number of times the cursor is opened and closed 32. avoid non-logic 33 in SQL. use Association ID to ensure better readability 34. ensure that the table and index files are legal and organized 35. make full use of update where current of cursor and delete where current of cursor36. when using a cursor, use multi-row acquisition, multi-row update, and multi-row insertion to locate and obtain 37 using rowset. understanding lock isolation level 38. about null processing 39. programming should consider performance 40. let SQL handle 41. use lock table42. consider OLTP frontend processing 43. consider using a dynamic scrollable cursor 44. improve response time using materialized query tables (only applicable to dynamic SQL) 45. combined with the select insert46. make full use of multiple rows to obtain 47. make full use of Multiline insert 48. make full use of Multiline update 49. make full use of Multiline deletion 50. in the select clause, select 51 for all scalar values. make full use of reopt once and reopt auto in dynamic SQL, and make full use of reopt vars and reopt always52. identify Table 53. improved by using on commit drop 54. use multiple distinct55. use reverse index scan 56. beware of like statement 57. correctly set clustering index 58. if necessary, use the group by expression 59. beware of tablespace Scan 60. do not ask the information you already know 61. note that the order of the tables in the query is 62. use the left Outer Join instead of the right Outer Join 63. check for nonexistent rows 64. use stored procedures 65. do not select a column 66 for sorting. limit the result set as much as possible. make full use of the improved discard function of DB2 V8 during batch deletion 68. make full use of the DB2 load tool to complete batch insertion 69. note that views, nested table expressions, and public table expressions are materialized. 70. consider compressing data 71. consider parallelism 72. keep stddev, stddev_samp, VAR, and var_samp functions away from other functions 73. consider using the rowid data type (V8) or the RID function (V9) to directly access row 74. use real statistics and certain data test queries to reflect performance issues 75. specify the leading index column 76 in the WHERE clause. use where instead of having to filter 77. try to handle index only 78. indexes on Expressions in DB2 V9 79. consider the DB2 V9 truncate statement 80. use DB2 V9 fetch first and order by81. use DB2 V9 for Optimistic Locking 82 in subqueries. use DB2 V9 merge statement 83. understand the DB2 nofor pre-compilation option 84. consider using order by85in select into. Try to write the Boolean predicate 86. write the passed closure 87. avoid sorting 88 by order. try to use join instead of subquery 89. beware of Case Logic 90. the order by clause makes full use of function 91. learn about your DB2 version 92. learn about date operations 93. learn how to select 94 for large-capacity insert. understand Skip locked data (V9) to avoid locking 95. sort the input stream 96. if you need true uniqueness, you can use the V8 generate_unique function 97. learn about the new option 98 for declaring a temporary table. note 99 when running get diagnostics. sort the in list by 100 as appropriate. combined with select update and delete (V9) 101. only execute SQL statement 102 when necessary. make full use of the memory Table 103. avoid the General SQL statement 104. avoid unnecessary sorting by 105. understand expressions and column functions 106. pay attention to 107 when using predicates in combination. add redundant predicates 108 for search queries. make full use of the improved dynamic cache (V10) 109. try the current commit to avoid the lock (V10) 110. try to use the system temporal table to obtain historical data (V10) 111. try to use the business temporal table to obtain historical data (V10) 112. learn about classification functions (V10) 113. make full use of the extended indicator (V10) 114. obtain a greater timestamp precision (V10) 115. try index primary des (V10) 116. use with return to client (V10) Chapter 2nd DB2 SQL prompt 1. use optimize for 1 row Statement 2 at the end of the SQL statement. add. pkey =. pkey predicate. Here pkey is equal to the table's primary key column 3. choose to change the index. 4. change the table processing sequence. use distributed dynamic SQL Chapter 1 SQL standards and principles for Cobol developers to view SQL programs in Chapter 2 SQL programs Chapter 2 Check existence Example 1 Example 2 Chapter 3rd runstats chapter 4th query initial adjustment optimization steps Appendix A predicate rewriting example appendix B DB2 SQL terms this book information source: interactive publishing network
