1 / 13

Optimizing recursive queries in sql

This article explores the intricacies of recursive queries in SQL, defining their structure and purpose in accessing hierarchical data. It outlines key optimization techniques such as early evaluation of row selection conditions, deleting duplicate rows, and efficient indexing for joins. The practical implications of each optimization technique are discussed, along with their impact on performance in various scenarios. Understanding these optimizations is crucial for effectively utilizing recursive queries in database management.

xavier-nielsen
Télécharger la présentation

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 BY: Mark Gruszecki

  2. Overview • What is a Recursive Query? • Definition(s) and Algorithm(s) • Optimization Techniques • Practical Issues • Impact of each Optimization • Conclusion

  3. In order to understand a Recursive Query, you must first understand a Recursive Query… CREATE RECURSIVE VIEW R(d, i, j, v) AS ( SELECT 1, i, j, v FROM T UNION ALL SELECT d+1, R.i, T.j, R.v + T.v FROM R JOIN T ON R.j = T.i WHERE d < 8);

  4. What is a Recursive Query used for? • Any kind of Hierarchical Data is more easily accessed using a Recursive Query. • For example, displaying Employees in an orginizational chart.

  5. Definition(s) and Algorithm(s) • A base table T is defined as T(i, j, v) with primary key (i, j) and v representing a numerical value. • Define the result table from a recursive query by R(d, i, j, v) with primary key (d, i, j). • The queries of interest are of the form • R[k] = T  T  T  …  T where  is the recursive query. • Let G = (V, E) be a directed graph with n vertices and m edges. • The algorithm to evaluate optimizations is: • R =

  6. Optimization Techniques • Early evaluation of row select conditions. • Deleting Duplicate Rows. • Indexing base and result tables for efficient joins.

  7. Early Evaluation of Row Select Conditions • Used on Queries of the form: SELECT i , j, v FROM R WHERE <condition> • Two Cases: • If the “WHERE” condition doesn’t participate in the JOIN condition of the recursion. • The “WHERE” condition is part of the JOIN in the recursion.

  8. Deleting Duplicate Rows • Suppose we want to know which vertices of a graph are connected to a specific node (e.g. all dependants of a particular person). • What if G is complete, dense, sparse, or even a tree? • Queries are optimized by deleting duplicate rows at each step.

  9. Indexing Base and Result Tables for Efficient Joins • Two Choices: • Index based on i and j. • Gives rise to an optimal Hash Join • Cannot uniquely identify rows • Index based on primary key of T and R • Can uniquely identify rows

  10. Practical Issues • The possible size of a result table of a recursive query is |T| x |T| x … |T|. • In general, if there are n recursive steps and T has m entries, then there are m^n.

  11. Impact of each Optimization • Early Evaluation of Row Selection Conditions • Good in all cases • Deleting Duplicate Rows • Good in certain cases, but not necessary in all • Indexing Base and Result Tables for Efficient Joins • If G is highly connected, slight loss of performance (Hash Collisions). • Otherwise, this is the best optimization.

  12. Conclusion • Recursive Queries are an important tool to be exposed to when dealing with databases. • There are three types of optimizations of these queries discussed: • Early evaluation of row selections • Deleting duplicate rows • Indexing for more efficient joins • Every optimization is good, but some are more applicable in certain circumstances than others.

  13. Questions(?) and References • Carlos Ordonez (2005) Optimizing Recursive Queries in SQL. Retrieved February 10, 2009 from IEEE Computer Science Digital Library • http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/rzajq/rzajqrcteexample.htm

More Related