Category: TalkingData

Now, we look at how to optimize and speed up homo-dimension table JOINs and primary-and-sub table JOINs. They share the same optimization method. As we mentioned, hash join algorithm’s computational complexity degree (the number of comparisons between associated fields) is about SUM(ni*mi), which is much smaller than n*m, the degree of complexity for performing a full traversal. Yet, you need to choose the right hash function to get your luck. If both tables are ordered by the associative fields, we can use the merge-join algorithm to handle a JOIN. The complexity degree is n+m. When both n and m are

Let’s continue our discussion of foreign-key-based JOINs using the example in the previous article. If a fact table is too big to be held by the memory, the foreign key address-alization strategy will fail because the external storage cannot store the pre-calculated addresses. Generally, a dimension table referred by a foreign key is small while an ever-increasing fact table is much larger. By loading the smaller dimension table into the memory, we can create temporary foreign key pointers.  A 1 =file(“customer.btx”).import@b() 2 >A1.keys@i(id) 3 =file(“orders.btx”).cursor@b() 4 >A3.switch(cid,A1) 5 =A3.groups(cid.city;sum(amount)) The first two steps are the same as those for an

By rethinking and redefining the equi-joins, we are able to simplify the join syntax. A direct and obvious effect is that queries become easy to write and understand. We offer three solutions – foreign key attributization, homo-dimension table interconnection, and sub table set-lization – to eliminate the JOIN keywords, making queries more conform to our natural thinking. The dimension alignment join syntax frees programmers from taking care of relationships between tables to let them create simple queries. What’s more, the simplified join syntax helps avoid mistakes. SQL allows writing join conditions in WHERE clause (which goes to the conventional Cartesian

In the previous essay, we talked about the alignment of two sub tables to the primary table. Below is the SQL query for doing it: Obviously, this is a useful JOIN in real-world queries, but under which type it should be classified? It involves the Orders table and two subqueries – A and B. Each subquery has a GROUP BY id clause which means the result set’s primary key will be the id field. Now the three tables (a subquery can be considered a temporary table) involved in the JOIN use the same primary key. They are homo-dimension tables associated

As all joins involve the primary key, we can devise ways of simplifying JOIN code according to this characteristic. There are three join simplification methods. 1. Foreign key attributization Below are two tables: Both tables use id field as the primary keys. The department field of employee table is a foreign key pointing to the department table. The manager field of the department table is a foreign key pointing to the employee table (because managers are also employees). This is the conventional table structures. Now we want to find the US employees whose managers are Chinese. SQL does this by

Let’s look at three types of equi-joins: 1. Foreign-key join Two tables are associated by matching a certain field in table A with the primary key of table B (By field association, as we explained in the previous essay, it is the equivalence of corresponding values in fields specified by the joining condition). Table A is called the fact table, and table B the dimension table. The field in table A that will match table B’s primary key is table A’s foreign key that points to table B; table B is thus table A’s foreign key table. In this context,

JOINs are long-standing SQL headaches. As the number of tables to be joined increases, coding becomes error prone. The complicated statements for achieving JOINs make associative queries a constantly BI software weakness. Almost no BI software can provide solutions for smooth multi-table associative queries. It is also hard for SQL to optimize and increase JOIN performance when the number of tables involved increases slightly or a large volume of data is involved. Our JOIN operations series will take a deep dive to examine the SQL pain and propose a way to simplify the JOIN syntax and enhance performance. Ⅰ JOINs

There is an I in both BI and AI, and the corresponding English word is “Intelligence”, which appears to have the same meaning. Actually, it’s not the case. Even if we only discuss issues in the field of business decision-making, there are significant differences between the I in BI and AI. Broadly speaking, tasks that rely on data to assist business decision-making can be called BI, and BI can be said to include AI. BI is the purpose, AI is the means, and BI can be achieved through AI means or other means. In this sense, the I of BI

A certain organization has established a distributed data warehouse, gradually loading historical data into the warehouse, and then building a BI system based on the data warehouse (mainly for multidimensional analysis). At first, everything went smoothly, but over time, there were more and more applications based on central data warehouse, and over the years, dozens of applications were accumulated. These applications all rely on data warehouse for calculations, leading to an increasing burden on the central data warehouse and a sluggish response of the BI system. For multidimensional analysis businesses requiring strong interactivity, this is hard to tolerate. What should