Improve Query Performance Research Articles

Optimizing Top-K query processing on GPU using data compression and pre-filtering

In large-scale data analysis, efficient Top-K query processing is critical for numerous applications in science, industry, and society. Traditional approaches often involve substantial data transfer and computational overhead, making it difficult to meet the scalability and efficiency demands of modern datasets. This paper proposes a GPU-accelerated Top-K query processing method that integrates data compression and pre-filtering techniques to address these challenges. By partitioning and compressing data on the host side, it alleviates common PCIe bottlenecks in heterogeneous computing environments. A metadata-driven pre-filtering technique further reduces the data volume processed on the GPU, significantly improving query performance, particularly when handling anti-correlated datasets. Experimental results demonstrate that this method markedly reduces data transfer and processing time, confirming its effectiveness in enhancing the efficiency and scalability of Top-K query processing compared to existing methods.

Creating a Cleaned Dataset for Ease of Use

ObjectiveThe Welsh Longitudinal General Practice (WLGP) dataset contains over 4 billion records. Due to the size and the need to filter the dataset to get the necessary general practice interaction results, query performance is poor. To overcome this, a ‘cleaned’ version of the dataset needed to be created. ApproachAn R package was created that would be run when a new refresh of the WLGP data is provided. Two new tables would be created based on the original WLGP Events table. ResultsA WLGP Cleaned Dataset R Package was produced and creates a reformatted events table and events look up table. The reformatted GP events table reduces the original events table from 14 columns to 8 key columns. The events look up table takes distinct events from the original WLGP events table and produces a new table including columns such as the event description, type and hierarchy levels. Both tables are then linked using an event code ID. ConclusionThe R package now creates a new reformatted events table as well as an events look up table which is run after a WLGP refresh is provided. The new tables are then provisioned to any projects that have access to the dataset. ImplicationsThe new tables has improved query performance for our researchers, providing them with a table with all the necessary information and an easy way to decipher events codes. This has improved the amount of time researchers have spent manipulating and querying the tables.

SSF-CDW: achieving scalable, secure, and fast OLAP query for encrypted cloud data warehouse

Implementing a cloud-based data warehouse to store sensitive or critical strategic data presents challenges primarily related to the security of the stored information and the exchange of OLAP queries between the cloud server and users. Although encryption is a viable solution for safeguarding outsourced data, applying it to OLAP queries involving multidimensional data, measures, and Multidimensional Expressions (MDX) operations on encrypted data poses difficulties. Existing searchable encryption solutions are inadequate for handling such complex queries, which complicates the use of business intelligence tools that rely on efficient and secure data processing and analysis.This paper proposes a new privacy-preserving cloud data warehouse scheme called SSF-CDW which facilitates a secure and scalable solution for an encrypted cloud data warehouse. Our SSF-CDW improves the OLAP queries accessible only to authorized users who can decrypt the query results with improved query performance compared to traditional OLAP tools. The approach involves utilizing symmetric encryption and Ciphertext Policy Attribute-Based Encryption (CP-ABE) to protect the privacy of the dimension and fact data modeled in Multidimensional OLAP (MOLAP). To support efficient OLAP query execution, we proposed a new data cube retrieval mechanism using a Redis schema which is an in-memory database. This technique dynamically compiles queries by disassembling them down into multiple levels and consolidates the results mapped to the corresponding encrypted data cube. The caching of dimensional and fact data associated with the encrypted cube is also implemented to improve the speed of frequently queried data. Experimental comparisons between our proposed indexed search strategy and other indexing schemes demonstrate that our approach surpasses alternative techniques in terms of search speed for both ad-hoc and repeated OLAP queries, all while preserving the privacy of the query results.

UniView: A Unified Autonomous Materialized View Management System for Various Databases

Materialized views (MVs) are critical for improving query performance of database systems, especially in online analytical processing (OLAP) databases. Typically, MVs are maintained by DBAs, which relies on prior knowledge and manual operations. Recently, autonomous solutions are designed for specific databases. However, a data warehouse for OLAP is typically hierarchical, which uses different database engines at different stages. Hence, existing methods have limitations in terms of autonomy and unification to support practical applications. Motivated by these, we develop UniView, a unified autonomous materialized view management system that supports various popular databases, including Spark SQL, PostgreSQL, and ClickHouse. Moreover, we provide a cross-platform web user interface, where users can carry out the process of materialized views and evaluate the optimization performance. In the demonstration, we show that UniView is user-friendly and can achieve superior performance in the practical industry scenarios.

