Search

Query optimization remains a crucial element of relational database systems. With rapidly expanding data volumes and an increasing trend of machine-generated queries, the significance of query optimization is only increasing and requires continuous advancements. The objective of the query optimizer is to identify an optimal query execution plan from a vast number of semantically equivalent query plans. The success of this search process depends on the optimal operation of the internal inter-connected components of the query optimizer.In this dissertation, we introduce COMPASS, a novel query optimization paradigm for in-memory databases based on a single type of statistics - Fast-AGMS sketches. While maintaining high accuracy, the highly parallelizable nature of Fast-AGMS empowers the query optimizer to accommodate more complex queries. Subsequently, we redefine the objective of the query optimizer to find a spanning tree with a low cost. Capitalizing on the polynomial time complexity of spanning tree algorithms, we present ESTE, an ensemble spanning tree-based enumeration strategy. ESTE systematically enumerates different parts of the search space, thereby enhancing the robustness of the query optimizer. We believe this perspective enables the application of well-studied spanning-tree algorithms to the field of query optimization. Finally, we address the impact of cardinality estimation errors on query optimizers. Given their inevitability, these errors can cause a domino effect, leading to additional mistakes in the subsequent components of the optimizer. We propose L1-error, a new indicator designed to identify sub-optimal plans. L1-error accounts for the fact that certain estimation errors may have more impact on selecting an optimal plan than others.

Exponentially growing number of workloads has been transferred from on-premises to the cloud environment during last decade. It incurs a constantly increasing load on the monitoring and troubleshooting capabilities of the platforms that host those applications nowadays. Data mining techniques and use of telemetry data have recently become an unavoidable means for tracking the behavior of cloud services. The research effort elaborated in this paper is focused on exploiting real-world data to build an automatic database troubleshooting system that exploits the combination of the more comprehensive statistical data science models and an expert system to perform the root cause analysis. An extensive evaluation study was conducted during the eight-month period with a plethora of Azure SQL production workloads for a varying number of databases. The obtained results confirmed the viability and cost-effectiveness of such an approach at the scale of the cloud.

This chapter presents some technical details about data formats, streaming, optimization of computation, and distributed deployment of optimized learning algorithms. Chapter 22 provides additional optimization details. We show format conversion and working with XML, SQL, JSON, 15 CSV, SAS and other data objects. In addition, we illustrate SQL server queries, describe protocols for managing, classifying and predicting outcomes from data streams, and demonstrate strategies for optimization, improvement of computational performance, parallel (MPI) and graphics (GPU) computing.

Various processes are carried out in the Soil and Fertilizer Department of the Ministry of Agriculture at Granma's province in Cuba, including information management for investment approval. It becomes difficult due to the fact that it is carried out manually and through printed models, leading to insufficiencies in the personnel in charge of recording the information, difficulty in preserving relevant information and delay in the response process. In response to these needs, in this investigation, an informatics system is developed that will enrich the management of the information for the approval of investments of Soils and Fertilizers in Granma. For the development of the system was utilized as methodology of development of software Extreme Programming (XP). PHP 7.3.3 and SQL as server-side, client-side programming languages HTML 5, CSS 3, JavaScript and JQuery 1.2.2. APACHE 2.4.38 web application server, MySQL 5.0.12 as Database manager, CodeIgniter 3.1.10 as Framework and DBDesigner 4 as an aid to database modeling. As a result of investigation, a system validated by means of proofs of software was obtained, which vouch for his correct functioning. Keywords: Informatics System , Management of Information , I nvestments, Soils . doi: https://doi.org/10.36825/RITI.09.19.005

Database education is at an inflection point. With the surge of interest in all things "data", enrollments in traditional database courses are at an all time high. At the same time, the rise of Data Science as a discipline has led to the creation of new courses whose content significantly overlaps that of an introductory database course (e.g. data preparation, cleaning, SQL). Students from all across campus aspire to take data science courses, even with limited Computer Science backgrounds. This juxtaposition of content and proliferation of audiences is causing many database educators to question what we should be teaching in our data-oriented courses, and what resources we should use to teach them.

