By Tony Andrews
Bankruptcy 1 SQL Optimization best a hundred 1 1. Take Out Any/All Scalar capabilities Coded on Columns in Predicates 2 2. Take Out Any/All arithmetic Coded on Columns in Predicates three three. Code in simple terms the Columns wanted within the pick out component of the SQL assertion four four. steer clear of distinctive if attainable four five. test Rewriting an In Subquery as an Exists Subquery five 6. constantly confirm Host Variables Are outlined to check the Columns Datatype 6 7. simply because Or good judgment will be troublesome to the Optimizer, try out a special Rewrite 6 eight. ensure the information Distribution and different data Are reliable and present within the Tables Being Processed eight nine. Use UNION ALL in preference to UNION the place attainable nine 10. think about Hardcoding as opposed to utilizing a bunch Variable nine eleven. reduce DB2's SQL Requests eleven 12. attempt Rewriting diversity Predicates as among Predicates 12 thirteen. think about using worldwide transitority Tables thirteen 14. provide Prominence to level 1 over degree 2 Predicates 14 15. keep in mind that the Order of (Some) Predicates Does subject 15 sixteen. Streamline a number of Subqueries sixteen 17. Index Correlated Subqueries 17 18. Get to grasp the DB2 clarify device 17 19. Use instruments for tracking 18 20. hire devote and Restart thoughts 19 21. enforce strong Index layout 19 22. stay away from Discrepancies with Non-Column Expressions 20 23. commence with All Filtering common sense open air program Code 21 24. make sure that Subquery Predicates related to Min and Max Have the potential for Nulls Being back dealt with 21 25. constantly Code For Fetch merely or For learn simply with Cursor Processing whilst a question is simply picking facts 22 26. stay away from picking out a Row from a desk to assist make a decision no matter if the common sense within the Code may still Execute an replace or an Insert 23 27. steer clear of opting for a Row from a desk so that it will Get Values for an replace 23 28. utilize Dynamic SQL assertion Caching 23 29. steer clear of utilizing choose * 24 30. beware of Nullable Columns or occasions whilst SQL Statements may have Nulls back from the Database supervisor 25 31. reduce the variety of occasions Open and shut Cursors Are finished 25 32. steer clear of no longer good judgment in SQL 26 33. Use Correlation IDs for greater clarity 26 34. retain desk and Index documents fit and arranged 27 35. reap the benefits of replace the place present of Cursor and Delete the place present of Cursor 27 36. whilst utilizing Cursors, Use ROWSET Positioning and Fetching utilizing Multiple-Row Fetch, Multiple-Row replace, and Multiple-Row Insert 28 37. recognize the Locking Isolation degrees 28 38. understand Null Processing 30 39. regularly software with functionality in brain 31 forty. allow SQL Do the paintings 32 forty-one. Code with Lock desk 32 forty two. give some thought to OLTP Front-End Processing 33 forty three. think about using Dynamic Scrollable Cursors 34 forty four. make the most of Materialized question Tables to enhance reaction Time (Dynamic SQL purely) 35 forty five. Insert with choose 37 forty six. make the most of Multiple-Row Fetch 38 forty seven. reap the benefits of Multiple-Row Insert 39 forty eight. reap the benefits of Multiple-Row replace forty forty nine. reap the benefits of Multiple-Row Delete forty two 50. test Scalar Fullselects in the decide upon Clause forty two fifty one. reap the benefits of REOPT as soon as and REOPT automobile in Dynamic SQL and REOPT VARS and REOPT consistently in Static SQL forty three fifty two. establish instances for unstable Tables forty four fifty three. Use the ON devote DROP Enhancement forty five fifty four. Use a number of Distincts forty five fifty five. benefit from Backward Index Scanning forty six fifty six. beware of the Like assertion forty six fifty seven. Set Your Clustering Index adequately forty seven fifty eight. Use workforce by way of Expressions if wanted forty eight fifty nine. beware of Tablespace Scans forty eight 60. don't Ask for What you understand forty nine sixty one. Watch the Order of Tables in a question forty nine sixty two. Use Left Outer Joins Over correct Outer Joins fifty one sixty three. payment for Non-Existence fifty one sixty four. Use saved approaches fifty two sixty five. don't choose a Column to be able to style on It fifty three sixty six. constantly restrict the end result Set if attainable fifty three sixty seven. benefit from DB2 V8 stronger DISCARD functions by way of Mass Deletes fifty four sixty eight. reap the benefits of the DB2 LOAD software for Mass Inserts fifty four sixty nine. beware of Materialization of perspectives, Nested desk Expressions, and customary desk Expressions fifty five 70. examine Compressing facts fifty six seventy one. think of Parallelism fifty seven seventy two. preserve the STDDEV, STDDEV_SAMP, VAR, and VAR_SAMP features except different capabilities fifty eight seventy three. reflect on Direct Row entry utilizing ROWID Datatype (V8) or RID functionality (V9) fifty eight seventy four. attempt Your Queries with real looking records and a degree of knowledge to mirror functionality concerns 60 seventy five. Specify the major Index Columns in the place Clauses sixty one seventy six. Use the place rather than HAVING for Filtering each time attainable sixty two seventy seven. bear in mind Index basically Processing each time attainable sixty two seventy eight. Index on Expression in DB2 V9 sixty three seventy nine. think of the DB2 V9 Truncate assertion sixty four eighty. Use DB2 V9 Fetch First and Order via inside Subqueries sixty five eighty one. benefit from DB2 V9 confident Locking sixty five eighty two. Use the DB2 V9 MERGE assertion sixty six eighty three. comprehend the DB2 NOFOR Precompile alternative sixty eight eighty four. think of opt for Into utilizing Order by way of sixty nine eighty five. Code Boolean time period Predicates at any time when attainable sixty nine 86. attempt Transitive Closure Coding 70 87. stay away from kinds with Order by means of seventy one 88. Use Joins rather than Subqueries each time attainable seventy one 89. beware of Case common sense seventy one ninety. benefit from services within the Order via Clause seventy two ninety one. understand Your model of DB2 seventy two ninety two. comprehend Date mathematics seventy three ninety three. recognize Your High-Volume Insert offerings seventy three ninety four. find out about pass Locked information (V9) for Lock Avoidance...75 ninety five. variety Your enter Streams seventy five ninety six. if you want actual area of expertise, try out the V8 Generate_Unique functionality seventy six ninety seven. understand the hot innovations for Declared transitority Tables seventy six ninety eight. be careful while Executing Get Diagnostics seventy seven ninety nine. Order Your In record competently seventy seven a hundred. replace and Delete with opt for (V9) seventy seven a hundred and one. Execute SQL Statements provided that worthy seventy eight 102. make the most of In-Memory Tables seventy eight 103. avoid Catchall SQL Statements seventy nine 104. stay away from pointless Sorting seventy nine a hundred and five. comprehend Expressions and Column features seventy nine 106. be careful while Combining Predicates eighty 107. upload Redundant Predicates to look Queries eighty 108. reap the benefits of superior Dynamic Caching (V10) eighty one 109. test presently devoted for Lock Avoidance (V10) eighty two a hundred and ten. attempt process Temporal Tables for ancient information (V10) eighty three 111. try out company Temporal Tables for old info (V10) eighty five 112. comprehend Your score features (V10) 86 113. reap the benefits of prolonged symptoms (V10) 87 114. Get larger Timestamp Precision (V10) 88 a hundred and fifteen. test Index contains (V10) 89 116. Use With go back to customer (V10) 89 bankruptcy 2 DB2 SQL tricks ninety one 1. try out the Optimize for 1 Row assertion on the finish of the SQL assertion ninety one 2. upload the A.PKEY = A.PKEY Predicate to the SQL question, the place PKEY Equals the first Key Column of the desk ninety two three. Disqualify an Index selection ninety three four. swap the Order of desk Processing ninety five five. Use disbursed Dynamic SQL ninety six bankruptcy three SQL criteria and guidance ninety nine For COBOL builders ninety nine For All SQL builders 102 bankruptcy four SQL software Walkthroughs 107 bankruptcy five life Checking 111 instance 1 111 instance 2 113 bankruptcy 6 Runstats a hundred and fifteen bankruptcy 7 preliminary Steps in Tuning a question 117 APPENDIX A Predicate Rewrite Examples 121 Predicate Rewrites: Transitive Closure 122 APPENDIX B DB2 SQL Terminology a hundred twenty five Index 131
Read or Download DB2 SQL tuning tips for z/OS developers PDF
Best databases books
Multimedia details Retrieval: Content-Based details Retrievalfrom huge textual content and Audio Databases addresses the long run want for classy seek recommendations that may be required to discover suitable info in huge electronic facts repositories, equivalent to electronic libraries and different multimedia databases.
Massive info Analytics will support managers in offering an outline of the drivers for introducing substantial info know-how into the association and for figuring out the categories of industrial difficulties most fitted to important facts analytics suggestions, knowing the price drivers and merits, strategic making plans, constructing a pilot, and finally making plans to combine again into construction in the company.
Adventure studying made easy-and speedy train your self tips to construct database strategies with entry 2010. With step-by-step, you place the pace-building and practising the talents you would like, simply for those who desire them! themes comprise construction an entry database from scratch or from templates; publishing your database to the net; changing facts with different databases and Microsoft place of work records; growing data-entry kinds; utilizing filters and queries; designing stories; utilizing conditional formatting; fighting information corruption and unauthorized entry; and different center themes.
- MySQL Datenbankhandbuch
- The Berkeley DB Book
- Nested Relations and Complex Objects in Databases
- Pro Couchbase Server
- Expert PHP and MySQL: Application Design and Development
- Distributed Databases in Real-Time Control
Additional info for DB2 SQL tuning tips for z/OS developers
05 COMM PIC S9(7)V99 COMP-3. 77 EOF-FLAG PIC X VALUE ‘N’. 9 10 CHAPTER 1 The Magic Words inherent in SQL. The COBOL example, though straightforward to a COBOL programmer, is more difficult for most beginning users to code and understand. NOTE Set-level processing differs from record-level processing because • All operations act on a complete set of rows • Fewer operations are necessary to retrieve the desired information • Data manipulation and retrieval instructions are simpler The set-level processing capabilities of SQL have an immediate and favorable impact on DB2’s capability to access and modify data.
Set-level processing, on the other hand, satisfies the same request with a single, non-navigational statement. Because fewer distinct operations (read, write, and so on) are required, set-level processing is simpler to implement. 1 The primary reason SQL was extended to support procedural structures was to enable stored procedures and triggers to be written and deployed using SQL alone. 2 Record-at-a-time processing versus set-at-a-time processing. The power of SQL becomes increasingly evident when you compare SQL to COBOL (and flat files to relational databases).
Use these sections as a refresher course; they are not meant to teach SQL syntax or provide in-depth coverage of its use. 4 Selection and projection. The DB2 SELECT Statement But don’t get ahead of yourself. What exactly is a SELECT statement? You can break down a SELECT statement into three components: • select-statement • fullselect • subselect The select-statement is the highest level; it is the thing most people think of when they think of SELECT in all its glory. If so desired, it can be embedded into an application program or issued interactively using SPUFI.