Enhancing Database Query Performance: Analysis of Indexing Techniques

The need to enhance query performance in databases within the contemporary information environment is crucial for ensuring efficient operations across various domains. This paper is dedicated to an analysis of database indexing techniques aimed at understanding their impact on query performance and efficiency. It meticulously examines various types of indexes, including B-trees, hash tables, and textual indexes, analyzing their advantages and limitations. The investigation into the influence of these techniques on query execution time reveals that performance depends on various factors such as query complexity and data volume. Special attention is given to selecting the optimal index type based on specific database needs and characteristics. The research underscores the importance of considering data volume and database structure in choosing effective indexing methods. Additionally, the advantages and disadvantages of different indexing techniques are scrutinized, taking into account their impact on query execution speed and efficiency. Of the study indicate that the correct selection and utilization of appropriate indexing strategies can significantly enhance the performance of database management systems, providing swift and efficient access to information for users. Ultimately, this work makes a significant contribution to understanding and practically applying indexing techniques to improve query performance in databases.

TC-Match: Fast Time-Constrained Continuous Subgraph Matching

Continuously monitoring structural patterns in streaming graphs is a critical task in many real-time graph-based applications. In this paper, we study the problem of time-constrained continuous subgraph matching (shorted as TCSM) over streaming graphs. Given a query graph Q with timing order constraint and a data graph stream G , TCSM aims to report all incremental matches of Q in G for each update of G , where a match should obey both structure constraint (i.e., isomorphism) and timing order constraint of Q. Although TCSM has a wide range of applications, such as cyber-attack detection and credit card fraud detection, we note that this problem has not been well addressed. The state-of-the-art bears the limitations of high index space cost and intermediate result maintenance cost. In this paper, we propose TC-Match, an effective approach to TCSM. First, we design a space and time cost-effective index CSS, which is essentially a k -partite graph structure where a node corresponds to an edge in G. By carefully creating links between nodes, we can encapsulate into CSS the partial embedding and timing order information between edges in G. We theoretically show that CSS has polynomial space and construction time complexities. Second, based on the property of CSS, we develop an efficient incremental matching algorithm with an effective node merging optimization. Extensive experiments show that TC-Match can achieve up to 3 orders of magnitude query performance improvement over the baseline methods, and meanwhile the memory consumption is reduced by 48.7%-86.7%.

OPTIMIZING SQL DATABASES FORBIG DATA WORKLOADS: TECHNIQUES AND BEST PRACTICES

In the era of big data, SQL databases face significant challenges in handling vast volumes of data efficiently. This article explores optimization techniques and best practices for enhancing the performance and scalability of SQL databases in handling big data workloads. The study addresses the significant challenges faced by traditional SQL databases, including scalability issues, performance bottlenecks, resource constraints, and data integration complexities. Through a comprehensive methodology involving literature review, case studies, expert interviews, and performance testing, the research identifies effective strategies such as indexing, partitioning, sharding, and caching. Findings from case studies in e-commerce and financial services sectors demonstrate substantial improvements in query performance and resource utilization, validating the practical benefits of these optimization techniques. The study underscores the importance of a multifaceted approach to database optimization, integrating both theoretical and practical insights to address the complexities of big data environments. By staying informed and adopting the latest optimization strategies, database administrators and IT professionals can ensure their SQL databases remain efficient, scalable, and capable of managing the increasing demands of large-scale data processing, ultimately enabling organizations to derive valuable insights from their data.

ADVANCED QUERY OPTIMIZATION IN SQL DATABASES FOR REAL-TIME BIG DATA ANALYTICS

