When temporary results meet intermediate index: An optimization technique of procedural SQL query processing

In modern data-intensive applications, optimizing procedural SQL queries in imperative programs is challenging because of inefficient access to intermediate results that are stored in temporary memory. Both the traditional and machine learning-based optimization techniques often fail to create indexes on intermediate results because they focus on previously stored base tables and declarative queries. However, indexing on intermediate results can be a potential solution to further reducing the cost of processing dependent queries. To the best of our knowledge, no existing work has considered reducing the processing cost of dependent queries that rely on intermediate results. Inspired by this issue, we introduce the intermediate index, a temporary index created on intermediate results within the scope of a single program execution. Leveraging the intermediate index, we propose a procedural SQL query optimization technique called AutoCox that identifies and evaluates the benefits of indexes using a novel what-if analysis method. AutoCox dynamically determines the need for indexing based on the producer–consumer relationships, cardinality, and selectivity of intermediate results, accounting for both index creation overhead and runtime reuse. AutoCox ensures that intermediate indexes hold up-to-date data, which are created only when beneficial and automatically dropped afterward. Experimental results show that our approach significantly outperforms existing methods, achieving a magnitude of 67% cost reduction of dependent queries and 261× speedups of the imperative program using a workload. It indicates that we effectively bridge a critical gap in the optimization of procedural SQL queries in relational big data processing environments.