SQL Queries Research Articles

SlabCity: Whole-Query Optimization Using Program Synthesis

Query rewriting is often a prerequisite for effective query optimization, particularly for poorly-written queries. Prior work on query rewriting has relied on a set of "rules" based on syntactic pattern-matching. Whether relying on manual rules or auto-generated ones, rule-based query rewriters are inherently limited in their ability to handle new query patterns. Their success is limited by the quality and quantity of the rules provided to them. To our knowledge, we present the first synthesis-based query rewriting technique, SlabCity, capable of whole-query optimization without relying on any rewrite rules. SlabCity directly searches the space of SQL queries using a novel query synthesis algorithm that leverages a new concept called query dataflows. We evaluate SlabCity on four workloads, including a newly curated benchmark with more than 1000 real-life queries. We show that not only can SlabCity optimize more queries than state-of-the-art query rewriting techniques, but interestingly, it also leads to queries that are significantly faster than those generated by rule-based systems.

Database memory forensics: Identifying cache patterns for log verification

Cyberattacks continue to evolve and adapt to state-of-the-art security mechanisms. Therefore, it is critical for security experts to routinely inspect audit logs to detect complex security breaches. However, if a system was compromised during a cyberattack, the validity of the audit logs themselves cannot necessarily be trusted. Specifically, for a database management system (DBMS), an attacker with elevated privileges may temporarily disable the audit logs, bypassing logging altogether along with any tamper-proof logging mechanisms. Thus, security experts need techniques to validate logs independent of a potentially compromised system to detect security breaches.This paper demonstrates that SQL query operations produce a repeatable set of patterns within DBMS process memory. Operations such as full table scans, index accesses, or joins each produce their own set of distinct forensic artifacts in memory. Given these known patterns, we propose that collecting forensic artifacts from a trusted memory snapshot allows one to reverse-engineer query activity and validate audit logs independent of the DBMS itself and outside the scope of a database administrator's privileges. We rely on the fact the queries must ultimately be processed in memory regardless of any security mechanisms they may have bypassed. This work is generalized to all relational DBMSes by using two representative DBMSes, Oracle and MySQL.

QueryBooster: Improving SQL Performance Using Middleware Services for Human-Centered Query Rewriting

SQL query performance is critical in database applications, and query rewriting is a technique that transforms an original query into an equivalent query with a better performance. In a wide range of database-supported systems, there is a unique problem where both the application and database layer are black boxes, and the developers need to use their knowledge about the data and domain to rewrite queries sent from the application to the database for better performance. Unfortunately, existing solutions do not give the users enough freedom to express their rewriting needs. To address this problem, we propose QueryBooster, a novel middleware-based service architecture for human-centered query rewriting, where users can use its expressive and easy-to-use rule language (called VarSQL) to formulate rewriting rules based on their needs. It also allows users to express rewriting intentions by providing examples of the original query and its rewritten query. QueryBooster automatically generalizes them to rewriting rules and suggests high-quality ones. We conduct a user study to show the benefits of VarSQL to formulate rewriting rules. Our experiments on real and synthetic workloads show the effectiveness of the rule-suggesting framework and the significant advantages of using QueryBooster for human-centered query rewriting to improve the end-to-end query performance.

An SQL query generator for cross-domain human language based questions based on NLP model

An SQL query generator for cross-domain human language based questions based on NLP model

RESDSQL: Decoupling Schema Linking and Skeleton Parsing for Text-to-SQL

One of the recent best attempts at Text-to-SQL is the pre-trained language model. Due to the structural property of the SQL queries, the seq2seq model takes the responsibility of parsing both the schema items (i.e., tables and columns) and the skeleton (i.e., SQL keywords). Such coupled targets increase the difficulty of parsing the correct SQL queries especially when they involve many schema items and logic operators. This paper proposes a ranking-enhanced encoding and skeleton-aware decoding framework to decouple the schema linking and the skeleton parsing. Specifically, for a seq2seq encoder-decode model, its encoder is injected by the most relevant schema items instead of the whole unordered ones, which could alleviate the schema linking effort during SQL parsing, and its decoder first generates the skeleton and then the actual SQL query, which could implicitly constrain the SQL parsing. We evaluate our proposed framework on Spider and its three robustness variants: Spider-DK, Spider-Syn, and Spider-Realistic. The experimental results show that our framework delivers promising performance and robustness. Our code is available at https://github.com/RUCKBReasoning/RESDSQL.

