1 / 28

Just-in-Time Indexes

Just-in-Time Indexes. Understanding Indexes in KB_SQL March 2001. March 2001. Learning Objectives. What options do I have to improve query performance? The answer is…indexes. Review indexing strategies Introduce just-in-time indexes Discuss advantages and caveats. Improve Performance.

raja
Download Presentation

Just-in-Time Indexes

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. Just-in-Time Indexes Understanding Indexes in KB_SQL March 2001 March 2001

  2. Learning Objectives • What options do I have to improve query performance? The answer is…indexes. • Review indexing strategies • Introduce just-in-time indexes • Discuss advantages and caveats

  3. Improve Performance Why does my query run so slow?

  4. Query Results

  5. Show Plan/Stats: Query Run query with Show Plan and Show Stats

  6. Show Plan: Results Note: No index being used

  7. Show Stats: Results Note: Selecting 9 of 25000 rows searched

  8. Show All Plans: Query Run query with SHOW_ALL_PLANS to see if index is available

  9. Show All Plans: Results Note: No indexes available

  10. Index Exists

  11. Define Index to KB_SQL Add BY_NAME index in KB_SQL

  12. New Show Plan Note: BY_NAME index is used, cost is reduced

  13. New Show Stats Note: Selecting 9 of 9 rows searched

  14. New Show All Plans Note: BY_NAME index available

  15. How do we improve performance if we don’t have sufficient indexes? • What are the options? • Create supplemental table(s) • Build index • Create just-in-time index • When will it be populated? • How often should it be updated? • Advantages and caveats?

  16. Create Supplemental Tables • Create new table • Join to “base” table

  17. Build the Index • Benefits • Queries only look at key data which results in faster searches • Caveats • Time • Resources • Additional globals to maintain

  18. What if there is no index?

  19. What if there is no index? (cont)

  20. What if there is no index? (cont)

  21. Just-in-Time Indexes • When will the index be populated? • How often should the index be updated?

  22. Just-in-Time Indexes: Advantages and Caveats • Advantages over traditional indexes • Add only the indexes that the user demands • Reduce wasted effort to build indexes that won’t be used • Caveats • Does not provide up-to-the-minute data accuracy • 2 or more queries to realize benefit

  23. Build the Just-in-Time Index

  24. Define the Index to KB_SQL

  25. New Show All Plans Note: Two indexes available; BY_SUPPLIER is accepted based on constraint

  26. New Show Plan Note: BY_SUPPLIER index is used

  27. New Show Stats Note: Selecting 4 of 4 rows searched

  28. Summary • Discussed options available to help improve query performance The answer is…indexes. • Reviewed indexing strategies • Introduced just-in-time indexes • Discussed advantages and caveats

More Related