This study investigates the effectiveness of advanced query optimization techniques in SQL databases, focusing on multi-level indexing, query rewriting, and dynamic query execution plans. The research employs a qualitative approach, gathering data from a variety of SQL databases characterized by large datasets typical of big data environments. Through structured interviews, focus groups, and observational methods, insights from database administrators highlight the practical benefits and challenges associated with implementing these techniques. The findings reveal significant improvements in query performance, with multi-level indexing reducing data retrieval times by approximately 40%, query rewriting decreasing execution times by 35%, and dynamic query execution plans enhancing resource utilization efficiency by 25%. These techniques were also praised for their ease of use, adaptability to different data types and query complexities, and overall reliability. This study contributes to the existing body of knowledge by providing a comprehensive analysis of the practical applications and performance enhancements offered by advanced query optimization methods in SQL databases, underscoring their value in managing large-scale, dynamic data environments.

Privacy-preserving geo-tagged image search in edge–cloud computing for IoT

The Internet of Things (IoT) generates a significant volume of geo-tagged images via surveillance sensors in edge–cloud computing environments. Image search is essential to facilitate information sharing, data analysis, and strategic decision-making. However, outsourced images are typically encrypted for privacy protection, posing a challenge in simultaneously searching for visual and geographical relevance on encrypted images. To address this, this paper proposes an edge intelligence empowered privacy-preserving top-k geo-tagged image search scheme for IoT in edge–cloud computing. The scheme presents a novel single-to-multi searchable encryption method for geo-tagged images that enables multiple users to perform secure nearest neighbor queries on a data source. Additionally, an extended anchor-based position determination method and an inner product-based distance calculation method are designed to enable geo-tagged image similarity calculation on ciphertext. Finally, a secure pruning method is introduced to improve query performance. Experiments are conducted to verify the performance of the scheme in terms of high efficiency and accuracy of the search.

Impact of Columnar Storage Optimizations in Redshift and BigQuery

With the data explosion, companies are utilizing "new age" data warehouses, such as Amazon Redshift and Google BigQuery, to process this information. Space-efficient storage structures (specifically column-oriented storage formats) are the path to improved query performance and lower costs. Columnar storage stores the data sorted by columns and not rows so that only the columns being queried are accessed when a query is made. Fewer disk reads mean faster execution time, and that's good for analytical processing

Reengineering of Proxy Logging Monitoring System in XYZ Institution

This research was conducted to assist the head of the IT Operational & Network Education Division of the XYZ institution to reengineering the internet usage monitoring system. This monitoring system is reengineered so that query performance becomes better and the information presented is easily understood by the user. Reengineering the old system will produce a system model that is more in line with the requirements of the current system. The steps taken in this study, analysis of the monitoring system case domain, data analysis, data visualization reengineering, internet use monitoring system reengineering, software implementation and testing. In addition, testing the performance of the query system and usability testing for users from the new visualization results as the output of this study. Reengineering the internet usage monitoring system is a solution to improve query performance and make it easier for users to understand the information presented

Proposed framework for enhancing integrity technique using distributed query operation

Database growth and Storage problems are main concern for large and small enterprises nowadays, which significantly influences the efficiency of database applications. Database archiving is one of the solutions available for management. Many issues have been identified as a result of the use of archive databases, including the elimination of inactive data from online transaction processing systems (OLTP), performance and integrity management. The aim of this paper is to propose a framework for storing OLTP and archiving data in a distributed database environment as part of an integrated system. Maintaining the integrity between an OLTP database and an archiving database is crucial, but even more critical is the performance of the required query. The main aspect of the proposed framework is that it will not only ensure data integrity for primary and unique keys between OLTP and archive database, but it will also improve query performance by introducing parallel processing and query execution plans to maintain that integrity.

Dynamic Hybrid Fragmentation Method for Multimedia Databases

Multimedia databases store high-volume data, which causes problems in efficient information retrieval, and increases execution costs and response times of the queries. To solve this problem, data fragmentation techniques exist to improve query performance, increase information availability, and efficiently execute more operations accessing less irrelevant data. This article presents a comprehensive review of 34 methods related to hybrid fragmentation and subsequently proposes the design of a hybrid fragmentation method that adapts the scheme according to workload changes to maintain efficient retrieval of multimedia data. The proposed technologies are Java as a programming language, Java Server Faces (JSF) as a framework, MySQL and MongoDB database management systems, and NetBeans as an Integrated Development Environment (IDE), following the UWE methodology (Unified Modeling Language-based Web Engineering).

LMSFC: A Novel Multidimensional Index Based on Learned Monotonic Space Filling Curves

