与-子句未预先计算，线材资源浪费，为什么？

Question

我试图节省资源，缩短代码，但使用“with -子句”却浪费了线状资源。

这是我的基本例子：

#!/bin/bash

echo "CREATE TABLE 'clients_table' ( id integer primary key, \
                        name            text DEFAULT 'noname', \
                        param1          int  DEFAULT 0, \
                        param2          int  DEFAULT 0, \
                        param3          int  DEFAULT 0, \
                        param4          int  DEFAULT 0, \
                        param5          int  DEFAULT 0 \
                        );"


# dimension=100
# dimension=20
dimension=15    # 1m16s
# dimension=10  # 16 seconds.
# dimension=2
for i1 in `seq 1 $dimension`;do
        echo "$(date): i1: $i1" > /dev/stderr
        for i2 in `seq 1 $dimension`;do
                for i3 in `seq 1 $dimension`;do
                        for i4 in `seq 1 $dimension`;do
                                for i5 in `seq 1 $dimension`;do
                                        echo "INSERT INTO clients_table ( name, param1, param2, param3, param4, param5 ) VALUES ( \
                                                'c${i1}_${i2}_${i3}_${i4}_${i5}', $i1, $i2, $i3, $i4, $i5 );"
                                done
                        done
                done
        done
done

在1分16秒内，使用维度15，我将通过以下命令获得sqlite数据库：

# time bash gen_base.sh | sqlite3 ram_fs/tmp.db
...
real    1m16.769s
user    1m23.690s
sys     0m53.379s
# du -hs ram_fs/tmp.db 
23M     ram_fs/tmp.db

此后，我对不同的客户端使用这样的sql，通过每个param，从这一点，从那个.

WITH work_set AS (
    SELECT * FROM clients_table
        WHERE   param1 % 2 == 0
        ORDER BY    param1 ASC,
                param2 DESC,
                param3 ASC,
                param4 DESC,
                param5 ASC
),
odd2        AS ( SELECT * FROM work_set WHERE param2 % 2 == 1 ),
even2       AS ( SELECT * FROM work_set WHERE param2 % 2 == 0 ),
/* --------------------------- */
odd2odd3    AS ( SELECT * FROM odd2     WHERE param3 % 2 == 1 ),
odd2even3   AS ( SELECT * FROM odd2     WHERE param3 % 2 == 0 ),
even2odd3   AS ( SELECT * FROM even2    WHERE param3 % 2 == 1 ),
even2even3  AS ( SELECT * FROM even2    WHERE param3 % 2 == 0 ),
/*-----------------------------*/
odd2odd3odd4    AS ( SELECT * FROM odd2odd3 WHERE param4 % 2 == 1 ),
odd2odd3even4   AS ( SELECT * FROM odd2odd3 WHERE param4 % 2 == 0 ),
odd2even3odd4   AS ( SELECT * FROM odd2even3    WHERE param4 % 2 == 1 ),
odd2even3even4  AS ( SELECT * FROM odd2even3    WHERE param4 % 2 == 0 ),
/*--*/
even2odd3odd4   AS ( SELECT * FROM even2odd3    WHERE param4 % 2 == 1 ),
even2odd3even4  AS ( SELECT * FROM even2odd3    WHERE param4 % 2 == 0 ),
even2even3odd4  AS ( SELECT * FROM even2even3   WHERE param4 % 2 == 1 ),
even2even3even4 AS ( SELECT * FROM even2even3   WHERE param4 % 2 == 0 ),
/* --------------------------- */
c1      AS ( SELECT * FROM odd2odd3odd4 WHERE param5 % 2 == 1 LIMIT 1 ),
c2      AS ( SELECT * FROM odd2odd3odd4 WHERE param5 % 2 == 0 LIMIT 1 ),
/*--*/
c3      AS ( SELECT * FROM odd2odd3even4 WHERE param5 % 2 == 1 LIMIT 1 ),
c4      AS ( SELECT * FROM odd2odd3even4 WHERE param5 % 2 == 0 LIMIT 1 ),
/*--*/
c5      AS ( SELECT * FROM odd2even3odd4 WHERE param5 % 2 == 1 LIMIT 1 ),
c6      AS ( SELECT * FROM odd2even3odd4 WHERE param5 % 2 == 0 LIMIT 1 ),
/*--*/
c7      AS ( SELECT * FROM odd2even3even4 WHERE param5 % 2 == 1 LIMIT 1 ),
c8      AS ( SELECT * FROM odd2even3even4 WHERE param5 % 2 == 0 LIMIT 1 ),
/*--*/
c9      AS ( SELECT * FROM even2odd3odd4 WHERE param5 % 2 == 1 LIMIT 1 ),
c10     AS ( SELECT * FROM even2odd3odd4 WHERE param5 % 2 == 0 LIMIT 1 ),
/*--*/
c11     AS ( SELECT * FROM even2odd3even4 WHERE param5 % 2 == 1 LIMIT 1 ),
c12     AS ( SELECT * FROM even2odd3even4 WHERE param5 % 2 == 0 LIMIT 1 ),
/*--*/
c13     AS ( SELECT * FROM even2even3odd4 WHERE param5 % 2 == 1 LIMIT 1 ),
c14     AS ( SELECT * FROM even2even3odd4 WHERE param5 % 2 == 0 LIMIT 1 ),
/*--*/
c15     AS ( SELECT * FROM even2even3even4 WHERE param5 % 2 == 1 LIMIT 1 ),
c16     AS ( SELECT * FROM even2even3even4 WHERE param5 % 2 == 0 LIMIT 1 )
/* --------------------------- */
SELECT * FROM c1 UNION ALL
SELECT * FROM c2 UNION ALL
SELECT * FROM c3 UNION ALL
SELECT * FROM c4 UNION ALL
SELECT * FROM c5 UNION ALL
SELECT * FROM c6 UNION ALL
SELECT * FROM c7 UNION ALL
SELECT * FROM c8 UNION ALL
SELECT * FROM c9 /*UNION ALL
SELECT * FROM c10 UNION ALL
SELECT * FROM c11 UNION ALL
SELECT * FROM c12 UNION ALL
SELECT * FROM c13 UNION ALL
SELECT * FROM c14 UNION ALL
SELECT * FROM c15 UNION ALL
SELECT * FROM c16
*/

