Execution strategies for SQL subqueries

Optimizing SQL subqueries has been an active area in database research and the database industry throughout the last decades. Pre-vious work has already identified some approaches to efficiently execute relational subqueries. For satisfactory performance, proper choice of subquery execution strategies becomes even more essen-tial today with the increase in decision support systems and auto-matically generated SQL, e.g., with ad-hoc reporting tools. This goes hand in hand with increasing query complexity and growing data volumes – which all pose challenges for an industrial-strength query optimizer. This current paper explores the basic building blocks that Microsoft SQL Server utilizes to optimize and execute relational subqueries. We start with indispensable prerequisites such as detection and removal of correlations for subqueries. We identify a full spectrum of fundamental subquery execution strategies such as forward and reverse lookup as well as set-based approaches, explain the different execution strategies for subqueries implemented in SQL Server, and relate them to the current state of the art. To the best of our knowl-edge, several strategies discussed in this paper have not been pub-lished before. An experimental evaluation complements the paper. It quantifies the performance characteristics of the different approaches and shows that indeed alternative execution strategies are needed in different circumstances, which make a cost-based query optimizer indispen-sable for adequate query performance

Data queries over heterogeneous sources

Dissertação para obtenção do Grau de Mestre em Engenharia InformáticaEnterprises typically have their data spread over many software systems, such as custom made applications, CRM systems like SalesForce, CMS systems, or ERP systems like SAP. In these setting, it is often desired to integrate information from many data sources to accomplish some business goal in an application. Data may be stored locally or in the cloud in a wide variety of ways, demanding for explicit transformation processes to be defined, reason why it is hard for developers to integrate it. Moreover, the amount of external data can be large and the difference of efficiency between a smart and a naive way of retrieving and filtering data from different locations can be great. Hence, it is clear that developers would benefit greatly from language abstractions to help them build queries over heterogeneous data sources and from an optimization process that avoids large and unnecessary data transfers during the execution of queries. This project was developed at OutSystems and aims at extending a real product, which makes it even more challenging. We followed a generic approach that can be implemented in any framework, not only focused on the product of OutSystems

A Labeling DOM-Based Tree Walking Algorithm for Mapping XML Documents into Relational Databases

XML has emerged as the standard format for representing and exchanging data on the World Wide Web. For practical purposes, it is found to be critical to have efficient mechanisms to store and query XML data, as well as to exploit the full power of this new technology. Several researchers have proposed to use relational databases to store and query XML data. With the understanding the limitations of current approaches, this thesis aims to propose an algorithm for automatic mapping XML documents to RDBMS with XML-API as a database utility. The algorithm uses best fit auto mapping technique, and dynamic shredding, of a specified selected XML document type (datacentric, document-centric, and mixed documents).e. The propose algorithm use DOM(Data Object Model) as a warehouse and stack as a data structure to mapping the XML document into relational database and reconstructing the XML document from the relational database. The experiment study show that the algorithm mapping document and reconstructing it again well. Finally, the algorithm compare with other algorithms the result is good in time and efficiency, also the algorithm complexity is O(11n+2)

WEB recommendations for E-commerce websites

In this part of the thesis we have investigated how the navigation utilizing web recommendations can be implemented on the e-commerce websites based on integrated data sources. The integrated e-commerce websites are an interesting use case for web recommendations. One of the reasons for this interest is that many modern, large and economically successful e-commerce websites follow the integrated approach. Another reason is that especially in the integrated environment, due to the lack of the pre-defined semantic connections between the data, the web recommendations step forward as means of enabling user navigation. In this chapter we have presented the architecture for the websites based on integrated data sources named EC-Fuice. We have also presented the prototypical implementation of our architecture which serves as a proof-of-concept and investigated the challenges of creating navigation on an integrated website. The following issues were addressed in this part of the thesis: Combination of several state-of-the-art tools and techniques in the fields of databases, data integration, ontology matching and web engineering into one generic architecture for creating integrated websites. Comparative experiments with several techniques for instance matching (also known as record linkage or duplicate detection). Investigation on using the ontology matching to facilitate the instance matching. Comparative experiments with several techniques for ontology matching. Investigations on the instance-based ontology matching and the possibilities for combining instance-based ontology matching with other techniques for ontology matching. Investigation of the possibilities to improve user navigation in the integrated data environment with different types of web recommendations. Review of the related work in the fields of data integration and ontology matching and discussion of the contact points between the research described here and other related projects. The main contributions of the research described in this part of the thesis are the EC-Fuice architecture, the novel method for matching e-commerce ontologies based on combination of instance information and metadata information, the experimental results of ontology and instance matching performed by different matching algorithms and the classification of the types of recommendations which can be used on an integrated e-commerce website

Database Queries in Java

In conventional programming languages like Java, the interface for accessing databases is often inelegant. Typically, an entire separate database query language must be embedded inside a conventional programming language for programmers to access the full power and speed of a database. Programmers, though, prefer working entirely from within their conventional programming languages, both for general-purpose computation and for database access. This thesis explores how database operations can be expressed using the existing syntax of conventional programming languages. Programmers are able to write all their code –both general purpose code and database access code– in a single language. To run these database operations efficiently though, algorithms are needed for finding these database operations and optimizing them. This thesis focuses on techniques that can be easily adopted because they do not require changes to existing compilers. Three systems have been developed: Queryll, JReq, and HadoopToSQL. Each system examines the problem from the context of functional-style code, imperative-style code, and MapReduce-style code respectively