Ling Chen, Yan Lin, Jingchang Wang et al. · Concurrency and Computation Practice and Experience · 2018
Summary In the past few years, executing high‐concurrency queries with interactive SQL query engines on Hadoop has become an important activity for many organizations. However, these systems do not adopt Multi‐Query Optimization (MQO) to accelerate the process. There are two major concerns. Firstly, traditional MQO researches assume that multiple queries have high similarity. However, these systems usually serve a variety of applications. Although queries from the same application have high similarity, queries from different applications may have low similarity, so using traditional MQO will be inefficient and time consuming. Secondly, integrating MQO may lead to lots of system modifications. To integrate MQO into interactive SQL query engines on Hadoop efficiently, a query grouping–based MQO framework is proposed. A lightweight mechanism is used to represent SQL queries, on which a grouping method is exploited to speed up the optimization process. A cost model is integrated to estimate the execution cost of interactive SQL query engines on Hadoop. By using the proposed framework, we modify Impala system to support MQO, and the experimental results on TPC‐DS show significant performance improvements.
Cited by 4
Khalifullah, Feroze · SHAREOK (University of Oklahoma) · 2014
Computer Science
Cited by 0Open access
Edy Budiman, Andi Tejawati, Ummul Hairah · International Journal of Recent Technology and Engineering (IJRTE) · 2020
Borneo bioinformatics portal test is a critical element of SQA and represents a comprehensive review of specifications, design and coding. The test represents an abnormality in the development of the portal. A series of tests systematically reveals several different types of errors. This study aims to evaluate the performance and optimization of Borneo’s Bioinformatics portal with a series test activities using the Web Performance Optimization methodology. Testing query performance with measuring the response time and page loading timings from the object relationship mapping (ORM) model Laravel PHP framework in offline and online. For optimization, we set a pre-test and post-test scenario to evaluate the efficiency performance test results. The results study found that the query relation model, parsing script (javaScript and CSS), service scale and dimension images in the interaction process to the database are the dominant resources affecting the performance of the Bioinformatics portal. Performance optimization through determining the appropriate query relation model, minify and defer parsing script or combine images using CSS sprites to reduce scala image.
Cited by 1Open access
Verlaine, Lionel · OpenGrey (Institut de l'Information Scientifique et Technique) · 1986
Cited by 0
David Kung‐Chun Chiu, Y. C. Ho, Bernstein,P. A. · Defense Technical Information Center (DTIC) · 1979
This report examines the formulation of the query processing problem as an optimization problem. The real problem, which involves database states as parameters, is an intractable one. The problem can, however, be transformed into equivalent and simplified versions by making certain assumptions. In order to state these assumptions and the problem transformation on a formal basis, the notion of characterization is introduced. A characterization is a mapping of the database state space into a simplified parameter space. Meaningful simplification of the original problem is possible only when suitable characterizations can be found. The problem formulations in several important papers are reviewed and a detailed example is represented to illustrate some ideas.
Cited by 0
Nawshad Farruque, University of Lethbridge. Faculty of Arts and Science · Open ULeth Scholarship (OPUS) (University of Lethbridge) · 2014
Applications exist today that require the management of distributed spatial data.Since spatial data is more complex than non-spatial data, performing queries on it requires more local processing (i.e.CPU and I/O) time.Also, due to geographical distribution, data transmission costs must be considered.To reduce these costs, one can employ a distributed spatial semijoin as it eliminates unnecessary objects before their transmission to other sites and the query site.Most existing work propose different representations of the distributed spatial semijoin between two sites only, with very few works exploring its use for processing a query involving more than two sites.In this thesis, we propose both new approaches for representing the spatial semijoin in a distributed setting, and their use for processing a distributed query consisting of any number of sites.Two strategies are proposed for compactly representing the spatial semijoin that reduce both the data transmission and local processing (CPU+I/O) costs when applied in a distributed spatial query.A Global Encompassing Minimum Bounding Rectangle (GEMBR) is utilized, which is partitioned, mapped and applied in two different ways to approximate the objects in a spatial joining attribute.The first is partition indices, while the second is a bit array representation.Then each spatial semijoin is applied in a multi-site distributed spatial query processing strategy.In addition, the two-site spatial semijoin is extended to handle multiple sites so that we have a benchmark strategy for comparison purposes.We have tested the query processing algorithms for four sites, which are a part of an actual working distributed system.The algorithms are compared with respect to data transmission cost, CPU time, I/O time and false positive results.The algorithms are superior in many cases at optimizing the above criteria.The bit array representation, which is called Bloom Filter Based Spatial Semijoin (BFSJ), is evaluated with respect to different filter (Thesis) degree in Computer Science from the Department of Mathematics and Computer Science at The University of Lethbridge.I am also grateful to my committee members, Dr. Yllias Chali and Dr
Cited by 0Open access
Juopperi, Tatiana · Theseus (Ammattikorkeakoulujen) · 2024
The subject of the research is AI-driven SQL query optimization techniques. The study aims to explore the role and capabilities of Artificial Intelligence in SQL query optimization as one of the methods of tuning and optimization. The research involves both quantitative and qualitative methods. This choice ensures a compre-hensive understanding of AI-driven SQL query optimization techniques. While direct contact with specific professionals was not established, information was collected from publicly available sources such as academic publications, industry blogs, forums, and documentation. The research first explored traditional SQL query optimization methods and their limitations. Then, it explored available AI techniques which can be used to improve query performance. The study included creating and processing SQL queries and execution plan data. The thesis explores how well AI models perform compared to traditional optimization methods in terms of query speed and quality. Additionally, were taken attention of risks with which face users if used both methods. Key findings highlight the role of indexes in traditional SQL query optimization and importance of query execution plans. Additionally, AI-driven techniques such as ChatGPT 3.5 SQL query opti-mization, cost optimization, and AI-driven detection of SQL injection risks. A review of existing AI tools for SQL query optimization is also provided. The research sheds light on the implications of AI-driven SQL query optimization for database administrators and developers, highlighting potential challenges and opportunities in integrating these techniques into existing systems.
Cited by 0Open access
Jeffrey Tao, Yimeng Zeng, Haydn T. Jones et al. · ArXiv.org · 2026
Benchmark workloads are extremely important to the database management research community, especially as more machine learning components are integrated into database systems. Here, we propose a Bayesian optimization technique to automatically search for difficult benchmark queries, significantly reducing the amount of manual effort usually required. In preliminary experiments, we show that our approach can generate queries with more than double the optimization headroom compared to existing benchmarks.
Cited by 0Open access
Richard L. Cole, Goetz Graefe · 1994
Traditional query optimizers assume accurate knowledge of run-time parameters such as selectivities and resource availability during plan optimization, i.e., at compile time. In reality, however, this assumption is often not justified. Therefore, the “static” plans produced by traditional optimizers may not be optimal for many of their actual run-time invocations. Instead, we propose a novel optimization model that assigns the bulk of the optimization effort to compile-time and delays carefully selected optimization decisions until run-time. Our previous work defined the run-time primitives, “dynamic plans” using “choose-plan” operators, for executing such delayed decisions, but did not solve the problem of constructing dynamic plans at compile-time. The present paper introduces techniques that solve this problem. Experience with a working prototype optimizer demonstrates (i) that the additional optimization and start-up overhead of dynamic plans compared to static plans is dominated by their advantage at run-time, (ii) that dynamic plans are as robust as the “brute-force” remedy of run-time optimization, i.e., dynamic plans maintain their optimality even if parameters change between compile-time and run-time, and (iii) that the start-up overhead of dynamic plans is significantly less than the time required for complete optimization at run-time. In other words, our proposed techniques are superior to both techniques considered to-date, namely compile-time optimization into a single static plan as well as run-time optimization. Finally, we believe that the concepts and technology described can be transferred to commercial query optimizers in order to improve the performance of embedded queries with host variables in the query predicate and to adapt to run-time system loads unpredictable at compile time.
Cited by 181Open access
Timos Sellis, Leonard Shapiro · 1985
Article Free Access Share on Optimization of extended database query languages Authors: Timos K. Sellis Department of Electrical Engineering and Computer, Science, Unversity of California, Berkeley, CA Department of Electrical Engineering and Computer, Science, Unversity of California, Berkeley, CAView Profile , Leonard Shapiro Department of Computer Science, North Dakota, State University, Fargo, ND Department of Computer Science, North Dakota, State University, Fargo, NDView Profile Authors Info & Claims SIGMOD '85: Proceedings of the 1985 ACM SIGMOD international conference on Management of dataMay 1985 Pages 424–436https://doi.org/10.1145/318898.318993Published:01 May 1985Publication History 13citation279DownloadsMetricsTotal Citations13Total Downloads279Last 12 Months15Last 6 weeks5 Get Citation AlertsNew Citation Alert added!This alert has been successfully added and will be sent to:You will be notified whenever a record that you have chosen has been cited.To manage your alert preferences, click on the button below.Manage my Alerts New Citation Alert!Please log in to your account Save to BinderSave to BinderCreate a New BinderNameCancelCreateExport CitationPublisher SiteeReaderPDF
Cited by 14
Volker Markl, Vijayshankar Raman, David Simmen et al. · 2004
Virtually every commercial query optimizer chooses the best plan for a query using a cost model that relies heavily on accurate cardinality estimation. Cardinality estimation errors can occur due to the use of inaccurate statistics, invalid assumptions about attribute independence, parameter markers, and so on. Cardinality estimation errors may cause the optimizer to choose a sub-optimal plan. We present an approach to query processing that is extremely robust because it is able to detect and recover from cardinality estimation errors. We call this approach "progressive query optimization" (POP). POP validates cardinality estimates against actual values as measured during query execution. If there is significant disagreement between estimated and actual values, execution might be stopped and re-optimization might occur. Oscillation between optimization and execution steps can occur any number of times. A re-optimization step can exploit both the actual cardinality and partial results, computed during a previous execution step. Checkpoint operators (CHECK) validate the optimizer's cardinality estimates against actual cardinalities. Each CHECK has a condition that indicates the cardinality bounds within which a plan is valid. We compute this validity range through a novel sensitivity analysis of query plan operators. If the CHECK condition is violated, CHECK triggers re-optimization. POP has been prototyped in a leading commercial DBMS. An experimental evaluation of POP using TPC-H queries illustrates the robustness POP adds to query processing, while incurring only negligible overhead. A case-study applying POP to a real-world database and workload shows the potential of POP, accelerating complex OLAP queries by almost two orders of magnitude.
Cited by 230
Hongjun Lü, Beng Chin Ooi, Cheng-Hian Goh · 2002
Despite the interest in multidatabase systems, research in multidatabase query optimization (MQO) has been scarce. Many researchers perceive the difficulty to be the lack of reliable cost estimates for autonomous component databases. Consequently, some have suggested that this problem degenerates to a distributed query optimization (DQO) problem once cost model coefficients for component databases are found. The authors argue that autonomy and heterogeneity of component databases give rise to a number of issues which make MQO a distinct problem from DQO. Consequently, existing solutions for DQO need to be re-evaluated in the light of these issues. The design of a multidatabase query optimizer, which accounts for the issues highlighted, is discussed. This provides a framework within which further research in MQO can be carried out.< <ETX xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">></ETX>
Cited by 31
A.B.M. Rubaiyat Islam Sadat · 2010
Cited by 0
Dongxing Tang · 1997
Datalog, a query language, is a version of Prolog suitable for database systems. It has been shown that linear datalog programs are easier to evaluate than nonlinear datalog programs.
In this work, we first consider the possibility and hardness of some special linearizations of a subclass of datalog programs, called n-linear sirups for arbitrary n. We then study the possibility of qualitatively approximating a nonlinearizable datalog predicate using a linear datalog program.
Finally, we consider bow to simplify a datalog program under a set of functional dependencies (fd's). In particular, given a linear sirup and a set of fd's over EDB predicates, we study how to make the recursion in the linear sirup less intensive.
Cited by 1
Pryscila Barvik Guttoski, Marcos Sfair Sunyé, Fabiano Silva · 2007
This paper describes an implementation of Kruskal's algorithm in query optimization process for generation of a near optimal execution query tree. The open source Post- greSQL DBMS was used in the experiments since its query optimization is performed by dynamic programming and genetic algorithm, which allows a concrete comparison with our approach. Kruskal's algorithm presents some advantages like its simplified code, its polynomial-time execution and the reduced search space to generate only one query tree, that will be the optimal tree. In most experiments, Kruskal's algorithm got the expected results in almost the same time as the results achieved by default Post- greSQL's optimization algorithms. The results confirm that Kruskal's algorithm is a feasible method for query optimization.
Cited by 23
Ming-Chuan Wu · 1999
Bitmaps are popular indexes for data warehouse (DW) applications and most database management systems offer them today. This paper proposes query optimization strategies for selections using bitmaps. Both continuous and discrete selection criteria are considered. Query optimization strategies are categorized into static and dynamic. Static optimization strategies discussed are the optimal design of bitmaps, and algorithms based on tree and logical reduction. The dynamic optimization discussed is the approach of inclusion and exclusion for both bit-sliced indexes and encoded bitmap indexes.
Cited by 30Open access
Ming-Chuan Wu · ACM SIGMOD Record · 1999
Bitmaps are popular indexes for data warehouse (DW) applications and most database management systems offer them today. This paper proposes query optimization strategies for selections using bitmaps. Both continuous and discrete selection criteria are considered. Query optimization strategies are categorized into static and dynamic. Static optimization strategies discussed are the optimal design of bitmaps, and algorithms based on tree and logical reduction. The dynamic optimization discussed is the approach of inclusion and exclusion for both bit-sliced indexes and encoded bitmap indexes.
Cited by 62
Manfred A. Jeusfeld, Martin Staudt · 1991
1 . Deductive object bases are extended database systems which amalgamate structural object-orientation with logical specification. Queries in such a system are regarded both as classes and as deduction rules. Besides a general architecture for query processing in deductive object bases, two specific query optimization techniques are presented: semantic query optimization with structural axioms of the object base, and view maintenance optimization. The approach has been formalized in the language Telos and implemented in the system ConceptBase. 1 This work has been supported in part by ESPRIT BRA 3012 Compulog. A version of this paper also appears in Freytag, Maier, Vossen (eds.): Query processing in objectoriented, complex-object, and nested relation databases, Morgan Kaufmann, 1992. 1 1. Introduction Traditionally, databases are systems for storing and accessing large amounts of shared persistent data in a secure way. Database research has always been concerned with pro...
Cited by 34
Pongtawat Chippimolchai, Kiyoshi Akama, Vilas Wuwongse · Journal of Advanced Computational Intelligence and Intelligent Informatics · 2006
We developed a semantic query optimization framework for deductive databases based on equivalent transformation (ET) rules. ET rules, prepared from the semantic knowledge of databases, such as integrity constraints, transform given queries into syntactically different but semantically equivalent and more efficient forms. We formally prove the correctness of query transformations by ET rules. For efficiency, we propose a two-phase heuristic-based strategy to guide query transformations and introduce a condition-based control strategy to prevent unwanted, unnecessary transformations. We give examples demonstrating the possible optimization.
Cited by 0Open access
Nikita Vasilenko, Alexander Demin, Denis Ponomaryov · The Bulletin of Irkutsk State University Series Mathematics · 2025
The principal component of conventional database query optimizers is a cost model that is used to estimate expected performance of query plans. The accuracy of the cost model has direct impact on the optimality of execution plans selected by the optimizer and thus, on the resulting query latency. Several common parameters of cost models in modern DBMS are related to the performance of CPU and I/O and are typically set by a database administrator upon system tuning. However these performance characteristics are not stable and therefore, a single point estimation may not suffice for all DB load regimes. In this paper, we propose an Adaptive Cost Model (ACM) which dynamically optimizes CPU- and I/O-related plan cost parameters at DB runtime. By continuously monitoring query execution statistics and the state of DB buffer cache ACM adjusts cost parameters without the need for manual intervention from a database administrator. This allows for responding to changes in the workload and system performance ensuring more optimal query execution plans. We describe the main ideas in the implementation of ACM and report on a preliminary experimental evaluation showing 20% end-to-end latency improvement on TPC-H benchmark.
Cited by 1Open access