The recently proposed learned indexes have attracted much attention as they can adapt to the actual data and query distributions to attain better search efficiency. Based on this technique, several existing works build up indexes for multi-dimensional data and achieve improved query performance. A common paradigm of these works is to (i) map multi-dimensional data points to a one-dimensional space using a fixed space-filling curve (SFC) or its variant and (ii) then apply the learned indexing techniques. We notice that the first step typically uses a fixed SFC method, such as row-major order and z -order. It definitely limits the potential of learned multi-dimensional indexes to adapt variable data distributions via different query workloads. In this paper, we propose a novel idea of learning a space-filling curve that is carefully designed and actively optimized for efficient query processing. We also identify innovative offline and online optimization opportunities common to SFC-based learned indexes and offer optimal and/or heuristic solutions. Experimental results demonstrate that our proposed method, LMSFC, outperforms state-of-the-art non-learned or learned methods across three commonly used real-world datasets and diverse experimental settings.

SplinterDB and Maplets: Improving the Tradeoffs in Key-Value Store Compaction Policy

A critical aspect of modern key-value stores is the interaction between compaction policy and filters. Aggressive compaction reduces the on-disk footprint of a key-value store and can improve query performance, but can reduce insertion throughput because it is I/O and CPU expensive. Filters can mitigate the query costs of lazy compaction, but only if they fit in RAM, limiting the scalability of queries with lazy compaction. And, with fast storage devices, the CPU costs of querying filters in a lazy compacting system can be significant. In this work, we present Mapped SplinterDB, a key-value store that achieves excellent insertion performance, query performance, space efficiency, and scalability by replacing filters with maplets, space-efficient data structures that act as lossy maps with false positives. Critically, we use quotient maplets, which can be merged and resized without access to the underlying data, enabling us to decouple compaction of the data from compaction of the quotient maplets. Thus Mapped SplinterDB can compact data lazily and quotient maplets aggressively, so that each level has multiple sorted runs of data but only one quotient maplet. Quotient maplets are so small that compacting them aggressively is still cheaper than compacting the (much larger) data lazily, so overall we get the insertion performance of a lazily compacted system. And, since there is only one quotient maplet to query on each level, we get the query performance of an aggressively compacted system. Furthermore, quotient maplets can accelerate queries even when they don't fit in RAM, improving scalability to huge datasets. We also show how to use quotient maplets to estimate when a compaction could resolve a high density of updates, enabling Mapped SplinterDB to perform targeted compactions for space recovery. In our benchmarks, Mapped SplinterDB matches the insertion performance of SplinterDB, a state-of-the-art lazily compacted system, and beats RocksDB, an aggressive compacting system, by up to 9×. On queries, Mapped SplinterDB outperforms SplinterDB and RocksDB by up to 89% and 83%, respectively, and scales gracefully to huge datasets. Mapped SplinterDB is able to dynamically trade update performance for space efficiency, resulting in space overheads on update-heavy workloads as low as 15-61%, whereas RocksDB had 80-117% and SplinterDB had up to 137% space overhead.

Adaptive query compilation in graph databases

Compiling database queries into compact and efficient machine code has proven to be a great technique to improve query performance and exploit characteristics of modern hardware. Particularly for graph database queries, which often execute the exact instructions for processing, this technique can lead to an improvement. Furthermore, compilation frameworks like LLVM provide powerful optimization techniques and support different backends. However, the time for generating and optimizing machine code becomes an issue for short-running queries or queries which could produce early results quickly. In this work, we present an adaptive approach integrating graph query interpretation and compilation. While query compilation and code generation are running in the background, the query execution starts using the interpreter. When the code generation is finished, the execution switches to the compiled code. Our evaluation of the approach using short-running and complex queries show that autonomously switching execution modes helps to improve the runtime of all types of queries and additionally to hide compilation times and the additional latencies of the underlying storage.

An ontology-based approach to designing a NoSQL database for semi-structured and unstructured health data.

