Show HN: The impasse of SQL performance optimizing

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.

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!