The savings and loans cooperative are a cooperative that has an important purpose in its reach and services, such as the Permata Ngijo Savings and Loans Cooperative. Activities within the cooperative itself include managing member data as well as savings and loan transactions. In systems that are still manual, such as Microsoft Excel, there is a risk of inaccurate data and takes a long time. This manual system is still less effective because the number of members and transactions continues to grow. Therefore, an application is needed as a more effective data processing and information system for members that is easier to access. This study aims to build an android-based savings and loan application using the hypertext preprocessor (PHP) programming language and my structured query language (MySQL) as data storage. Based on the android application that has been made, it shows that the black box functionality of the application runs as planned, the success rate for users reaches 90%.

In recent years, Apache Spark has become the de facto standard for big data processing. SparkSQL is a module offering support for relational analysis on Spark with Structured Query Language (SQL). SparkSQL provides convenient data processing interfaces. Despite its efficient optimizer, SparkSQL still suffers from the inefficiency of Spark resulting from Java virtual machine and the unnecessary data serialization and deserialization. Adopting native languages such as C++ could help to avoid such bottlenecks. Benefiting from a bare-metal runtime environment and template usage, systems with C++ interfaces usually achieve superior performance. However, the complexity of native languages also increases the required programming and debugging efforts. In this work, we present LotusSQL, an engine to provide SQL support for dataset abstraction on a native backend Lotus. We employ a convenient SQL processing framework to deal with frontend jobs. Advanced query optimization technologies are added to improve the quality of execution plans. Above the storage design and user interface of the compute engine, LotusSQL implements a set of structured dataset operations with high efficiency and integrates them with the frontend. Evaluation results show that LotusSQL achieves a speedup of up to 9× in certain queries and outperforms Spark SQL in a standard query benchmark by more than 2× on average.

Understanding the history and theory behind relational databases helps us make sense of things and avoid common traps. But there are also many times when we need to ignore the theory and build practical solutions. This book assumes you are already familiar with relational databases and SQL; the information provided here is not merely introductory, it is foundational.

Testing is more than simply demonstrating a program is correct. We must constantly challenge ourselves to make ourselves better. Likewise, we must also challenge our programs and our ideas. Testing is how we bring science into our craft and replace guesses and myths with real knowledge about how the world works. Building good tests can take a lot of time and effort, and Oracle databases present unique challenges for building reproducible test cases. Luckily, Oracle provides many tools to help us test, such as SQL, PL/SQL, the data dictionary, dynamic performance views, and much more.

Oracle SQL programming has an epistemic problem. Epistemology is the study of knowledge, or simply asking ourselves what do we know and how do we know it. We don’t need to take a philosophy class before we start programming, but we do need to carefully consider our sources. The Oracle programming culture places too much weight on unreliable epistemologies, such as tradition and scripture, and not enough weight on science and reason. If we listen to the wrong sources, we will learn the wrong lessons.

The most important thing about programming styles is to have one. This chapter shows how traditional SQL styles can prevent us from realizing Oracle SQL’s true potential. This subjective and opinionated chapter finishes the discussion about how to write beautiful SQL statements.

It’s time to discuss the most advanced Oracle features. These features can solve extremely challenging problems; the kind of problems that many developers think are impossible to solve in a database.

Now that we learned how to write advanced SQL statements to retrieve data, it is time to learn how to write advanced SQL statements to change data. Oracle Data Manipulation Language (DML) lets us insert, update, and delete data.

It’s time to use our theories and start building practical SQL tuning solutions. This chapter starts with high-level applications and then drills down through databases and SQL statements, until we get to operations.

Solving Oracle SQL performance issues is the pinnacle of SQL development. Performance tuning requires a combination of all the skills previously discussed in this book. We need to understand the development process (to know why problems happened and weren’t caught sooner), advanced features (to find alternative ways to implement code), and programming styles (in order to understand the code and rewrite it into something better).

