On Relational Support for XML Publishing Beyond Sorting

On Relational Support for XML Publishing Beyond Sorting and Tagging Surajit Chaudhuri Raghav Kaushik Jeffrey F. Naughton Presented by: Conn Doherty 02. 19. 2004 CS 561

Outline n n n n n Motivation & Observations XML Topic of Paper GApply Operator Approach Transformation Rules Experiments and Results Related Work Conclusions Future Problems 02. 19. 2004 CS 561

Motivation n Does the need for efficient XML publishing bring any new requirements for relational query engines, or is sorting query results in the relational engine and tagging them in middleware sufficient? 02. 19. 2004 CS 561

Observations n The mismatch between the XML data model and relational model requires relational engines to be enhances for efficiency n Need support for relation-valued variables 02. 19. 2004 CS 561

XML n Extendible Markup Language (rather a metalanguage or metalanguage) n Rapidly emerging as a standard for exchanging business data n Substantial interest in publishing existing relational data as XML 02. 19. 2004 CS 561

Current XML Publishing n Most focus has been on issues external to the RDBMS – Determining the class of XML views that can be defined – Languages used to specify the conversion from relational data to XML – Methods of composing XML queries with XML views n Data warehousing has caused focus on similar issues internal to RDBMS 02. 19. 2004 CS 561

Primary Topic of Paper n Focus closely on the class of SQL queries that are typically generated by XML publishing applications n Ask if anything needs to be changed within the relational engine to efficiently evaluate these queries? 02. 19. 2004 CS 561

YES! n Differences models in the XML and relational data – cause awkward and inefficient translations of XML queries to relational SQL queries n Main Issue – XML’s hierarchical model makes it very convenient and natural to apply operators to subtrees 02. 19. 2004 CS 561

Part Supplier Example n Part and Supplier Data Set – supplier(s_key, s_name) – partsupp(ps_suppkey, ps_partkey) – part(p_partkey, p_name, p_retailprice) 02. 19. 2004 CS 561

Part Supplier Example XML Document n Query Q 1: For each supplier element, return the names and retail prices of all parts supplied by that supplier, and also, the over-all average retail price of all parts supplied 02. 19. 2004 <suppliers> <supplier> <sname>S 1</sname> <parts> <part> <pname>P 1</pname> <retailprice>10</retailprice> </part> <pname>P 2</pname> <retailprice>10</retailprice> </parts> </supplier> <sname>S 2</sname> <parts> <part> <pname>P 21</pname> <retailprice>12</retailprice> </part> <pname>P 22</pname> <retailprice>13</retailprice> </parts> </supplier> <suppliers> CS 561

Example Queries n XQuery For $s in /doc(tpch. xml)/suppliers/supplier Return <ret> $s/s_suppkey <parts> For $p in $s/part Return <part> n $p/p_name $p/p_retailprice </ret> n SQL (select ps_suppkey, p_name, p_retailprice, null from partsupp, part where ps_partkey = p_partkey union all select ps_suppkey, null, avg(p_retailprice) from partsupp, part where ps_partkey = p_partkey group by ps_suppkey) Order by ps_suppkey </part> </parts> avg($s/part/p_retailprice) SQL (relational data model) is hard to express and inefficient – Unable to bind a variable to sets of tuples and execute subqueries on these sets 02. 19. 2004 CS 561

3 Angle Approach n 1) New operator, GApply – Binds variable to sets of tuples – Allows subqureies to be executed over set of tuples (tmp relation) bound to a variable n 2) Propose transformation rules to modify query plan trees with GApply operator n 3) Expose GApply operator in SQL syntax 02. 19. 2004 CS 561

GApply Operator n Syntax: GApply(GCols, PGQ) – GCols: grouping/partitioning columns – PGQ: per-group query n Input tuple stream is partitioned on GCols n PGQ applied to each group n Output is the union of all above results taken over all groups 02. 19. 2004 CS 561

Terminology n Outer tuple stream: input tuple stream n Inner query: per-group query n Outer child of GApply: root of outer query n Inner child of GApply: root of inner query 02. 19. 2004 CS 561

PGQ Restrictions n Only operate on temporary relation associated with the group of tuples n Operator type also known as groupwise processing allowed in PGQ: scan, select, project, distinct, apply, exists, union(all), groupby, aggregate, and orderby n Operators 02. 19. 2004 CS 561

Physical Implemenation n Two Phases: – Partitioning Phase § Implemented using sorting or hashing – Execution Phase § Performed in nested loop fashion § PGQ is evaluated on each group of tuples – Each group is a temporary relation bound to a relationvalued parameter $group 02. 19. 2004 CS 561

Implementation Diagram NL – Nested Loop Tmp relation: $group Outer Child Outer Query Partition Phase 02. 19. 2004 Inner Child Inner Query Execution Phase CS 561 $group

Expose GApply in Syntax n Difficult for the parser and optimizer to determine when GApply applies n Tests on Microsoft SQL Server 2000 with GApply operator not exposed in syntax – Need sometimes identified by optimizer – Use in each case, considerably speeds up performance 02. 19. 2004 CS 561

