From: firebird-support@yahoogroups.com [firebird-support@yahoogroups.com] On Behalf Of personalsoft_fabiano [fabiano@...]
Sent: Friday, August 14, 2009 2:21 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] CTE performance

...
I have a block which uses CTE´s inside it.

I started declaring the alias CTE_1, which queries a real table, grouping its records (it reads 6738 records, and returns 285 records grouped)

Then i declare a second alias CTE_2, which just reads CTE_1 and transform its rows in columns (1 column for each month). This part returns about 40 rows.

Up to this point, the query runs in about 2 seconds (see EXAMPLE 1 below)

Next step i insert a third alias, CTE_3, which just reads CTE_2, grouping all its records in a single row. After inserting CTE_3 and joining it in the final SELECT, the performance drops drastically (the block takes about 1 minute to execute - see EXAMPLE 2 below).

Is this performance drop expected in this case?

- - - - - - - - - -

Fabiano,

1 - It appears that your use of "inner join cte_3 c on 1 = 1" is the source of your slowdown, it results in the "explosion" of cte_3 for each row in the cad1 table.

2 - CTE_3 does not "reads CTE_2", as you outline, but rather it is reading CTE_1 (see your example 2)

3 - No elements/columns of CTE_3 is referenced anywhere in the final query... so why it is there?

4 - Why do you have both CTE_2 and CTE_3 joined in the final query?