Tag: Surpass DB

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

HANA is a popular in-memory database and, theoretically, has the potential to replace Spark, but that it is not open-source keeps people away. SQLite, another in-memory database, is open-source, but it only supports embedded call, which puts great limits on data size and computing performance. Redis is open-source, high-performance, and supports processing of huge volumes of data, but it is extremely bad at doing computations and turns to a great deal of hardcoding for performing in-memory computations. The best Spark alternative is esProc SPL. SPL is an open-source, in-memory big data computing library that uses fewer memory resources, offers higher

HANA, SPARK and Redis are commonly used in-memory databases, but they all have complex and heavy frameworks, compromising their ranges of applications. The popular simple and lightweight in-memory technology is SQLite, which is nimble and simple-framed and can be directly embedded in a Java program. Its disadvantages include lack of independent services and support for stored procedures, unstable environment and slow execution, weak computing abilities, and complicated data loading process for external data use. The technology has better and more powerful embodiment – esProc SPL. The open-source Java class library has simple and agile framework, enabling it to be embedded

All popular big data computing technologies, including Hadoop, Storm, Hive and Spark, use large-cluster-based strategies, which are suited to large enterprises having massive-scale data. Actually, those technologies originate from some IT giants. Yet, small-scale clusters are enough, even no clusters are needed, for handling many real-world scenarios involving the so-called “big data” that is, in fact, far less than that the giant corporations have, and smaller companies do not have many hardware components and a large team of maintenance staff. For them, a lightweight big data computing technology will exactly fit their needs. Among the few such technologies, esProc SPL is

The open-source analytical database ClickHouse (CH) is hot recently and is said to be exceptionally fast for performing OLAP analyses. Many users suffering from performance problems are eager to have a try. Does the DBMS really meet our expectations? The following performance test will tell us. Our subjects for comparisons are CH, Oracle (Ora in the following sections), and the open-source esProc SPL. The benchmark is the internationally acknowledged TPC-H. The test is based on 8 tables, tasked with achieving computations defined by 22 SQL statements (Q1-Q22), and compares times spent in finishing the computations with CH, Ora, and SPL

Compared with SQL databases/ data warehouses, the search engine Elasticsearch is more suitable for implementing high-concurrency queries, such as account detail query that searches for several to thousands of detailed records from tens of millions of, even a billion, rows of historical data. The queries feature huge amounts of data, high concurrency, and demand of sub-second response times. The SQL-based technological frameworks, including relational databases and HADOOP data warehouses, can hardly meet the requirements with available resources. The popular practice is to export data to Elasticsearch and use its search technology to achieve good performance at high concurrency. From this perspective,

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

In a multidimensional application, a fact table always has a lot of dimension tables. As the following shows, the orders table has a series of dimension tables: The association between any of the dimension tables and the fact table is the JOIN defined in SQL. Technically, databases use the HASH JOIN algorithm to achieve the joins, implementing one at a time. N times of HASH JOIN are needed when there are N joins, and each intermediate result set will be retained for use in the next join. The computing process is complex and involves multiple traversals, markedly dragging down the

Multidimensional analysis usually involves large data sets. It is inefficient to summarize original detail data ad hoc for performing business analysis. So, pre-aggregation is used to speed up queries by pre-preparing the desired result sets, which will be directly retrieved to guarantee a real-time response for interactive analysis. About pre-aggregation strategies Pre-aggregation is a way of achieving very short response times through highly disk-consuming data pre-summarization. In theory, we can pre-summarize all combinations of dimensions for use to handle multidimensional analysis scenarios based on any of the combinations. In practice, it is hardly feasible. A 50-dimension full pre-aggregation takes up

The algorithm of achieving SQL TopN operations is to sort records and get top N values or records. Some databases provide good optimization strategies, avoiding sorting the entire set to get the top N and acquiring higher performance. Yet facing the complicated scenario of getting the top N from each group after the grouping operation, SQL is hard to code the algorithm and database optimization is even harder to implement. Generally, sorting the whole set is practically unavoidable. The sorting, however, is costly. Performance will be seriously affected, particularly when data cannot fit into the memory and external data exchanges