SELECT is the most important SQL statement type. Even when we’re changing data, most of the logic will go in the SELECT and WHERE clauses of the statement. Before we can insert, update, or delete a set, we must be able to choose a set.

The data-oriented paradigm has proven to be fundamental for the technological transformation process that characterizes Industry 4.0 (I4.0) so that big data and analytics is considered a technological pillar of this process. The goal of I4.0 is the implementation of the so-called Smart Factory, characterized by Intelligent Manufacturing Systems (IMS) that overcome traditional manufacturing systems in terms of efficiency, flexibility, level of integration, digitalization, and intelligence. The literature reports a series of system architecture proposals for IMS, which are primarily data driven. Many of these proposals treat data storage solutions as mere entities that support the architecture’s functionalities. However, choosing which logical data model to use can significantly affect the performance of the IMS. This work identifies the advantages and disadvantages of relational (SQL) and non-relational (NoSQL) data models for I4.0, considering the nature of the data in this process. The characterization of data in the context of I4.0 is based on the five dimensions of big data and a standardized format for representing information of assets in the virtual world, the Asset Administration Shell. This work allows identifying appropriate transactional properties and logical data models according to the volume, variety, velocity, veracity, and value of the data. In this way, it is possible to describe the suitability of relational and NoSQL databases for different scenarios within I4.0.

This work introduces CGRdb2.0─an open-source database management system for molecules, reactions, and chemical data. CGRdb2.0 is a Python package connecting to a PostgreSQL database that enables native searches for molecules and reactions without complicated SQL syntax. The library provides out-of-the-box implementations for similarity and substructure searches for molecules, as well as similarity and substructure searches for reactions in two ways─based on reaction components and based on the Condensed Graph of Reaction approach, the latter significantly accelerating the performance. In benchmarking studies with the RDKit database cartridge, we demonstrate that CGRdb2.0 performs searches faster for smaller data sets, while allowing for interactive access to the retrieved data.

In the current ongoing crisis, people mostly rely on mobile phones for all the activities, but query analysis and mobile data security are major issues. Several research works have been made on efficient detection of antipatterns for minimizing the complexity of query analysis. However, more focus needs to be given to the accuracy aspect. In addition, for grouping similar antipatterns, a clustering process was performed to eradicate the design errors. To address the above-said issues and further enhance the antipattern detection accuracy with minimum time and false positive rate, in this work, Random Forest Bagging X-means SQL Query Clustering (RFBXSQLQC) technique is proposed. Different patterns or queries are initially gathered from the input SQL query log, and bootstrap samples are created. Then, for each pattern, various weak clusters are constructed via X-means clustering and are utilized as the weak learner (clusters). During this process, the input patterns are categorized into different clusters. Using the Bayesian information criterion, the similarity measure is employed to evaluate the similarity between the patterns and cluster weight. Based on the similarity value, patterns are assigned to either relevant or irrelevant groups. The weak learner results are aggregated to form strong clusters, and, with the aid of voting, a majority vote is considered for designing strong clusters with minimum time. Experiments are conducted to evaluate the performance of the RFBXSQLQC technique using the IIT Bombay dataset using the metrics like antipattern detection accuracy, time complexity, false-positive rate, and computational overhead with respect to the differing number of queries. The results revealed that the RFBXSQLQC technique outperforms the existing algorithms by 19% with pattern detection accuracy, 34% minimized time complexity, 64% false-positive rate, and 31% in terms of computational overhead.

Information access to bibliographic metadata needs to be uncomplicated, as users may not benefit from complex and potentially richer data that may be difficult to obtain. Sophisticated research questions including complex aggregations could be answered with complex SQL queries. However, this comes with the cost of high complexity, which requires for a high level of expertise even for trained programmers. A domain-specific query language could provide a straightforward solution to this problem. Although less generic, it can support users not familiar with query construction in the formulation of complex information needs. In this paper, we present and evaluate SchenQL, a simple and applicable query language that is accompanied by a prototypical GUI. SchenQL focuses on querying bibliographic metadata using the vocabulary of domain experts. The easy-to-learn domain-specific query language is suitable for domain experts as well as casual users while still providing the possibility to answer complex information demands. Query construction and information exploration are supported by a prototypical GUI. We present an evaluation of the complete system: different variants for executing SchenQL queries are benchmarked; interviews with domain-experts and a bipartite quantitative user study demonstrate SchenQL’s suitability and high level of users’ acceptance.

