Tag: JOIN

JOIN has always been a knotty and long-pending problem in the performance optimization of database. The performance of a query that was originally very fast will drop sharply once several JOINs are involved and, the larger and the more tables participating in the JOIN, the harder it is to improve performance. In fact, the key to making JOIN run faster is to categorize the JOINs. After categorizing, we can make use of the characteristic of different types of JOINs to optimize the performance. Categorize the JOINs Those having SQL development experience all know that the vast majority of JOINs are

JOIN is a long-standing challenge in optimizing database performance. The more large tables in a JOIN operation, the worse the performance. The key of JOIN optimization is to properly classify the operations so that we can design and choose the right optimization method according to the characteristics of each category. SQL defines JOINs in one single way that is too general and simple to leave little room for performance optimization. This is why it is hard to speed up JOIN operations in relational databases. The open-source esProc SPL categorizes JOINs into more types and designs specific high-performance algorithms for each

Below are order table, whose primary key is id, and order_detail table, which has a composite primary key consisting of id and productid. We might want to perform joins on the two tables. Suppose we are trying to group data by customer and order date and subtotal order amounts in each group. The grouping fields are order table’s customerid and orderdate, and order amount is the result of multiplying price by quantity in the order_detail table. Such a join is rather common. Characteristics are: The join field(s) is the primary key or one or more fields of a composite key; relationship between

The MongoDB official query language can handle joins. It achieves left joins using $lookup function and other types of joins by modifying $lookup statements. The cross join, however, almost cannot be coded through such modifications. Besides, the JSON-style syntax is complex, diffuse, and difficult to use. Calcite that supports general SQL is able and gives rather good support for various types of joins. But it has big issues. The overall computing ability is extremely weak – even without fuzzy queries and date functions. It does not support direct joins within a multilevel collection; rather, it will re-structure the collection into