Incorporating partitioning and parallel plans into the SCOPE optimizer

Abstract

Massive data analysis on large clusters presents new opportunities and challenges for query optimization. Data partitioning is crucial to performance in this environment. However, data repartitioning is a very expensive operation so minimizing the number of such operations can yield very significant performance improvements. A query optimizer for this environment must therefore be able to reason about data partitioning including its interaction with sorting and grouping.SCOPE is a SQL-like scripting language used at Microsoft for massive data analysis. A transformation-based optimizer is responsible for converting scripts into efficient execution plans for the Cosmos distributed computing platform. In this paper, we describe how reasoning about data partitioning is incorporated into the SCOPE optimizer. We show how relational operators affect partitioning, sorting and grouping properties and describe how the optimizer reasons about and exploits such properties to avoid unnecessary operations. In most optimizers, consideration of parallel plans is an afterthought done in a postprocessing step. Reasoning about partitioning enables the SCOPE optimizer to fully integrate consideration of parallel, serial and mixed plans into the cost-based optimization. The benefits are illustrated by showing the variety of plans enabled by our approach.

From conceptual design to performance optimization of ETL workflows: current state of research and open problems

“…It extends the work presented in [44] by introducing parallelization techniques for UDFs. An UDF is treated as a black-box operation.…”

“…The scheduling policies are proposed to optimize the ETL workflow with respect to execution time and memory consumption. In the literature, there exist multiple methods that revolve around data flow parallelism [34,36,40,[43][44][45]. However, research on an ETL workflow parallelism has not appealed much consideration.…”

“…The SCOPE optimizer introduces considerable parallelism based on a cost function. As presented in [44], the SCOPE optimizer generates a large number of execution plans by taking into account structural properties of data (e.g., partitioning, sorting, or grouping). The generated execution plans are then pruned using a cost model.…”

“…A script (with annotations) similar to SQLScript [46] is used to express complex data flows containing ROs and UDFs together. The main goal is to parallelize ROs and UDFs together, which is achieved by directly translating a RO into the internal representation of the proposed cost-based optimizer as described in [44] and by applying the 'Worker-Farm' pattern [47] on an UDF. A complete set of annotations is described in [45].…”

Advanced join strategies for large-scale distributed computation

“…In a distributed environment, an additional dimension is introduced into the join taxonomy: the join graph topology. Graph topologies specify how different partitions of data are processed in a distributed way, and is affected by the following factors [25]:…”