Purpose The purpose of this study is to develop a computerized system that will increase the efficiency and supervision of library activities, thereby providing easy access to library usage for librarians and library users. Design/methodology/approach The researchers use the agile methodology to develop and implement the system. PHP, JavaScript, Hypertext Markup Language, Cascading Style Sheets and SQL are the languages used to create the library management system. Findings The developed system received an overall rating of 4.42 with a descriptive rating of “Excellent.” Originality/value The automated library management system is a computerized system for the public libraries designed in the Philippine setting to make the library’s processes more efficient, reliable and portable especially in times of pandemics.

Order appears in all areas related to mathematics and computer science. Given a set in many cases it is desirable to establish a precedence relation between the elements of the set either total or partial. In this paper, we explore an ordering operator for fuzzy numbers that is based on the Zadeh extension principle. This proposal takes into account the intuition of users of Database Management Systems. Our analysis includes a formal proof of these operator’s properties and examples of the applicability of the operator in representative cases that show its suitability for intuition management. Also, an operator implementation in the Haskell and SQL languages is presented. This allows its evaluation in different contexts.

The paper describes a proposal for a software solution to support the creation of a diet plan for a period of eight weeks. In addition to providing services to end users - patients, nutritionists it is made easier to create a plan, as well as use a database with existing recipes. The technologies used in the development of the application solution are presented, as well as the functionalities that are available. Besides that, the possibilities to improve the application are proposed, with the aim of stabilizing the system, interaction with users and profit opportunities.

SQL injection is a type of database-targeted attack for data-driven applications. It is performed by inserting malicious code in the SQL query to alter and modify its meaning, enabling the attacker...

Many data processing systems allow SQL queries that call user-defined functions (UDFs) written in conventional programming languages. While such SQL extensions provide convenience and flexibility to users, queries involving UDFs are not as efficient as their pure SQL counterparts that invoke SQL’s highly-optimized built-in functions. Motivated by this problem, we propose a new technique for translating SQL queries with UDFs to pure SQL expressions. Unlike prior work in this space, our method is not based on syntactic rewrite rules and can handle a much more general class of UDFs. At a high-level, our method is based on counterexample-guided inductive synthesis (CEGIS) but employs a novel compositional strategy that decomposes the synthesis task into simpler sub-problems. However, because there is no universal decomposition strategy that works for all UDFs, we propose a novel lazy inductive synthesis approach that generates a sequence of decompositions that correspond to increasingly harder inductive synthesis problems. Because most realistic UDF-to-SQL translation tasks are amenable to a fine-grained decomposition strategy, our lazy inductive synthesis method scales significantly better than traditional CEGIS. We have implemented our proposed technique in a tool called CLIS for optimizing Spark SQL programs containing Scala UDFs. To evaluate CLIS, we manually study 100 randomly selected UDFs and find that 63 of them can be expressed in pure SQL. Our evaluation on these 63 UDFs shows that CLIS can automatically synthesize equivalent SQL expressions in 92% of the cases and that it can solve 2.4× more benchmarks compared to a baseline that does not use our compositional approach. We also show that CLIS yields an average speed-up of 3.5× for individual UDFs and 1.3× to 3.1× in terms of end-to-end application performance.