MIGA: A Unified Multi-Task Generation Framework for Conversational Text-to-SQL

Conversational text-to-SQL is designed to translate multi-turn natural language questions into their corresponding SQL queries. Most advanced conversational text-to-SQL methods are incompatible with generative pre-trained language models (PLMs), such as T5. In this paper, we present a two-stage unified MultI-task Generation frAmework (MIGA) that leverages PLMs’ ability to tackle conversational text-to-SQL. In the pre-training stage, MIGA first decomposes the main task into several related sub-tasks and then unifies them into the same sequence-to-sequence (Seq2Seq) paradigm with task-specific natural language prompts to boost the main task from multi-task training. Later in the fine-tuning stage, we propose four SQL perturbations to alleviate the error propagation problem. MIGA tends to achieve state-of-the-art performance on two benchmarks (SparC and CoSQL). We also provide extensive analyses and discussions to shed light on some new perspectives for conversational text-to-SQL.

Graphix-T5: Mixing Pre-trained Transformers with Graph-Aware Layers for Text-to-SQL Parsing

The task of text-to-SQL parsing, which aims at converting natural language questions into executable SQL queries, has garnered increasing attention in recent years. One of the major challenges in text-to-SQL parsing is domain generalization, i.e., how to generalize well to unseen databases. Recently, the pre-trained text-to-text transformer model, namely T5, though not specialized for text-to-SQL parsing, has achieved state-of-the-art performance on standard benchmarks targeting domain generalization. In this work, we explore ways to further augment the pre-trained T5 model with specialized components for text-to-SQL parsing. Such components are expected to introduce structural inductive bias into text-to-SQL parsers thus improving the model’s capacity on (potentially multi-hop) reasoning, which is critical for generating structure-rich SQLs. To this end, we propose a new architecture GRAPHIX-T5, a mixed model with the standard pre-trained transformer model augmented by specially-designed graph-aware layers. Extensive experiments and analysis demonstrate the effectiveness of GRAPHIX-T5 across four text-to-SQL benchmarks: SPIDER, SYN, REALISTIC and DK. GRAPHIX-T5 surpasses all other T5-based parsers with a significant margin, achieving new state-of-the-art performance. Notably, GRAPHIX-T5-large reaches performance superior to the original T5-large by 5.7% on exact match (EM) accuracy and 6.6% on execution accuracy (EX). This even outperforms the T5-3B by 1.2% on EM and 1.5% on EX

T-Rex: Optimizing Pattern Search on Time Series

Pattern search is an important class of queries for time series data. Time series patterns often match variable-length segments with a large search space, thereby posing a significant performance challenge. The existing pattern search systems, for example, SQL query engines supporting MATCH_RECOGNIZE, are ineffective in pruning the large search space of variable-length segments. In many cases, the issue is due to the use of a restrictive query language modeled on time series points and a computational model that limits search space pruning. We built T-ReX to address this problem using two main building blocks: first, a MATCH_RECOGNIZE language extension that exposes the notion of segment variable and adds new operators, lending itself to better optimization; second, an executor capable of pruning the search space of matches and minimizing total query time using an optimizer. We conducted experiments using 5 real-world datasets and 11 query templates, including those from existing works. T-ReX outperformed an optimized NFA-based pattern search executor by 6x in median query time and an optimized tree-based executor by 19X.

Presto: A Decade of SQL Analytics at Meta

Presto is an open-source distributed SQL query engine that supports analytics workloads involving multiple exabyte-scale data sources. Presto is used for low-latency interactive use cases as well as long-running ETL jobs at Meta. It was originally launched at Meta in 2013 and donated to the Linux Foundation in 2019. Over the last ten years, upholding query latency and scalability with the hyper growth of data volume at Meta as well as new SQL analytics requirements have raised impressive challenges for Presto. A top priority has been ensuring query reliability does not regress with the shift towards smaller, more elastic container allocation, which requires queries to run with substantially smaller memory headroom and can be preempted at any time. Additionally, new demands from machine learning, privacy, and graph analytics have driven Presto maintainers to think beyond traditional data analytics. In this paper, we discuss several successful evolutions in recent years that have improved Presto latency as well as scalability by several orders of magnitude in production at Meta. Some of the notable ones are hierarchical caching, native vectorized execution engines, materialized views, and Presto on Spark. With these new capabilities, we have deprecated or are in the process of deprecating various legacy query engines so that Presto becomes the single piece to serve interactive, ad-hoc, ETL, and graph processing workloads for the entire data warehouse.