With the advent of ICT-based healthcare applications, various formats of health data are generated every day in huge volume. Such data, consisting of unstructured, semi-structured and structured data, has every characteristic of Big data. NoSQL databases are generally preferred for storing such type of health data with the objective of improving query performance. However, for efficient retrieval and processing of Big Health Data and for resource optimization, suitable data models and design of the NoSQL databases are important requirements. Unlike relational databases, no standard methods or tools exist for NoSQL database design. In this work, we adopt an ontology-based schema design approach. We propose that an ontology, which captures the domain knowledge, be used for developing a health data model. An ontology for primary healthcare is described in this paper. We also propose an algorithm for designing the schema of a NoSQL database, keeping in mind the characteristics of the target NoSQL store, using a related ontology, a sample query set, some statistical information of the queries, and performance requirements of the query set. The ontology proposed by us for primary healthcare domain and the above mentioned algorithm along with a set of queries are used for generating a schema targeting MongoDB datastore. The performance of the proposed design is compared with a relational model developed for the same primary healthcare data and the effectiveness of our proposed approach is demonstrated. The entire experiment has been carried out on MongoDB cloud platform.

Efficient Learned Spatial Index With Interpolation Function Based Learned Model

Recently, researchers have demonstrated that learned index can improve query performance while reducing the storage overhead. It potentially offers an opportunity to address the spatial query processing challenges caused by the surge in location-based services. Although several learned indexes have been proposed to process spatial data, the main idea behind these approaches is to utilize the existing one-dimensional learned models, which requires either converting the spatial data into one-dimensional data or applying the learned model on individual dimensions separately. As a result, these approaches cannot fully leverage or take advantage of the information regarding the spatial distribution of the original spatial data. To this end, in our previous work, we proposed a spatial (multi-dimensional) interpolation function based learned model to develop a spatial learned index and designed efficient range and <inline-formula><tex-math notation="LaTeX">$k$</tex-math></inline-formula> NN query strategies over it. However, there are some limitations in the proposed learned model, such as the prediction accuracy and index building time. In this paper, we address the limitations of our previous work and propose a new spatial learned model by employing the characteristics of the spatial interpolation functions and a novel dynamic encoding technique. Detailed experiments are conducted with real-world datasets. The results indicate that our new proposed learned model is better than our previous one in terms of building time, prediction accuracy, and storage overhead simultaneously, and the new learned spatial index is better than the existing learned spatial indexes in query execution time and index building time.

Efficient Processing of k-Hop Reachability Queries on Directed Graphs

Given a directed graph, a k-hop reachability query, u→?kv, is used to check for the existence of a directed path from u to v that has a length of at most k. Addressing k-hop reachability queries is a fundamental task in graph theory and has been extensively investigated. However, existing algorithms can be inefficient when answering queries because they require costly graph traversal operations. To improve query performance, we propose an approach based on a vertex cover. We construct an index that covers all reachability information using a small set of vertices from the input graph. This allows us to answer k-hop reachability queries without performing graph traversal. We propose a linear-time algorithm to quickly compute a vertex cover, S, which we use to develop a novel labeling scheme and two algorithms for efficient query answering. The experimental results demonstrate that our approach significantly outperforms the existing approaches in terms of query response time.

A Novel Approach to Improve the Performance of the Database Storing Big Data with Time Information

Big data is defined as data sets that are too large and/or complex to be processed by classical data processing methods. Big data analysis is essential because it enables more competent business movements, more efficient operations, and higher profits by using the data of institutions and organizations. However, large datasets are difficult to analyze because they are produced quickly, require large storage areas in computer systems, and the diversity of their data. In this study, a new approach using the denormalization method is proposed to accelerate the response time of the database in database systems where large volumes of data containing historical information are stored. Denormalization is defined as the process of adding rows or columns that are not needed to increase the reading performance of the database to the database system that has been normalized. In the proposed approach in this study, a large-volume dataset consisting of real spatial data belonging to Kayseri Metropolitan Municipality, containing temporal information and having approximately 96,000,000 row records, was used. In the proposed approach, the response time of the query is accelerated by recording the time information as numbers to increase the query performance of large volumes of data recorded in date format due to the temporal query process. The performance of the proposed method is compared with the performance of the normalization method using actual data on Microsoft SQL Server and Oracle database systems. The method proposed in the experimental evaluations shows that it works approximately eight times faster. In addition, the experimental results showed that the proposed method improves query performance more than the normalization-based method as the data size increases.