Improving the Automatic Pretanslator of SQL-Queries to a Regular Plan

Abstract

We have created an automatic pretranslator prototype and proposing a new approach to improve the automatic pretranslator efficiency by parsing the original SQL (Structured Query Language) query and building a regular tree. The new approach is to create custom handlers for specific SQL constructs. The resulting handlers, instead of omitting some SQL constructs at the sorting and aggregation stage, allow them to be processed at the join stage. Joining operations, in contrast to sorting and aggregation operations, are performed in parallel on several nodes of the computing cluster, which significantly increases the productivity of processing a single query. You can additionally improve performance by reducing the amount of data transferred, due to a more accurate selection of the dimensions of DECIMAL fields and lowering the projection operations to the level of sorting and aggregation if lifting them increases the number of intermediate relation fields. A regular tree for a single request can be built in various ways. Finding the most effective regular plan pretranslator requires constructing many plans. A plan is selected from the set in which join operations are performed with the order of intermediate relations with sizes from smallest to largest. The experiments carried out have shown that the execution time of a stream of requests pretranslated by the pretranslator is not more than the execution time of a similar stream, pretranslated manually, which proves its effectiveness.