Technical Perspective: Revisiting Runtime Dynamic Optimization for Join Queries in Big Data Management Systems

Query optimization is the process of finding an efficient query execution plan for a given SQL query. The runtime difference between a good and a bad plan can be tremendous. For example, in the case of TPC-H query 5, a query with 5 joins, the difference between the best and the worst plan is more than 10,000×. Therefore, it is vital to avoid bad plans. The dominating factor which differentiates a good from a bad plan is their join order and whether this join order avoids large intermediate results.

SLocator: Localizing the Origin of SQL Queries in Database-Backed Web Applications

In database-backed web applications, developers often leverage Object-Relational Mapping (ORM) frameworks for database accesses. ORM frameworks provide an abstraction of the underlying database access details so that developers can focus on implementing the business logic of the application. However, due to the abstraction, developers may not know where and how a problematic SQL query is generated in the application code, causing challenges in debugging database access problems. In this paper, we propose an approach, called SLocator, which locates where a SQL query is generated in the application code. SLocator is a hybrid approach that leverages both static analysis and information retrieval (IR) techniques. SLocator uses static analysis to infer the database access for every possible path in the control flow graph. Then, given a SQL query, SLocator applies IR techniques to find the control flow path (i.e., a sequence of methods called in an interprocedural control flow graph) whose inferred database access has the highest similarity ranking. We implement SLocator for Java's official ORM API specification (JPA) and evaluate SLocator on seven open source Java applications. We find that SLocator is able to locate the control flow path that generates a SQL query with a Top@1 accuracy ranging from 37.4% to 70% for SQL queries in sessions, and 30.7% to 69.2% for individual SQL queries; and Top@5 ranging from 78.3% to 95.5% for SQL queries in sessions, and 59.1% to 100% for individual SQL queries. We also conduct a study to illustrate how SLocator may be used for locating issues in the database access code.

Conversion of NLP to SQL Using Machine Learning Approach

Abstract: Now days data is increasing rapidly. There are so many new database tools and technologies are rowing, therefore we can store large data, but the problem is that the technology or an interface which can process data and display the data as per the user request is not familiarized with many of the people. It means many people don’t have proper knowledge of handling database. Actually, NLP to SQL conversion is part of machine learning. The main idea of NLP to SQL conversion is to generate SQL query from natural language. This technique is useful for accessing data from database without having prior knowledge of SQL. This technique can be used by many common people. In this system input is simple English text and query is generated by using POS tagger in python. In this project we are going to implement a system which will generate SQL query from natural language.

DsJSON: A Distributed SQL JSON Processor

The popularity of JSON as a data interchange format resulted in big amounts of datasets available for processing. Users would like to analyze this data using SQL queries but existing distributed systems limit their users to only two specific formats, JSONLine and GeoJSON. The complexity of JSON schema makes it challenging to parse arbitrary files in a modern distributed system while producing records with unified schema that can be processed with SQL. To address these challenges, this paper introduces dsJSON, a state-of-the-art distributed JSON processor that overcomes limitations in existing systems and scales to big and complex data. dsJSON introduces the projection tree, a novel data structure that applies selective parsing of nested attributes to produce records that are ready for SQL processors. The key objective of the projection tree is to parse a big JSON file in parallel to produce records with a unified schema that can be processed with SQL. dsJSON is integrated into SparkSQL which enables users to run arbitrary SQL queries on complex JSON files. It also pushes projection and filter down into the parser for full integration between the parser and the processor. Experiments on up-to two terabytes of real data show that dsJSON performs several times faster than existing systems. It can also efficiently parse extremely large files not supported by existing distributed parsers

Course Management System

