Increasing Database Performance through Optimizing Structure Query Language Join Statement

Abstract

Problem statement: A join statement is a select statement with more than table in the FROM clause. A join predicate is a predicate in the WHERE clause that combines the columns of two of the tables in the join. Any database gives you the ability to join various tables together through different types of joins, resulting large number of rows to process. Query language can be used to join these tables and as it is well known query language should be declarative, so we can write alternative formulas to perform join statements. Different formulas provide variation in performance. Approach: This research presented a transparent middle layer between application interface front end and database back end. Results: The responsibilities of this layer were catching the SQL commands sent by application before reaching the database then examining these commands to see if they join more than one table, after that rewriting the SQL command taking into consideration the order of executing join predicates and none join predicates. This research focused on rewriting the SQL commands without application modification. Conclusion: Rewriting stage is the most complex stage because the system will restructure the SQL command with new syntax taking two things in its consideration, the first one was rewriting the command with better performance syntax after getting the help from recommendation dictionary, the second one was resulting the same data (output) as previous old command.