followSPL

Replacing SQL and stored procedure to develop business logic in Java in the microservices system can indeed gain framework advantages, but the development complexity is not “micro” at all. Java lacks the computational capability of structured datasets, and the code is much longer than SQL. Even with the help of technologies such as Stream and Kotlin, when faced with slightly more complex computational requirements, from the perspective of code length, its “micro” level is still far from comparable to SQL (stored procedures).

Even in terms of frameworks, microservices are not necessarily “micro” enough. Java is a compiled language, and when code changes, it needs to be recompiled and deployed, which is tedious and may force unrelated parts of the entire application to be compiled together and deployed. To make each service independent, it is necessary to use a heavy mechanism like Docker. Unable to achieve lightweight hot swap, which is very unfriendly for microservices that originally wanted to adapt to frequent business changes.

For example, there are three statements in the stored procedure, which are roughly like this, and execute very slowly:

select a,b,sum(x) from T group by a,b where …; select c,d,max(y) from T group by c,d where …; select a,c,avg(y),min(z) from T group by a,c where …; T is a huge table with hundreds of millions of rows. It needs to be grouped by three methods, and the grouped result sets are not large.

The grouping operation needs to traverse the data table. These three SQL statements will traverse the huge table three times. It takes a long time to traverse hundreds of millions of rows of data once, not to mention three times.

In this grouping operation, the CPU calculation time is almost negligible relative to the time of traversing the hard disk. If we can calculate multiple group aggregations in one traversal, although the amount of CPU calculation is not reduced, it can greatly reduce the amount of data read from the hard disk and double the speed.

If SQL could support syntax like this:

from T select a,b,sum(x) group by a,b where … -- the first grouping in the traversal select c,d,max(y) group by c,d where … -- the second grouping in the traversal select a,c,avg(y),min(z) group by a,c where …; -- the third grouping in the traversal It would be able to return multiple result sets in one traversal, and the performance can be greatly improved.

Unfortunately, SQL does not have this syntax and cannot code like this. We can only use an alternative method, that is, use group a,b,c,d to calculate a more detailed grouping result set first, but first save it into a temporary table before we can further calculate the target results with SQL. The SQL statements are rough as follows:

create table T_temp as select a,b,c,d, sum(case when … then x else 0 end) sumx, max(case when … then y else null end) maxy, sum(case when … then y else 0 end) sumy, count(case when … then 1 else null end) county, min(case when … then z else null end) minz group by a,b,c,d; select a,b,sum(sumx) from T_temp group by a,b where …; select c,d,max(maxy) from T_temp group by c,d where …; select a,c,sum(sumy)/sum(county),min(minz) from T_temp group by a,c where …; In this way, we only need to traverse once, but we have to transfer different where conditions to the previous case when, the code is much more complex and the amount of calculation will be increased. Moreover, when calculating the temporary table, the number of grouping fields becomes large, and the result set may be large. The temporary table is traversed many times, and the calculation performance is not good. Large result set grouping calculation needs hard disk buffer, and its performance is also very poor.

We can also use the database cursor of the stored procedure to fetch the data one by one, but we have to implement the actions of where and group by ourselves. It's too cumbersome to code, and the performance of the database cursor traversing the data will only be worse!

We can do nothing about it!

Many big data calculations are implemented in SQL. When running slowly, we have to optimize SQL, but we often encounter situations that we can do nothing about it.

Thank you for your reply,It's a pleasure to discuss it with you.We are not only discussing the query side of SQL (DML), it is the essence of SQL, relational algebra, and the examples in the post are just for a more intuitive representation of this. The efficiency of executing a complex SQL statement can usually only be achieved through the automatic optimization function of the database engine. As you said, SQL does not specify ordered vs hash grouping nor merge instance of hash That is an engine implementation detail and only touched upon in DDL (schema declaration). Even if more optimization is done in DML, the selection of the underlying algorithm cannot be changed, at most, it makes writing SQL more convenient for users.

Thanks for your reply, SQL is really good,but nothing in the world is perfect, technology is even more so, society is developing, always need the emergence of new technology, since dare to say so, must have done research to study, and SPL has been studied for 10 years, self-iteration several times, I hope you can try to understand it, thank you!

Let’s start with an example to explain why it’s difficult to write in SQL: calculate the maximum consecutive days that a stock keeps rising.

SELECT MAX(ContinuousDays) FROM (SELECT COUNT(*) ContinuousDays FROM (SELECT SUM(UpDownTag) OVER ( ORDER BY TradeDate) NoRisingDays FROM (SELECT TradeDate, CASE WHEN Price>LAG(price) OVER ( ORDER BY TradeDate) THEN 0 ELSE 1 END UpDownTag FROM Stock ) ) GROUP BY NoRisingDays )

