An Approach to Aid Decision-Making by Solving Complex Optimization Problems Using SQL Queries (original) (raw)
Related papers
CSP Techniques for Solving Combinatorial Queries within Relational Databases
Studies in Computational Intelligence, 2009
A combinatorial query is a request for tuples from multiple relations that satisfy a conjunction of constraints on tuple attribute values. Managing combinatorial queries using the traditional database systems is very challenging due to the combinatorial nature of the problem. Indeed, for queries involving a large number of constraints, relations and tuples, the response time to satisfy these queries becomes an issue. To overcome this difficulty in practice we propose a new model integrating the Constraint Satisfaction Problem (CSP) framework into the database systems. Indeed, CSPs are very popular for solving combinatorial problems and have demonstrated their ability to tackle, in an efficient manner, real life large scale applications under constraints. In order to compare the performance in response time of our CSP-based model with the traditional way for handling combinatorial queries and implemented by MS SQL Server, we have conducted several experiments on large size databases. The results are very promising and show the superiority of our method comparing to the traditional one.
Multiple Query Optimization (MQO) is a technique for processing a set of queries as a batch in such a way that shared tasks in these queries are executed only once, resulting in significant savings in the total evaluation cost of the batch. The first phase of MQO requires producing alternative query execution plans so that the shared tasks between queries are identified and maximized. The second phase of MQO is an optimization problem where the goal is selecting exactly one of the alternative plans for each query such that the total execution cost of all queries is minimized. A-star, branch-and-bound, dynamic programming (DP), and genetic algorithm (GA) solutions for MQO have been given in the literature. However, the performance of optimal algorithms, A-star and DP, is not sufficient for solving large MQO problems involving 20-100 queries which are quite common in large shared databases used by on-line services and web servers. In this study, we propose an Integer Linear Programming (ILP) formulation to solve large MQO problem instances and experimentally evaluate its performance. Our results show that ILP greatly outperforms the best existing A-star algorithm for solving MQO.
Integer Linear Programming Approach for the Multiple Query Optimization Problem
Multiple Query Optimization (MQO) is a technique for processing a batch of queries in such a way that shared tasks in these queries are executed only once, resulting in significant savings in the total evaluation. The first phase of MQO requires producing alternative query execution plans so that the shared tasks between queries are identified and maximized. The second phase of MQO is an optimization problem where the goal is selecting exactly one of the alternative plans for each query to minimize the total execution cost of all queries. A-star, branch-and-bound, dynamic programming (DP), and genetic algorithm (GA) solutions for MQO have been given in the literature. However; the performance of optimal algorithms, A-star and DP, is not sufficient for solving large MQO problems involving large number of queries. In this study, we propose an Integer Linear Programming (ILP) formulation to solve the MQO problem exactly for large number of queries and evaluate its performance. Our results show that ILP outperforms the existing A-star algorithm.
Using Heuristics and Genetic Algorithms for Large-scale Database Query Optimization
2007
Distributed database system technology is one of the major developments in information technology area. It will continue to have a very significant impact on data processing in the upcoming years because distributed database systems have many potential advantages over centralized systems for geographically distributed organizations. The continuing interest in distributed database systems in the research community and the marketplace and the introduction of many commercial products indicate that distributed database systems will play a more important role in data processing and eventually will replace centralized systems as the major database technology in the future. The availability of high speed communication networks and, especially, the phenomenal popularity of the Internet and the intranets will undoubtedly speed up the transition process. Some challenging problems must be solved before the full potential benefits of distributed database technology can be realized. Among them is query processing (including query optimization), one of the most important issues in distributed database system design. The query optimization problem in large-scale distributed databases is NP-hard in nature and difficult to solve. In this study, the query optimization problem is reduced to a join ordering problem similar to a variant of traveling salesman problem. We explored several heuristics and a genetic algorithm for solving the join ordering problem. Some computational experiments on these algorithms were conducted and solution qualities compared. The computation experiments show that heuristics and genetic algorithms are viable methods for solving query optimization problem in large scale distributed database systems. 262 issues related to the problem, to model the problem, taking into consideration the most important factors, to propose some solution methods for these models, and, finally, to conduct computational experiments and compare the results to determine the effectiveness and efficiency of the solution techniques (algorithms). We believe that the development of the comprehensive models for the query optimization in large-scale systems, as well as finding effective and/or efficient solution techniques to solve the problems that have been identified are important and will contribute to the use of and research on distributed database technology.
Multiple Query Optimization (MQO) is a technique for processing a batch of queries in such a way that shared tasks in these queries are executed only once, resulting in significant savings in the total evaluation. The first phase of MQO requires producing alternative query execution plans so that the shared tasks between queries are identified and maximized. The second phase of MQO is an optimization problem where the goal is selecting exactly one of the alternative plans for each query to minimize the total execution cost of all queries. A-star, branch-and-bound, dynamic programming (DP), and genetic algorithm (GA) solutions for MQO have been given in the literature. However, the performance of optimal algorithms, A-star and DP, is not sufficient for solving large MQO problems involving large number of queries. In this study, we propose an Integer Linear Programming (ILP) formulation to solve the MQO
Efficient Handling of Relational Database Combinatorial Queries Using CSPs
Lecture Notes in Computer Science, 2008
A combinatorial query is a request for tuples from multiple relations that satisfy a conjunction of constraints on tuple attribute values. Managing combinatorial queries using the traditional database systems is very challenging due to the combinatorial nature of the problem. Indeed, for queries involving a large number of constraints, relations and tuples, the response time to satisfy these queries becomes an issue. To overcome this difficulty in practice we propose a new model integrating the Constraint Satisfaction Problem (CSP) framework into the database systems. Indeed, CSPs are very popular for solving combinatorial problems and have demonstrated their ability to tackle, in an efficient manner, real life large scale applications under constraints. In order to compare the performance in response time of our CSP-based model with the traditional way for handling combinatorial queries and implemented by MS SQL Server, we have conducted several experiments on large size databases. The results are very promizing and show the superiority of our method comparing to the traditional one.
Reusing Relational Queries for Intuitive Decision Optimization
2011 44th Hawaii International Conference on System Sciences, 2011
Decision optimization is used in many applications such as those for finding the best course of action in emergencies. However, optimization solutions require considerable mathematical expertise and effort to generate effective models. On the other hand, reporting applications over databases are more intuitive and have long been established using the mature database query technology. A decision optimization problem can be viewed as an "inverse" of the reporting problem. For example, a report may tell the total cost of a certain supply chain given the various sourcing and transportation options used; the corresponding optimization problem can be to select among all possible sourcing and transportation options to minimize the total cost. Reusing existing reporting queries for decision optimization will achieve the dual goals of taking advantage of past investments and of making decision optimization more intuitive. To realize these goals, this paper addresses two related technical issues with a decision guidance query language (DGQL) framework. The first is to annotate existing queries to precisely express the optimization semantics, and the second is to translate the annotated queries into equivalent mathematical programming (MP) formulation that can be solved efficiently. This paper presents the decision queries with examples, provides formal syntax and semantics to DGQL, describes an implementation method through a reduction to MP formulation. Finally, the paper illustrates via experiments on a prototype system that the optimization tasks done with DGQL compete squarely with expertly generated MP models.
Research Direction in Query Optimization at the University of Maryland
IEEE Data(base) Engineering Bulletin, 1982
Database Engineering Bulletin is a quarterly publication of the IEEE Computer Society Technical Committee on Database Engineering. Its scope of interest includes: data structures and models, access strategies, access control techniques, database architecture, database machines, intelligent front ends, mass storage for very large data bases, distributed database systems and techniques, database software design and implementation, database utilities, database security and related areas. Contribution to the Bulletin is hereby solicited. News items, letters, technical papers, book reviews, meeting previews, summaries, case studies, etc., should be sent to the Editor. All letters to the Editor will be considered for publication unless accompanied by a request to the con trary. Technical papers are unrefereed.
A Software Framework for Solving Combinatorial Optimization Tasks
Due to the major practical importance of combinatorial optimization problems, many approaches for tackling them have been developed. As the problem of intelligent solution generation can be approached with reinforcement learning techniques, we aim at presenting in this paper a programming interface for solving combinatorial optimization problems using reinforcement learning techniques. The advantages of the proposed framework are emphasized, highlighting the potential of using reinforcement learning for solving optimization tasks. An experiment for solving the bidimensional protein folding problem developed using the designed interface is also presented.