Database security focuses on protecting most organization’s virtual data storage unit and confidential information from malicious threats and external attacks. To keep out data secure, we need to use a role-based access control (RBAC) approach to accurately differentiate access permissions, but SQL queries written by an authorized user have very similar characteristics and are difficult to distinguish. In this paper, we propose a method of optimizing CNN-LSTM neural networks with particle swarm optimization (PSO) to classify the roles in RBAC system. Convolutional neural network (CNN) can extract parsed SQL queries into smaller details and features through an analysis mechanism. Long short-term memory (LSTM) is also suitable for modeling the temporal information of SQL queries to recognize the context of user authorities. PSO repeatedly searches and optimizes the complex hyperparameter space of the CNN-LSTM. Our PSO-based CNN-LSTM neural networks outperform other deep learning and machine learning models in the TPC-E benchmark SQL query statement. Finally, experiments and analysis show the usefulness of PSO and identify the important SQL query features that affect user role classification.

A query is a request for data or information from a database table or a combination of tables. It allows for a more accurate database search. SQL queries are divided into two types, namely, simple queries and complex queries. Complex SQL is the use of SQL queries that go beyond standard SQL by using the SELECT and WHERE commands. Complex SQL queries often involve the use of complex joins and subqueries, where the queries are nested in a WHERE clause. Complex SQL queries can be grouped into two types of queries, namely, Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) queries. In the implementation of complex SQL queries in the NoSQL database, a classification process is needed due to the varying data formats, namely, structured, semi-structured, and unstructured data. The classification process aims to make it easier for the query data to be organized by type of query. The classification method used in this research is the Naive Bayes Classifier (NBC) which is generally often used in text data, and the Support Vector Machine (SVM), which is known to work very well on data with large dimensions. The two methods will be compared to determine the best classification result. The results showed that SVM was 84.61% accurate in terms of classification, and comparatively, NBC was at 76.92%.

Background Interpretations of the electrocardiogram (ECG) are often prepared using software outside the electronic health record (EHR) and imported via an interface as a narrative note. Thus, natural language processing is required to create a computable representation of the findings. Challenges include misspellings, nonstandard abbreviations, jargon, and equivocation in diagnostic interpretations. Objectives Our objective was to develop an algorithm to reliably and efficiently extract such information and map it to the standardized ECG ontology developed jointly by the American Heart Association, the American College of Cardiology Foundation, and the Heart Rhythm Society. The algorithm was to be designed to be easily modifiable for use with EHRs and ECG reporting systems other than the ones studied. Methods An algorithm using natural language processing techniques was developed in structured query language to extract and map quantitative and diagnostic information from ECG narrative reports to the cardiology societies' standardized ECG ontology. The algorithm was developed using a training dataset of 43,861 ECG reports and applied to a test dataset of 46,873 reports. Results Accuracy, precision, recall, and the F1-measure were all 100% in the test dataset for the extraction of quantitative data (e.g., PR and QTc interval, atrial and ventricular heart rate). Performances for matches in each diagnostic category in the standardized ECG ontology were all above 99% in the test dataset. The processing speed was approximately 20,000 reports per minute. We externally validated the algorithm from another institution that used a different ECG reporting system and found similar performance. Conclusion The developed algorithm had high performance for creating a computable representation of ECG interpretations. Software and lookup tables are provided that can easily be modified for local customization and for use with other EHR and ECG reporting systems. This algorithm has utility for research and in clinical decision-support where incorporation of ECG findings is desired.

The number of databases as well as their size and complexity is increasing. This creates a barrier to use especially for non-experts, who have to come to grips with the nature of the data, the way it has been represented in the database, and the specific query languages or user interfaces by which data are accessed. These difficulties worsen in research settings, where it is common to work with many different databases. One approach to improving this situation is to allow users to pose their queries in natural language. In this work we describe a machine learning framework, Polyglotter, that in a general way supports the mapping of natural language searches to database queries. Importantly, it does not require the creation of manually annotated data for training and therefore can be applied easily to multiple domains. The framework is polyglot in the sense that it supports multiple different database engines that are accessed with a variety of query languages, including SQL and Cypher. Furthermore Polyglotter supports multi-class queries. Good performance is achieved on both toy and real databases, as well as a human-annotated WikiSQL query set. Thus Polyglotter may help database maintainers make their resources more accessible.

