Earticle

Multi Query Optimization is one of the most important tasks in Relational Database Management System (RBMS) and it becomes common due to high usage of online decision support management systems in every industry nowadays. In multi query optimization, queries are optimized and executed in batches. However, there are many algorithms use to detect and unified common sub-expressions among multiple queries and unified them so that the more encompassing sub- expression is executed and the other sub-expressions are derived from. In this work, multi-query optimization algorithm using heuristics and semantic approaches was proposed and encoded on SQL Server version 10.0.1600 and three queries were used for the experiment between the proposed algorithm and most recent basic Multi Query Optimization Algorithm (Volcano RU). The result of experiment showed that, Proposed Algorithm gave the best plans compared Volcano RU Algorithm, across all three queries and was best for all queries in terms of execution time and CPU time.

Multi Query Optimization is an important process in database and it becomes the commonplace due to the frequent usage of decision support systems in almost all the multinational enterprises. The multiple queries from different users that have been addressed to one schema often have a lot of common sub-expressions and it is the function of the multi query optimization algorithms such as Basic Volcano, Volcano RU and Volcano SH algorithms to optimize such multiple queries together and executes the common operation once and share the output among the queries. In this work, a multi query shareability algorithm which can efficiently detect the common sub- expressions among the multiple queries and share the output among those queries was proposed and algorithm for optimal order of those queries was also proposed. The Algorithm has a time complexity of O(n2 + 9n +6) while the most recent basic algorithm thus Volcano RU Algorithm has O(2n2 +20n +12), both the algorithms have O(n2) time complexity which is quadratic in nature. However, the Proposed Algorithm is more efficient and better than Volcano RU algorithm even if n approach to infinity.

Without multi query optimization, Relational Database Management System for online and analytical decision support systems would have been inefficient and hence unpractical. It is an expensive process because it relies at a great extent on evaluating the different plans (access paths) and choosing an optimal one among them. In Multi Query Optimization, queries are executed in batches and there were many different algorithms acted in such way that, in case some queries have a common sub-expression such a sub- expression is executed once and the output shared. We studied the basic multi query optimization algorithms including Basic Volcano, Volcano-SH and Volcano RU, identified their strengths and weaknesses and recommend strategies for developing new improved multi query optimization algorithm so as to reduce weaknesses and integrate strengths of the different basic multi query algorithms into one efficient algorithm.