Tuning Different Types of Complex Queries Using the Appropriate Indexes in Parallel/Distributed Database Systems (original) (raw)

Optimization of Local Parallel Index (Lpi) in Parallel/Distributed Database Systems

International Journal of Geomate

The widespread growth of data has created many problems for businesses, such as delay requests; in this paper, we propose several methods of partitioning an index B*Tree in multi-processor machines in parallel/distributed database systems and collaboration between processors when executing multi-queries. When optimizing, indexing automatically comes to mind; we distinguish two types of indexing: B*Tree and Bitmap. Since the advent of multicore computers (multi processors) parallelism becomes an indispensable part of optimization. Our work will focus on partitioning each table on three parts following indexing key partitioning; each processor will host a partition of the index, and the first processor that will finish will immediately take another partition of the index pending according to the priority. The parallelism will reduce the CPU cost then reduces execution time; collaboration between processors will further reduce these costs.

Performance and scalability of parallel database systems

1994

This dissertation addressed the performance of database operations on parallel systems, emphasizing factors which limit scalability of such applications. Even though algorithms were proposed and discussed in the context of the relational framework, the work developed here is relevant for the performance of systems supporting any other data model, such as object-oriented databases. The join operation is representative of the family of binary matching operators, which include set operators that must be supported by any system regardless of the underlying data model [42]. The techniques developed in this dissertation are not restricted to the join operation and can be applied for other binary matching operators as well. This is also true of sorting, which is used in many other nonnumerical applications besides database processing. Both architectural aspects and the design of algorithms were considered in this work. These two topics cannot be divorced in any work addressing parallel performance, since only with good knowledge of the capabilities of a parallel system can an algorithm be optimally designed to achieve the best capabilities of the system.

Open issues in parallel query optimization

ACM SIGMOD Record, 1996

Parallel database systems combine data management and parallel processing techniques to provide high-performance, high-availability and scalability for data-intensive applications [10, 35]. By exploiting parallel computers, they provide performance at a cheaper price ...

High Performance Parallel DBMS

Parallelism is the key to realizing high performance, scalable, fault tolerant database management systems. With the predicted future database sizes and complexity of queries, the scalability of these systems to hundreds and thousands of processors is essential for satisfying the projected demand. This chapter describes three key components of a high performance parallel database management system. First, data partitioning strategies that distribute the workload of a table across the available nodes while minimizing the overhead of parallelism. Second, algorithms for parallel processing of a join operator.

IRJET- Indexing Strategies for Performance Optimization of Relational Databases

IRJET, 2021

Databases and database management systems have been the backbone of computing world for the past many years. The enterprise, web and cloud computing market is growing bigger in terms of size. It will definitely continue to gain prominence in the coming years. With the standardization and consolidation of information technology systems in most enterprises, the demand for highly scalable, reliable and faster relational database systems is on the rise. The databases are crucial for any enterprise operations and to ensure the operations go on smoothly without any issues, database performance is highly crucial. The high performance of the databases could be very well managed by practicing and adopting good database optimization strategies. Indexing is one of the most important strategy to assure the optimal performance of relational databases. To fix the problem of poor database performance and improve the database performance optimization, indexing strategies are essential. Index is basically a data structure based on one or more columns of the database. With faster data retrieval and minimal disk accesses for each query, indexing strategies emerge as powerful technique for performance optimization of relational databases.

Industrial-strength parallel query optimization: Issues and lessons

Information Systems, 1994

In the industrial context of the EDS project, we have designed and implemented a query optimizer which we have integrated within a parallel database system. The optimizer takes as input a query expressed in ESQL, an extension of SQL with objects and rules, and produces a minimum cost parallel execution plan. Our research agenda has focused on several di cult problems: support of ESQL's advanced features such as path expressions and recursion, modelling of parallel execution spaces and extensibility of the search strategy. In this paper, we give a retrospective on the optimizer project with emphasis on our design goals, research contributions and implementation decisions. We also describe the current optimizer prototype and report on experiments performed with a pilot application. Finally, we present the lessons learned.

Survey of Architectures of Parallel Database Systems

2004

The paper is devoted to the classification, design, and analysis of architectures of parallel database systems. A formalization of the notion “parallel database system” is suggested, which relies on a concept of a virtual machine. Based on this formalization, a new approach to the classification of architectures of parallel database systems is suggested. Requirements to parallel database systems are formulated, which serve as criteria for comparing various architectures. Various classes of architectures of parallel database systems are considered and compared.

Invited Project Review: Industrial-strength parallel query optimization: issues and lessons

1994

In the industrial context of the EDS project, we have designed and implemented a query optimizer which we have integrated within a parallel database system. The optimizer takes as input a query expressed in ESQL, an extension of SQL with objects and rules, and produces a minimum cost parallel execution plan. Our research agenda has focused on several difficult problems: support of ESQL's advanced features such as path expressions and recursion, modelling of parallel execution spaces and extensibility of the search strategy. In this paper, we give a retrospective on the optimizer project with emphasis on our design goals, research contributions and implementation decisions. We also describe the current optimizer prototype and report on experiments performed with a pilot application. Finally, we present the lessons learned.