Increasing Database Performance through Optimizing Structure Query Language Join Statement

Abstract

Problem statement: A join statement is a select statement with more th an 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 t he 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 lan guage should be declarative, so we can write alternative formulas to perform join statements. Di fferent 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 t he SQL commands sent by application before reaching the database then examining these commands to see if they join more than one table, after that re writing the SQL command taking into consideration the order of executing join predicates and none joi n predicates. This research focused on rewriting th e SQL commands without application modification. Conclusion: Rewriting stage is the most complex stage because the system will restructure the SQL c ommand with new syntax taking two things in its consideration, the first one was rewriting the comm and with better performance syntax after getting th e help from recommendation dictionary, the second one was resulting the same data (output) as previous old command.