Exploring the sharing opportunities among multiple stream queries is crucial for high-performance stream processing. Modern stream processing necessitates accelerating multiple queries by utilizing heterogeneous coprocessors, such as GPUs, and this has shown to be an effective method. Emerging CPU-GPU integrated architectures 6integrate CPU and GPU on the same chip and eliminate PCI-e bandwidth bottleneck. Such a novel architecture provides new opportunities for improving multi-query performance in stream processing but has not been fully explored by existing systems. We introduce a stream processing engine, called FineStream, for efficient multi-query window-based stream processing on CPU-GPU integrated architectures. FineStream's key contribution is a novel fine-grained workload scheduling mechanism between CPU and GPU to take advantage of both architectures. Particularly, FineStream is able to efficiently handle multiple queries in both static and dynamic streams. Our experimental results show that 1) on integrated architectures, FineStream achieves an average 52 percent throughput improvement and 36 percent lower latency over the state-of-the-art stream processing engine; 2) compared to the coarse-grained strategy of applying different devices for multiple queries, FineStream achieves 32 percent throughput improvement; 3) compared to the stream processing engine on the discrete architecture, FineStream on the integrated architecture achieves 10.4× price-throughput ratio, 1.8× energy efficiency, and can enjoy lower latency benefits.

Ovaj rad opisuje izradu aplikacije za integraciju ponuda poslova iz više izvora. Opisuje problematiku rastućeg broja web stranica za oglašavanje poslova kao i korištenje tehnologije i njezinu primjenu u ovom radu. Projekt se sastoji od dvije klijentske strane - jedna se bavi dohvatom podataka oglasa sa različitih web stranica koje se pohranjuju u bazu podataka dok druga služi za prikaz tih oglasa iz baze podataka i njihovo filtriranje kako bi se pronašli oglasi koji zanimaju tražitelja zaposlenja. Projekt se također sastoji od backend servera koji poslužuje klijente i komunicira s SQL bazom podataka. Razvoj API servisa i razvoj klijenta popraćen je slikama koda koji su zatim detaljno opisani. This paper describes the creation of an application for integrating job offers from multiple sources. It describes the problem of the growing number of websites for advertising jobs as well as the use of technology and its application in this paper. This project consists of two clients sides – one that deals with retrieving ad data from different web pages that are then stored in the database, while the other serves to display those ads from the database and filter them to find ads that interest the job seeker the most. It also consists of a backend server that serves clients and communicates with the sql database. API service development and client development are accompanied by code images that are then described in detail.

We study the combined class of possible keys and functional dependencies over Codd tables under NOT NULL constraints. These constraints can express significant application semantics of data that is compliant with the industry standard SQL. Three major contributions are made. Firstly, the PTIME-complete implication problem is characterized axiomatically by a finite set of Horn rules, algorithmically by a linear time decision procedure, and logically by goal and definite clauses under S-3 logic. Secondly, we establish structural and computational properties of Armstrong tables for the combined class. This includes an algorithm that is guaranteed to compute an Armstrong table of size at most quadratic in that of a minimum-sized table. Thirdly, we conduct an empirical evaluation of a GUI-based implementation of this algorithm. We conclude that Armstrong tables are an effective tool for recognizing domain semantics and should be exploited as early as possible during the acquisition of possible keys and functional dependencies.

Background: Nowadays, the digital world is rising rapidly and becoming very difficult in nature's quantity, diversity, and speed. Recently, there have been two major changes in data management, which are NoSQL databases and Big Data Analytics. While evolving with the diverse reasons, their independent growths balance each other and their convergence would greatly benefit organization to make decisions on-time with the amount of multifaceted data sets that might be semi structured, structured, and unstructured. Though several software solutions have come out to support Big Data analytics on the one hand, on the other hand, there have been several packages of NoSQL database available in the market. Aim and Methods: The main goal of this article is to give comprehension of their perspective and a complete study to associate the future of the emerging several important NoSQL data models. Results: Evaluating NoSQL databases for Big Data analytics with traditional SQL performance shows that NoSQL database is a superior alternative for industry condition need high-performance analytics, adaptability, simplicity, and distributed large data scalability. Conclusion: In this article we conclude with the adoption of NoSQL in various markets.

