Exploiting Shared Sub-Expression and Materialized View Reuse for Multi-Query Optimization

IF 6.9 3区 管理学 Q1 COMPUTER SCIENCE, INFORMATION SYSTEMS
Bala Gurumurthy, Vasudev Raghavendra Bidarkar, David Broneske, Thilo Pionteck, Gunter Saake
{"title":"Exploiting Shared Sub-Expression and Materialized View Reuse for Multi-Query Optimization","authors":"Bala Gurumurthy, Vasudev Raghavendra Bidarkar, David Broneske, Thilo Pionteck, Gunter Saake","doi":"10.1007/s10796-024-10506-w","DOIUrl":null,"url":null,"abstract":"<p>Querying in isolation lacks the potential of reusing intermediate results, which ends up wasting computational resources. Multi-Query Optimization (MQO) addresses this challenge by devising a shared execution strategy across queries, with two generally used strategies: <i>batched</i> or <i>cached</i>. These strategies are shown to improve performance, but hardly any study explores the combination of both. In this work we explore such a hybrid MQO, combining batching (Shared Sub-Expression) and caching (Materialized View Reuse) techniques. Our hybrid-MQO system merges batched query results as well as caches the intermediate results, thereby any new query is given a path within the previous plan as well as reusing the results. Since caching is a key component for improving performance, we measure the impact of common caching techniques such as FIFO, LRU, MRU and LFU. Our results show LRU to be the optimal for our usecase, which we use in our subsequent evaluations. To study the influence of batching, we vary the factor - <span>derivability</span> - which represents the similarity of the results within a query batch. Similarly, we vary the cache sizes to study the influence of caching. Moreover, we also study the role of different database operators in the performance of our hybrid system. The results suggest that, depending on the individual operators, our hybrid method gains a speed-up between 4x to a slowdown of 2x from using MQO techniques in isolation. Furthermore, our results show that workloads with a generously sized cache that contain similar queries benefit from using our hybrid method, with an observed speed-up of 2x over sequential execution in the best case.</p>","PeriodicalId":13610,"journal":{"name":"Information Systems Frontiers","volume":"1 1","pages":""},"PeriodicalIF":6.9000,"publicationDate":"2024-06-25","publicationTypes":"Journal Article","fieldsOfStudy":null,"isOpenAccess":false,"openAccessPdf":"","citationCount":"0","resultStr":null,"platform":"Semanticscholar","paperid":null,"PeriodicalName":"Information Systems Frontiers","FirstCategoryId":"94","ListUrlMain":"https://doi.org/10.1007/s10796-024-10506-w","RegionNum":3,"RegionCategory":"管理学","ArticlePicture":[],"TitleCN":null,"AbstractTextCN":null,"PMCID":null,"EPubDate":"","PubModel":"","JCR":"Q1","JCRName":"COMPUTER SCIENCE, INFORMATION SYSTEMS","Score":null,"Total":0}
引用次数: 0

Abstract

Querying in isolation lacks the potential of reusing intermediate results, which ends up wasting computational resources. Multi-Query Optimization (MQO) addresses this challenge by devising a shared execution strategy across queries, with two generally used strategies: batched or cached. These strategies are shown to improve performance, but hardly any study explores the combination of both. In this work we explore such a hybrid MQO, combining batching (Shared Sub-Expression) and caching (Materialized View Reuse) techniques. Our hybrid-MQO system merges batched query results as well as caches the intermediate results, thereby any new query is given a path within the previous plan as well as reusing the results. Since caching is a key component for improving performance, we measure the impact of common caching techniques such as FIFO, LRU, MRU and LFU. Our results show LRU to be the optimal for our usecase, which we use in our subsequent evaluations. To study the influence of batching, we vary the factor - derivability - which represents the similarity of the results within a query batch. Similarly, we vary the cache sizes to study the influence of caching. Moreover, we also study the role of different database operators in the performance of our hybrid system. The results suggest that, depending on the individual operators, our hybrid method gains a speed-up between 4x to a slowdown of 2x from using MQO techniques in isolation. Furthermore, our results show that workloads with a generously sized cache that contain similar queries benefit from using our hybrid method, with an observed speed-up of 2x over sequential execution in the best case.

Abstract Image

利用共享子表达式和物化视图重用实现多查询优化
孤立查询缺乏重复使用中间结果的潜力,最终会浪费计算资源。多查询优化(Multi-Query Optimization,MQO)通过设计一种跨查询的共享执行策略来应对这一挑战,通常使用两种策略:批处理或缓存。这些策略都被证明可以提高性能,但几乎没有研究探讨过如何将这两种策略结合起来。在这项工作中,我们探索了这样一种混合 MQO,它结合了批处理(共享子表达式)和缓存(物化视图重用)技术。我们的混合 MQO 系统合并了批处理查询结果,并缓存了中间结果,从而为任何新查询提供了先前计划中的路径,并重复使用了查询结果。由于缓存是提高性能的关键因素,我们衡量了 FIFO、LRU、MRU 和 LFU 等常用缓存技术的影响。我们的结果表明,LRU 对于我们的用例来说是最佳的,我们在随后的评估中使用了它。为了研究批处理的影响,我们改变了表示查询批处理中结果相似性的因素--可衍生性。同样,我们还改变了缓存大小,以研究缓存的影响。此外,我们还研究了不同数据库操作员对混合系统性能的影响。结果表明,与单独使用 MQO 技术相比,根据不同的操作员,我们的混合方法可以提高 4 倍的速度,降低 2 倍的速度。此外,我们的结果表明,使用我们的混合方法,缓存大小适中且包含类似查询的工作负载将受益匪浅,在最佳情况下,观察到的速度比顺序执行提高了 2 倍。
本文章由计算机程序翻译,如有差异,请以英文原文为准。
求助全文
约1分钟内获得全文 求助全文
来源期刊
Information Systems Frontiers
Information Systems Frontiers 工程技术-计算机:理论方法
CiteScore
13.30
自引率
18.60%
发文量
127
审稿时长
9 months
期刊介绍: The interdisciplinary interfaces of Information Systems (IS) are fast emerging as defining areas of research and development in IS. These developments are largely due to the transformation of Information Technology (IT) towards networked worlds and its effects on global communications and economies. While these developments are shaping the way information is used in all forms of human enterprise, they are also setting the tone and pace of information systems of the future. The major advances in IT such as client/server systems, the Internet and the desktop/multimedia computing revolution, for example, have led to numerous important vistas of research and development with considerable practical impact and academic significance. While the industry seeks to develop high performance IS/IT solutions to a variety of contemporary information support needs, academia looks to extend the reach of IS technology into new application domains. Information Systems Frontiers (ISF) aims to provide a common forum of dissemination of frontline industrial developments of substantial academic value and pioneering academic research of significant practical impact.
×
引用
GB/T 7714-2015
复制
MLA
复制
APA
复制
导出至
BibTeX EndNote RefMan NoteFirst NoteExpress
×
提示
您的信息不完整,为了账户安全,请先补充。
现在去补充
×
提示
您因"违规操作"
具体请查看互助需知
我知道了
×
提示
确定
请完成安全验证×
copy
已复制链接
快去分享给好友吧!
我知道了
右上角分享
点击右上角分享
0
联系我们:info@booksci.cn Book学术提供免费学术资源搜索服务,方便国内外学者检索中英文文献。致力于提供最便捷和优质的服务体验。 Copyright © 2023 布克学术 All rights reserved.
京ICP备2023020795号-1
ghs 京公网安备 11010802042870号
Book学术文献互助
Book学术文献互助群
群 号:481959085
Book学术官方微信