1
Vote

Execution report too slow

description

In an example a "select * from exec_stats_recursed" (where exec_stats_recursed is a CTE in the query that drives this report) took 8mins to return ~4000rows and was still going. It also brought my workstation to its knees (4 CPUs all at 100%).
 
For reference:
select * from exec_stats - 9252 rows in 4seconds
select * from generated_executables - 4533 rows in 5seconds
select * from anchor - 4535 rows in 5seconds
select * from exec_stats_recursed - ~4000rows in 8minutes at which point I killed it
 
Clearly this query can't handle big data volumes. This was an extreme case because a ForEach Loop had 4520 iterations but still - this is not good.
 
I took [generated_executables] out of the mix (see attached execution_without_generated_executables.sql) and it returned in 12 seconds (good) but didn't return everything I needed (bad) because we need [generated_executables to make the recursion work. I think we need a way of recursing without generating executables. Not easy.

file attachments

comments