NoSQL databases are new architectures developed to remedy the various weaknesses that have affected relational databases in highly distributed systems such as cloud computing, social networks, electronic commerce. Several companies loyal to traditional relational SQL databases for several decades seek to switch to the new “NoSQL” databases to meet the new requirements related to the change of scale in data volumetry, the load increases, the diversity of types of data handled, and geographic distribution. This paper develops a comparative study in which the authors will evaluate the performance of two databases very widespread in the field: MySQL as a relational database and MongoDB as a NoSQL database. To accomplish this confrontation, this research uses the Yahoo! Cloud Serving Benchmark (YCSB). This contribution is to provide some answers to choose the appropriate database management system for the type of data used and the type of processing performed on that data.

Query compilation has proven to be one of the most efficient query processing techniques. Despite its fast processing speed, the additional compilation times of the technique limit its applicability. This is because the approach is most beneficial only when the improvements in processing time clearly exceed the additional compilation time. Recently the feasibility of query compilers with very low compilation times has been shown. This may prove query compilation as a merely universal approach. In this article and in the corresponding live demo, we show the capabilities of the ReSQL database system, which uses the intermediate representation Flounder IR to achieve very low compilation times. ReSQL reduces the compilation times from SQL to machine code compared to existing LLVM-based techniques by up to 101.1x for real-world analytic queries.

Data analysis often involves comparing subsets of data across many dimensions for finding unusual trends and patterns. While the comparison between subsets of data can be expressed using SQL, they tend to be complex to write, and suffer from poor performance over large and high-dimensional datasets. In this paper, we propose a new logical operator COMPARE for relational databases that concisely captures the enumeration and comparison between subsets of data and greatly simplifies the expressing of a large class of comparative queries. We extend the database engine with optimization techniques that exploit the semantics of COMPARE to significantly improve the performance of such queries. We have implemented these extensions inside Microsoft SQL Server, a commercial DBMS engine. Our extensive evaluation on synthetic and real-world datasets shows that COMPARE results in a significant speedup over existing approaches, including physical plans generated by today's database systems, user-defined function (UDF), as well as middleware solutions that compare subsets outside the databases., Comment: Extended Version of VLDB 2021 paper

Right-sizing resources for query execution is important for cost-efficient performance, but estimating how performance is affected by resource allocations, upfront, before query execution is difficult. We demonstrate AutoExecutor , a predictive system that uses machine learning models to predict query run times as a function of the number of allocated executors, that limits the maximum allowed parallelism, for Spark SQL queries running on Azure Synapse.

Kubernetes is an open-source container orchestration system for automating container application operations and has been considered to deploy various kinds of container workloads. Traditional geo-databases face frequent scalability issues while dealing with dense and complex spatial data. Despite plenty of research work in the comparison of relational and NoSQL databases in handling geospatial data, there is a shortage of existing knowledge about the performance of geo-database in a clustered environment like Kubernetes. This paper presents benchmarking of PostgreSQL/PostGIS geospatial databases operating on a clustered environment against non-clustered environments. The benchmarking process considers the average execution times of geospatial structured query language (SQL) queries on multiple hardware configurations to compare the environments based on handling computationally expensive queries involving SQL operations and PostGIS functions. The geospatial queries operate on data imported from OpenStreetMap into PostgreSQL/PostGIS. The clustered environment powered by Kubernetes demonstrated promising improvements in the average execution times of computationally expensive geospatial SQL queries on all considered hardware configurations compared to their average execution times in non-clustered environments.

