1 / 18

Optimizing Recursive Queries in SQL

Learn how to optimize recursive queries in SQL for better performance. This paper covers syntax, examples, technical details, issues, and recommendations. Understand the impact of indexing, early row selection, and duplicate elimination on query efficiency.

alehr
Download Presentation

Optimizing Recursive Queries in SQL

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. Optimizing Recursive Queries in SQL Carlos Ordonez ACM SIGMOD 2005

  2. Introduction • Recursion available in ANSI SQL • Deductive databases; graph problems • Challenging query optimization • Syntax now available in Teradata SQL V2R6

  3. Directed Graphs • Consider G=(V,E) • A vertex in V is identified with i or j and i,j=1..n. An edge i,j has a direction • G may have cycles • G: adjacency list in table T • Transitive closure problem: give me all vertices reachable from i

  4. Examples • V=cities,E=roads. Is there some path from San Diego to NYC?: path from i to j? shortest one? • V=employees, E=manager -> employee relationship q1: all employees under i q2: Is j supervised by i? • The well-known part/subpart manufacturing DB

  5. Recursive view Rk: rec. depth n: graph size

  6. Technical details • Linear recursion • Inner joins • SELECTs must have same term types • No GROUP-BY, DISTINCT, HAVING, NOT IN, OUTER JOIN clauses inside R • Any SQL construct is valid on top of R • Recursive view can used as any table • Recursion depth k: loop with k-1 joins

  7. Row selection: WHERE clause

  8. WHERE clause in base step

  9. Issues with WHERE clauses • Incorrect to use a WHERE clause involving a join expression column in the recursive step • Infinite recursion: cycles • Monotonically increasing v OK • Recursion depth

  10. Using DISTINCT: many paths

  11. Eliminating duplicate rows

  12. Indexing • T is self-joined many times • R is appended many times • Subset of R at depth d-1 used at each recursive step d

  13. Indexing schemes • Index on join expression: • T is indexed on (i) • R is indexed on (j) • Index on primary keys: • T is indexed on (i,j) • R is indexed on (d,i,j)

  14. Impact of early row selectionn: |V|, k=max recursion depth, times in secs

  15. Impact of duplicate eliminationn: |V|, k=max recursion depth, times in secs

  16. Indexingn: |V|, k=max recursion depth, times in secs

  17. Recommendations • Writing base step first • A wrong join expression may produce larger cartesian products than usual • Setting a maximum recursion depth k • Careful indexing

  18. Conclusions • Constraints inside recursive view • Three optimizations: WHERE, DISTINCT, indexing • Max recursion depth • Graphs with cycles impact performance

More Related