WITH-clause not pre-calculated, lineal resource wasting, why?

Question

I'm trying to save resources, and shorten code, but getting lineal resource wasting with "WITH-clause".

This is my base example:

#!/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

For 1 minute and 16 seconds, with dimension 15, I'm getting sqlite database by such command:

# 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

Thereafter, I'm using such sql to different clients, by each param, little bit from this, little bit from that...

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
*/

I'm trying to move opening of comment /* in the end of sql, to differ the number of UNIONs, and measuring the time:

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

• 0m0.092s - only one client.
• 0m0.183s - two clients.
• 0m0.274s - three clients.
• ...
• 0m0.678s - eight clients.
• ...
• 0m1.336s - all sixteen clients.

As you can see, lineal growing of CPU time usage, one client - right two times faster than two clients, and all clients - want to take full second from my CPU.. WHY?

I've declared WITH-clause, right? - SELECT shall be pre-calculated, and all clients, shall only filter result of calculated previously data.

---

Moreover, If I'm asking the same client many times:

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

Every select will give me the same client, but everyone will be calculated separately!! Like declaration of c16 - does not exist...

I can put 100 lines SELECT * FROM c16 UNION ALL, and it will be 100x time slower than one. Why so?

Questions:

• Why growing is lineal?
• How can I increase the speed?
• Is "WITH-clause" - just to shorten code only? like macros, no speed-up functionality...

Answer 1

The "WITH clause" you refer to is a CTE. It allows you to re-use code multiple times but write it once.

Your code has a pattern which I've never seen perform well - multiple, nested CTE's. Every CTE you've written is being executed multiple times - once each time you call it. If you post your execution plan, it should confirm this.

this article explains far more eloquently than I able to, how calling the same CTE multiple times will cause the optimizer to execute the CTE multiple times behind the scenes.

Is "WITH-clause" - just to shorten code only? like macros, no speed-up functionality...

I would say a CTE is a readability tool, syntactic sugar if you will. Not only does it not have any "speed up functionality" it in fact has the the opposite effect as the article above demonstrates.

How can I increase the speed?

It's difficult to tell what your code is trying to achieve from your snippet but it would maybe be worth investigating the CROSS APPLY solution in the article, or instead of CTEs, consider using temp tables which will only perform the relevant read once.

You can verfify whether the changes you make are making things better by adding SET STATISTICS IO, TIME ON at the beggining of your code - if your reads and CPU time decreases after you make a code change, your code is more performant.