A course management system is a set of tools that enables the instructor to create online course content and post on the Web. It enables the college or university to provide individual user account to each student. All the user accounts (instructors and students) are password protected. So, the database can be termed as secured. In Course Management System, only administrative section is allowed to create the account, but students and instructor can register with application. After creating the account, the user can view his/her detail by using SQL query. In the existing system, we can store all the record manually that require large manpower & place to store all the records but it does require only a database connectivity to store all the data. It can handle all details about a student. The details include college details, subject details, student personnel details, academic details, exam details. In this we can implement a facility that a faculty can be able to post Document such as word files and PDF in this and student those who have enrolled can able to access all of those documentations. Key Word: User, database, application registry, password authentication, academic details.

Course Outcome and Program Outcome Attainment Calculation

The course outcome & program outcome attainment calculation system is a set of tools that enables the teaching staff to create student batches and enter their exam scores on the Web in order to calculate attainment. It enables the college or university to provide an individual user account to each faculty. All the user accounts (Admin and teaching faculty) are password protected. So, the database can be termed as secured. In CO & PO Attainment Calculation Portal, only the administrative section is allowed to create the account, but faculty can register with the application. After creating the account, the user can view his/her detail by using an SQL query. In the existing system, we can store all the records manually in spreadsheets that require large manpower & place to store all the records but it does require only database connectivity to store all the data. It can handle all details about a college mark sheet system. The details include faculty details, subject details, student personnel details, academic details, and exam details. In this, we can implement a facility that a faculty can be able to create a new batch of students, and other faculties who have mapped to handle subjects, can able to access and enter the marks of that batch of students.

Abstract 3167: The need for structured pathology data in matching human subjects to cancer clinical trial criteria: A methodological approach

Abstract Introduction: Identification of human subjects who qualify for a cancer trial can be challenging, particularly given narrow time frames for enrollment and highly specific criteria for eligibility. We sought to identify patients on a timely basis by matching trial criteria to data present in both the Pathology and clinical information systems. In a separate abstract submitted to the AACR meeting, we described the enrollment outcomes of our effort for two clinical trials. In this companion abstract, we describe the query methods that we employed. Methods: Method #1 involved querying structured data fields in the EMR, using dashboards or SQL queries of relational databases. Method #2 utilized natural language processing (NLP) queries of narrative text in pathology reports. We assessed the useability of each query approach, on a near-real-time basis, against eligibility criteria for open clinical trials at our institution. Results: We examined the first 25 open clinical trials for treatment of solid tumors listed publicly on our institutional website. For each trial, eligibility and query criteria were compiled by two reviewers (DC,YZ); discrepancies between reviewers were resolved by a third reviewer (NY). We found that of the 33 trials examined: 26 trials (79%) could be successfully analyzed by NLP in the pathology report. 2 trials (6%) required SQL query of discrete data fields in clinical databases. 2 trials (6%) required both methods to be used together. 3 trials (9%) required data elements outside the scope of either method. The data fields provided by NLP were: tumor morphology; tumor stage; specimen margin; and tumor receptor status. The data fields provided by SQL were: ICD 10 codes, CPT codes, Chemotherapy, and quantitative lab results. The data fields not accessible by either approach were: tumor resectability and risk calculation. Conclusion: There is no single method that is best for all trials, and availability of a broad set of tools is needed to craft a fitting query for each trial. However, 26 of 33 trials (79%) required NLP to parse eligibility criteria from narrative pathology reports. Cancer clinical trials enrollment may be substantially enhanced by NLP extraction of structured data from pathology reports as part of a near-real-time workflow for identifying eligible Human Subjects. Citation Format: Nalan Yurtsever, Yonah Ziemba, Daniel A. King, Dylan J. Cooper, Sergio Garza, Cheryl B. Schleicher, Sharon S. Fox, James M. Crawford, Joseph Herman. The need for structured pathology data in matching human subjects to cancer clinical trial criteria: A methodological approach [abstract]. In: Proceedings of the American Association for Cancer Research Annual Meeting 2023; Part 1 (Regular and Invited Abstracts); 2023 Apr 14-19; Orlando, FL. Philadelphia (PA): AACR; Cancer Res 2023;83(7_Suppl):Abstract nr 3167.

Software Development Effort Estimation Using Relational Database and Optimized Learning Mechanism

