03.06.2016 17:59, Svein Erling Tysvær wrote: > > I simplified the query to > > WITH TMP( TJENESTEENHETRESHID, TJENESTEENHETLOKAL, IMPORT_DATO ) AS > ( SELECT TJENESTEENHETRESHID, TJENESTEENHETLOKAL, MAX( IMPORT_DATO ) > FROM NPR > WHERE TJENESTEENHETRESHID > 0 > AND TJENESTEENHETLOKAL > '' > GROUP BY 1, 2 ) > select t1.tjenesteenhetreshid, t1.tjenesteenhetlokal > from tmp t1 > left join tmp t2 > on t1.tjenesteenhetreshid = t2.tjenesteenhetreshid > and t1.tjenesteenhetlokal <> t2.tjenesteenhetlokal > and t1.IMPORT_DATO <= t2.IMPORT_DATO > where t2.tjenesteenhetreshid is null
No difference: the same nested loop join. > So my question was more: "Why not execute the CTE (against the large > table) once, and do the further processing against the (very small) > result of the CTE, rather than expand the CTE twice (and thus increase > the execution time from 16 minutes to about four weeks)?" Prior to v3, the engine didn't support materialized subqueries (except in sorts and merge joins). I hoped to implement such a "caching" for nested loop joins in v3, but didn't have time to succeed. Maybe in some point release. > I think I've found a completely different way to solve the particular > problem I was trying to solve, so this was more a case of me believing > Firebird behaving differently than it does. I can think of at least two options to execute this query fast, but none of them implemented in Firebird yet. It's in my personal roadmap. Dmitry