SQL adopts a four-layer nested code, which is more complicated overall. This problem was once used as a recruitment test of our company, the pass rate was less than 20%. Since this test question was too difficult, we modified it: give the statement and ask the candidate to find its objective. Unfortunately, the pass rate was still not high. What does it tell us? It tells us that once the calculation logic becomes slightly complex, SQL will become difficult in both understanding and writing!

Then, how SPL solve these?

For the first example:

Stock.sort(TradeDate).group@i(Price<Price[-1]).max(~.len())

No, it's the open source SPL above

This is where SPL comes from. Its theoretical basis is no longer the relational algebra, but something called discrete dataset. The formal language designed based on this new algebra is named SPL (structured process language).

Innovations against the shortcomings of SQL have been made to SPL (more precisely, innovations against various deficiencies of relational algebra have been made to the discrete dataset). SPL redefines and extends many operations of structured data, specifically, it adds the discreteness, enhances ordered computation, implements a thorough set orientation, supports object references, and advocates stepwise operation.

Maybe you think it's bad, that's okay, give some good advice

New technology will always face various controversies, I hope to see more suggestions

Thank you for your reply,It's a pleasure to discuss it with you.We are not only discussing the query side of SQL (DML), it is the essence of SQL, relational algebra, and the examples in the post are just for a more intuitive representation of this. The efficiency of executing a complex SQL statement can usually only be achieved through the automatic optimization function of the database engine. As you said, SQL does not specify ordered vs hash grouping nor merge instance of hash That is an engine implementation detail and only touched upon in DDL (schema declaration). Even if more optimization is done in DML, the selection of the underlying algorithm cannot be changed, at most, it makes writing SPL more convenient for users.

esProc SPL is a data analysis engine with four main characteristics: low code, high performance, lightweight and versatility. To be specific, SPL allows you to write simply, and makes the running speed faster; SPL can be used either independently or embedded in applications; SPL is suitable for a variety of application scenarios. Analyzing the data through esProc SPL can reduce the overall application cost by several times compared with SQL-represented traditional technologies. Details will be given below.

Only new technology can promote the development of human society

Please read the full article:

https://blog.scudata.com/a-challenger-of-sqlite/

Thank you for your attention, there are many more examples like this 》example 1:List the last login interval for each user

SQL:

WITH TT AS (SELECT RANK() OVER(PARTITION BY uid ORDER BY logtime DESC) rk, T.* FROM t_loginT) SELECT uid,(SELECT TT. logtime FROM TT where TT.uid=TTT. uid and TT.rk=1) -(SELET TT. logtim FROM TT WHERE TT.uid=TTT. uid and TT.rk=2) interval FROM t_loginTTTT GROUP BY uid

SPL: =t_login.groups(uid;top(2,-logtime)).new(uid,#2(1).logtime-#2(2).logtime:interval)

》example 2:Calculate the moving average of sales for each month before and after each month

SQL:

WITH B AS (SELECT LAG(amount) OVER (ORDER BY smonth) f1, LEAD(amount) OVER (ORDER BY smonth) f2, A.* FROM orders A) SELECT smonth,amount, (NVL(f1,0)+NVL(f2,0)+amount)/(DECODE(f1,NULLl,0,1)+DECODE(f2,NULL,0,1)+1) moving_average FROM B

SPL: =orders.sort(smonth).derive(amount[-1,1].avg()):moving_average)

Complex SQL is not something that everyone encounters every day, and the financial industry often has more. And MATCH_ RECOGNIZE currently does not have the functionality of all databases, and we cannot require everyone to use such databases. Furthermore, simple grammar is only superficial, and the new algebra will actually bring performance improvements. For example, using ordered grouping instead of hash grouping, using merge instead of hash when associating, and so on.

Yes, you are right. everything belongs to its appropriate scenario. SQL is not good at some complex situation.There are many examples to prove this. Check out this article if you are interested. https://blog.scudata.com/spl-a-database-language-featuring-e...

Ships are safe when they're docked, but that's not what they're built for

Only new technology can promote the development of human society

esProc SPL is a data analysis engine with four main characteristics: low code, high performance, lightweight and versatility. To be specific, SPL allows you to write simply, and makes the running speed faster; SPL can be used either independently or embedded in applications; SPL is suitable for a variety of application scenarios. Analyzing the data through esProc SPL can reduce the overall application cost by several times compared with SQL-represented traditional technologies. Details will be given below.

we conduct a comparative test on ClickHouse (CH for short) and Oracle database (ORA for short) under the same hardware and software environment, and use the internationally recognized TPC-H as the test benchmark. The test is to execute the calculation requirements (Q1 to Q22) defined with 22 SQL statements for 8 tables. The test is performed on one machine within 12 threads, and the total data amount is around 100G. Since the SQL statements corresponding to TPC-H are relatively long, we do not list them here.

Q1 is a simple calculation, that is, traverse a single table to group and then aggregate. We can clearly see from the figure that CH performs better than ORA, showing that CH’s columnar storage works very well, and the speed of traversing a single table is very fast. The main cause that makes ORA much slower is that it uses the row-based storage.