1 / 34

Proactive Index Design with QUBE: A Guide for Optimal Database Performance

Learn how to optimize performance by using QUBE for index design and estimating CPU time for queries. Gain insights into B-Tree index anatomy and efficient access methods for common transactions.

cedric-valenzuela
Télécharger la présentation

Proactive Index Design with QUBE: A Guide for Optimal Database Performance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Proactive Index Design using QUBECourtesy of Tapio Lahdenmäki Lauri Pietarinen Relational Consulting Session Code: F04 Nov 09, 2010 8:30 – 9:30 AM | Platform: z/OS, LUW

  2. Q - Quick U - Upper B - Bound E - Estimate A simple formula for estimating CPU and elapsed time for queries, created by Tapio Lahdenmäki and others at IBM-Finland

  3. Basic Access Methods

  4. Simple Anatomy of the B-Tree Index

  5. Recommended Mental Image

  6. Case Study: Request Tracking

  7. Common Transaction

  8. Which One is Faster?

  9. Sequential Read in 2010

  10. Random Read in 2010

  11. Random Read from Disk Drive

  12. Disk Drives – the Bottleneck

  13. Quick Upper Bound Estimate (QUBE)

  14. Alternative 1

  15. Alternative 2A

  16. Alternative 2B

  17. The Basic Question

  18. Semi-Fat Index

  19. QUBE for Semi-Fat Index – Your Turn!

  20. Still Too Long – What Next?

  21. When Do Touches Take Place?

  22. No Sort, 20 Fetches

  23. Worst-Input Estimates

  24. Fat Index with Sort

  25. Worst-Input Estimates, with Fat Index

  26. Too Expensive?

  27. The Cost of Adding an Index

  28. The Cost of Adding an Index Column

  29. So, Too Expensive?

  30. Obsolete and Relevant Guidelines

  31. Index BO was not Adequate for this Select

  32. QUBE for Semi-Fat Index -- Solution

  33. Summary Qube is a way of thinking about indexes It can be used to prevent performance problems It can (and should) be used in conjunction with other tools It can be used to understand and analyze performance problems

  34. Lauri PietarinenRelational Consultinglauri.pietarinen@relational-consulting.com F04 Proactive Index Design using QUBE

More Related