Nowadays many neural networks have been widely employed for semantic parsing problems especially for Structured Query Language (SQL) parsing, which aims at transforming natural language sentences into SQL representations. Selecting proper table headers in SQL tasks is extremely important, and the main cause of performance drop is that attention mechanism in neural models sometimes obtains wrong word-level distribution over headers. In order to obtain better header selection, we propose a match matrix aggregation enhanced SQL parser to consider the outer character-level ROUGE-L match information between the question and headers, then dynamically combine it with the inner generated attention matrix. We also introduce customized BERT and extra semantic information of the question and headers to further improve the performance. The results on two SQL datasets demonstrate that our method achieves an inspiring performance and highly outperforms other state-of-the-art alternatives.

Today, everyone has a personal device to access the web. Every user tries to access the knowledge that they require through internet. Most of the knowledge is within the sort of a database. A user with limited knowledge of database will have difficulty in accessing the data in the database. Hence, there’s a requirement for a system that permits the users to access the knowledge within the database. The proposed method is to develop a system where the input be a natural language and receive an SQL query which is used to access the database and retrieve the information with ease. Tokenization, parts-of-speech tagging, lemmatization, parsing and mapping are the steps involved in the process. The project proposed would give a view of using of Natural Language Processing (NLP) and mapping the query in accordance with regular expression in English language to SQL.

The purpose of this instructional resource is to explain how Structured Query Language (SQL) queries can help auditors to independently retrieve properly formatted data as audit evidence or for further analyses. It demonstrates data extraction using Microsoft (MS) Access, one of the simplest SQL-compliant database software applications. We use a dataset fragment that is extracted from the publicly available enterprise datasets provided by Walton College (University of Arkansas) to run SQL queries as a part of audit investigations. Data extraction is the first step of Extract, Transform, and Load (ETL) and may be time-consuming. We demonstrate how SQL queries can assist with this task, thereby allowing the auditor to begin analysis sooner. This instructional resource can be used to prepare future auditors for the emerging data-rich and technology-driven business environment.

In the last decade, many intelligent interfaces and layers have been suggested to allow the use of relational databases and extraction of the content using only the natural language. However most of them struggle when exposed to new databases. In this article, we present SQLSketch, a sketch-based network for generating SQL queries to address the problem of automatically translate Natural Languages questions to SQL using the related databases schemas. We argue that the previous models that use full or partial sequence-to-sequence structure in the decoding phase can, in fact, have counter-effect on the generation operation and came up with more loss of the context or the meaning of the user question. In this regard, we use a full sketch-based structure that decouples the generation process into many small prediction modules. The SQLSketch is evaluated against GreatSQL, a new cross-domain, large-scale and balanced dataset for the Natural Language to SQL translation task. For a long-term aim of making better models and contributing in adding more improvements to the semantic parsing tasks, we propose the GreatSQL dataset as the first balanced cross-domain corpus that includes 45,969 pairs of natural language questions and their corresponding SQL queries in addition to simplified and well structured ground-truth annotations. We establish results for SQLSketch using GreatSQL dataset and compare the performance against two popular types of models that represent the sequential and partial-sketch based approaches. Experimental result shows that SQLSketch outperforms the baseline models by 13% in exact matching accuracy and achieve a score of 23.9% to be the new state-of-the-art model on GreatSQL.

Interpreting the mechanism of machining errors’ accumulation (MEA) is hard in multistage machining processes. This article addresses a novel three-dimensional graphical model named RFID-MEA for quantitating MEA inspired by the radio-frequency identification (RFID) graphical deduction computing model. RFID-MEA aims at revealing the MEA's mechanism instead regarding the machining process as a traditional “black box” model. First, Taylor's second-order expansion is employed to advise the mathematical representation of MEA and its calculation formula. Then, RFID data are collected to bond the MEA formula's variables with certain data silos in the right type at the right time. After that, raw not only structured query language (NoSQL) data computing is executed in edge nodes for assigning values to variables of the MEA formula accurately. Finally, machining errors are estimated by solving the MEA formula. By illustrating an industrial case, it is shown that the RFID-MEA has better performance in estimating machining errors, as well as with higher reliability and interpretability.