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