Proposed Syntax n Proposed extension to SQL syntax n SQL query performing groupwise processing: – Select gapply(PGQ(x)) as <column list> from <relation list> where <conditions> group by <grouping columns> : x – x is a relation-valued variable 02. 19. 2004 CS 561

Example Query in Syntax n Query Q 1: – select gapply(PGQ 1(tmp. Supp)) from partsupp, part where ps_partkey = p_partkey group by ps_suppkey: tmp. Supp – PGQ 1(tmp. Supp) § select p_name, p_retailprice, null from tmp. Supp union all select null, avg(p_retailprice) from tmp 02. 19. 2004 CS 561

Transformation Rules n Precise semantics of the operators n Three categories – 1) Pushing Computation into the Outer Query § Placing Projections Before GApply § Placing Selections Before GApply § Converting GApply to groupby – 2) Group Selection – 3) Pushing GApply Below Joins 02. 19. 2004 CS 561

Rule 2 n Group Selection – Consider PGQ that either return whole group (subtree) or nothing based on a predicate – Two methods to evaluate § Join suppliers & parts, group by suppkey, check selection method on group, if true - return group § Selection method to get suppkeys, then return join – Second method will win if predicate is highly selective 02. 19. 2004 CS 561

– Example Rule 2 cont. For $s in /doc(tpch. xml)/suppliers /supplier[/part/p_retailprice > 1000] Return $s 02. 19. 2004 CS 561

Integrating Rules in Optimizer n None of the rules above loop -> optimizer terminates n Optimizer must estimate the cost of the GApply operation 02. 19. 2004 CS 561

Preliminary Experiments n Performance study – Find efficacy of the GApply operator to speed up queries – Understand impact of each proposed transformation rule n Microsoft SQL Server 2000 – Supports GApply without syntax exposure – Control over GApply invocation is needed § Simulate operation of GApply on the client side 02. 19. 2004 CS 561

Client Side Simulation of GApply n Partition – Sorting – Hashing (simulation) n Execute – Store result of outer query in temporary table – For each distinct tmp group relation, evaluate PGQ on that relation, then union all results 02. 19. 2004 CS 561

Estimate Running Time n Measure both elapsed time and CPU time n Operator trees with GApply is the top most operator n Expect real elapsed time less in full server implementation 02. 19. 2004 CS 561

Setup n Experimental Setup – TPCH benchmark data – 5 GB database – Server § 1 GHz processor § 784 MB main memory § 512 MB buffer pool – Each query ran several times and then average taken 02. 19. 2004 CS 561

Results n Effectiveness of GApply – Comparable whether performing partitioning using sorting or hashing – Tested 4 queries representing a wide range of queries 02. 19. 2004 CS 561

GApply Effectiveness Results – Main conclusions: § § § GApply is a useful operator even for simple XQuery queries Yields improvements of factors of up to 2 x faster Queries representative of a wide class of queries Q 4 took 20% longer with the client side implementation Q 1, Q 2, Q 3 expect performance improvements with server side implementation (hash-based partitioning) 02. 19. 2004 CS 561

Results cont. n Effectiveness of Optimization Rules – Tested the improvement obtained by firing each rule – Performance metric is elapsed time – Method: § Choose relevant parameterized query § Vary parameter and find performance benefit for each value § Benefit ratio: elapsed time without the rule to time taken with the rule fired 02. 19. 2004 CS 561

Rule Effectiveness Example n Query: – For $s in /doc(tpch. xml)/suppliers /supplier[/part/p_retailprice > x] Return $s – x parameter determines the selectivity of selection 02. 19. 2004 CS 561

Results cont. n Effectiveness of Optimization Rules – Main conclusions: § Proposed rules can have significant impact on elapsed time of a query involving GApply § Some rules always lowered cost of the query, while other occasionally lowered or increased cost § Benefit of converting GApply to groupby is comparatively lower 02. 19. 2004 CS 561

Related Work n Xperanto Project n Silk. Route Project n ROLEX Project n Difference – Concluded, pushing as much computation to relational engine is best – Language to specify the conversion between relational data and XML – To avoid inefficient parsing in applications, the relational engine returns a navigable result tree – Question whether whole process of XML publishing has any impact on the core relational operators (YES) 02. 19. 2004 CS 561

Conclusions n Relational engine must provide support for binding variable to sets of tuples n Required support can be enabled through the GApply operator with seamless integration into existing relational engines n Operator should be exposed in the syntax n Optimization rules are needed 02. 19. 2004 CS 561

Future Problems n How should modified syntax be exploited by algorithms to translate XML queries over XML views of relational data? n Any other changes needed to meet the requirements of XML publishing? n What changes are needed in the optimizer if the relational database returns navigable results? 02. 19. 2004 CS 561

Other Papers n D. Chatziantoniou and K. A. Ross. Querying multiple features of groups in relational databases. In VLDB, 1996. – Extension to SQL syntax with relational algebra implementation n D. Chatziantoniou and K. A. Ross. Groupwise processing of relational queries. In VLDB, 1997. – Methods to identify group query components n C. A. Galindo-Legaria and M. M. Joshi. Ortogonal optimization of subqueries and aggregation. In SIGMOD, 2001. – Introduction of segment. Apply operator and many transformation rules 02. 19. 2004 CS 561