我试图在sql末尾移开注释/*，以区别联合的数量，并测量时间：

# time cat with_select_bug.sql  | sqlite3 ram_fs/tmp.db 

• 0m0.092 s-只有一个客户。
• 0m0.183 s-两个客户。
• 0m0.274 s-三个客户。
• ..。
• 0m0.678 s-八个客户。
• ..。
• 0m1.336 s-所有16个客户。

正如您所看到的，CPU时间使用的直线增长，一个客户端--比两个客户端快两倍--以及所有客户端--都希望从我的CPU中获得完整的秒。为什么？

我已经声明了WITH-clause，对吗？- SELECT应该是预先计算的，所有的客户都应该只过滤以前计算出来的数据的结果。

此外，如果我多次问同一个客户：

...
SELECT * FROM c16 UNION ALL
SELECT * FROM c16 UNION ALL
SELECT * FROM c16 UNION ALL
SELECT * FROM c16 UNION ALL
...

每一个选择都会给我相同的客户，但是每个人都会被分开计算！就像c16的声明-不存在.

我可以把100行SELECT * FROM c16 UNION ALL，它将是100倍的时间比一个。为什么会这样呢？

问题：

• Why生长呈线性？
• How我能提高速度吗？
• Is“WITH-子句”--只是为了缩短代码？就像宏一样，没有加速功能.

Answer 1

您所指的"WITH子句“是一个CTE。它允许您多次重复使用代码，但只编写一次。

您的代码有一个我从未见过的执行良好的模式--多个嵌套的CTE。您编写的每个CTE都被多次执行--每次调用它。如果你张贴你的执行计划，它应该确认这一点。

这篇文章比我所能解释的要好得多，如何多次调用相同的CTE将导致优化器在幕后多次执行CTE。

“WITH-子句”只是为了缩短代码吗？像宏一样，没有加速功能.

如果你愿意的话，我会说CTE是一个可读性工具，语法糖。它不仅没有任何“加速功能”，实际上也有相反的效果，正如上面的文章所演示的。

我怎样才能提高速度？

很难从代码片段中看出您的代码正在尝试实现什么，但可能值得在本文中研究CROSS APPLY解决方案，或者考虑使用只执行相关读取一次的临时表来代替CTE。

您可以通过在代码请求时添加SET STATISTICS IO, TIME ON来验证您所做的更改是否使事情变得更好--如果您的读取和CPU时间在代码更改后减少，那么您的代码就更具有性能了。