Accurately estimating the cost of software development is crucial for effective project planning and resource allocation. However, traditional cost estimation methods rely heavily on expert judgment and historical data, which can be time consuming and prone to errors. This study suggests a learning-based cost estimation model that leverages relational databases to improve accuracy. The proposed approach estimates project cost based on the effort required to complete software development, which is a key driver of the project cost. The proposed model is designed to address the challenges posed by the variability in open-source development, including variable team sizes, working hours, and expertise. The study collects and pre-processes data from open-source platforms and selects cost drivers and metrics based on logical rules and SQL queries. Moreover, we propose an optimized Artificial Neural Network (ANN) with augmented topology to automate the selection of neuron units, layers, and adjustment of learnable parameters according to the input variables. The proposed model is evaluated on a 100 open-source software repositories dataset and demonstrates its effectiveness in accurately estimating development cost. The system is implemented using Python and evaluated using performance parameters such as MSE, RMSE, MAE, and MMRE. Results indicate that our proposed model offers a more accurate and efficient approach to software cost estimation, especially for freelancers and outsourcing firms. The proposed model has the potential to save time and resources and improve the reliability and accuracy of software cost estimation.

321 Automated Development of a Spine Registry (Autoregistry) using an Interpretable Surgeon-Written Regular Expression Classifier

INTRODUCTION: Clinical registries are critical for modern surgery and underpin outcomes research, reimbursement negotiations, device monitoring, and trial development. Existing approaches to registry construction are labor intensive, costly, and prone to user error. Natural language processing (NLP) techniques combined with broad electronic health record (EHR) datasets can theoretically automate the building and maintenance of registries. METHODS: We utilized a Hadoop datalake consisting of the EHR, billing systems, imaging systems, and other data representing all information generated by an AMC. Using this database and SQL queries, we retrieved every operative note written in the department of neurosurgery over the last decade. Operative notes were parsed using regular expressions constructed by the operative surgeons themselves. A random subset of 100 notes was selected for manual review and analysis. RESULTS: 31,139 operative cases were identified and downloaded. An operative note processor was written by the operating surgeons and required three days of development time with approximately two weeks of validation. In 100 random cases, the regex classifier was on average 98.6% accurate at identifying whether a particular procedure was performed and correctly identified the entire list of defined surgical procedures in 88% of patients. Most errors (64.2%) occurred during the identification of the involved vertebral levels. Operative notes from ancillary specialties for assistive approaches or closures also led to false-positive results. CONCLUSIONS: This study demonstrates the feasibility of automatically generating a spine surgery registry (“autoregistry”) using a Hadoop EHR datalake combined with an interpretable and customizable surgeon-written natural language processing algorithm, which may reduce labor costs and other pitfalls associated with manual registry development.

ZKSQL: Verifiable and Efficient Query Evaluation with Zero-Knowledge Proofs

Individuals and organizations are using databases to store personal information at an unprecedented rate. This creates a quandary for data providers. They are responsible for protecting the privacy of individuals described in their database. On the other hand, data providers are sometimes required to provide statistics about their data instead of sharing it wholesale with strong assurances that these answers are correct and complete such as in regulatory filings for the US SEC and other goverment organizations. We introduce a system, ZKSQL , that provides authenticated answers to ad-hoc SQL queries with zero-knowledge proofs. Its proofs show that the answers are correct and sound with respect to the database's contents and they do not divulge any information about its input records. This system constructs proofs over the steps in a query's evaluation and it accelerates this process with authenticated set operations. We validate the efficiency of this approach over a suite of TPC-H queries and our results show that ZKSQL achieves two orders of magnitude speedup over the baseline.

Save Our Ships SOS-AN Android Application for Safety

Abstract: In this paper, android applications are named SOS for smartphones and web applications, which are developed by java and XML using android studio. The main aim of SOS is to save lives with a single tap. It sends multiple text messages and emails while pressing the SOS button. SOS uses user interface and user experience design by a Machine Learning (ML) algorithms such as Application programming interface(API) to understand SQL queries and it provides a perfect response. Through the Dijkstra algorithm, this application employs Google Maps to trace the nearest way to an available time. SOS has a high level of accuracy in understanding user’s needs and providing a response on time. SOS provides safety for children and girls without parental care.