Submitted by: Baldur Fürchau (bfuerchau)
Since Release 1.5 i use the following strategie to build queries depending on missing CTE's:
create table tmptable1 (key varchar(10), result double precision)
create index ix1 on tmptable1 (key)
create table tmptable2 (key varchar(10), result double precision)
create index ix2 on tmptable2 (key)
insert into tmptable1
select key, sum(result) from mytable1 group by key
insert into tmptable2
select key, sum(result) from mytable2 group by key
Now the final result:
select a.key, a.result + coalesce(b.result, 0)
from tmptable1 a
left join tmptable2 on a.key=b.key
and finally:
drop table tmptable1
drop table tmptable2
This works fast and easy, but depending on many temporary tables i get often an error on droppeng the temptables with "object in use"-message. I need every week a save/restore to reorganize the database.
Now in release 2.1.2 i thought i could use CTE's to solve the problem:
with
tmptable1 (key, result) as (select key, sum(result) from mytable1 group by key)
,
tmptable2 (key, result) as (select key, sum(result) from mytable2 group by key)
select a.key, a.result + coalesce(b.result, 0)
from tmptable1 a
left join tmptable2 on a.key=b.key
This SQL works as expected but the performance is very bad.
If each temporary result contains many records, i think an index over the key-fields would not be build.
So the performance goes down when then results grows.
My suggestion is to build also temporary indexes on the join-fields or also group and order-fields to improve performance and also stability on the database himself.
The "old" solution has fast response less than 1-2 seconds totally, with CTE the response is > 30 seconds.
My original tables contains somtimes more than 100.000 records and the temporary results somtimes more than 1000 records.
Until the firebird-team don't solve this problem i must use my "old" solution.
Submitted by: Baldur Fürchau (bfuerchau)
Since Release 1.5 i use the following strategie to build queries depending on missing CTE's:
create table tmptable1 (key varchar(10), result double precision)
create index ix1 on tmptable1 (key)
create table tmptable2 (key varchar(10), result double precision)
create index ix2 on tmptable2 (key)
insert into tmptable1
select key, sum(result) from mytable1 group by key
insert into tmptable2
select key, sum(result) from mytable2 group by key
Now the final result:
select a.key, a.result + coalesce(b.result, 0)
from tmptable1 a
left join tmptable2 on a.key=b.key
and finally:
drop table tmptable1
drop table tmptable2
This works fast and easy, but depending on many temporary tables i get often an error on droppeng the temptables with "object in use"-message. I need every week a save/restore to reorganize the database.
Now in release 2.1.2 i thought i could use CTE's to solve the problem:
with
tmptable1 (key, result) as (select key, sum(result) from mytable1 group by key)
,
tmptable2 (key, result) as (select key, sum(result) from mytable2 group by key)
select a.key, a.result + coalesce(b.result, 0)
from tmptable1 a
left join tmptable2 on a.key=b.key
This SQL works as expected but the performance is very bad.
If each temporary result contains many records, i think an index over the key-fields would not be build.
So the performance goes down when then results grows.
My suggestion is to build also temporary indexes on the join-fields or also group and order-fields to improve performance and also stability on the database himself.
The "old" solution has fast response less than 1-2 seconds totally, with CTE the response is > 30 seconds.
My original tables contains somtimes more than 100.000 records and the temporary results somtimes more than 1000 records.
Until the firebird-team don't solve this problem i must use my "old" solution.