Performance with Subselect containing Group-By [CORE1034] #1451

firebird-automations · 2006-11-30T13:35:23Z

Submitted by: Baldur F?rchau (fuerchau)

I have to tables, each with about 200.000 Records.

Now i code the following SQL:

select * from
(select k1, k2, k3, sum(v1) as v1
from atable
where k1='1' and K2='1'
group by 1, 2, 3
) a
left join
(select k1, k2, k3, sum(v1) as v1
from btable
where k1='1' and K2='1'
group by 1, 2, 3
) b on a.k1=b.k1 and a.k2=b.k2 and a.k3=b.k3

The Resultset contains ca. 100 Records.
The Querytime is 8 Minutes !!!

If i create 2 temporary tables
XTablea, XTableb
and make the following SQL's:

insert into xtablea
select k1, k2, k3, sum(v1) as v1
from atable
where k1='1' and K2='1'
group by 1, 2, 3

insert into xtable5
select k1, k2, k3, sum(v1) as v1
from btable
where k1='1' and K2='1'
group by 1, 2, 3

select * from xtablea left join xtableb on a.k1=b.k1 and a.k2=b.k2 and a.k3=b.k3

the answertime is about 8 seconds !!!!

I think, firebird solve this with "temporary views" instead of "temporary table".
In Firebird 1.5 i tried this obove with views an i have the same query-time 8 minutes.

So, why can't Firebird do use automatic "temporary tables" ?

firebird-automations · 2006-11-30T14:08:57Z

Modified by: Baldur F?rchau (fuerchau)

description: I have to tables, each with about 200.000 Records.

Now i code the following SQL:

select * from
(select k1, k2, k3, sum(v1) as v1
from atable
where k1='1' and K2='1' and k3='1'
group by 1, 2, 3
) a
left join
(select k1, k2, k3, sum(v1) as v1
from btable
where k1='1' and K2='1' and k3='1'
group by 1, 2, 3
) b on a.k1=b.k1 and a.k2=b.k2 and a.k3=b.k3

The Resultset contains ca. 100 Records.
The Querytime is 8 Minutes !!!

If i create 2 temporary tables
XTablea, XTableb
and make the following SQL's:

insert into xtablea
select k1, k2, k3, sum(v1) as v1
from atable
where k1='1' and K2='1' and k3='1'
group by 1, 2, 3

insert into xtable5
select k1, k2, k3, sum(v1) as v1
from btable
where k1='1' and K2='1' and k3='1'
group by 1, 2, 3

select * from xtablea left join xtableb on a.k1=b.k1 and a.k2=b.k2 and a.k3=b.k3

the answertime is about 8 seconds !!!!

So, why can't Firebird do this automatic ?

=>

I have to tables, each with about 200.000 Records.

Now i code the following SQL:

select * from
(select k1, k2, k3, sum(v1) as v1
from atable
where k1='1' and K2='1'
group by 1, 2, 3
) a
left join
(select k1, k2, k3, sum(v1) as v1
from btable
where k1='1' and K2='1'
group by 1, 2, 3
) b on a.k1=b.k1 and a.k2=b.k2 and a.k3=b.k3

The Resultset contains ca. 100 Records.
The Querytime is 8 Minutes !!!

If i create 2 temporary tables
XTablea, XTableb
and make the following SQL's:

insert into xtablea
select k1, k2, k3, sum(v1) as v1
from atable
where k1='1' and K2='1'
group by 1, 2, 3

insert into xtable5
select k1, k2, k3, sum(v1) as v1
from btable
where k1='1' and K2='1'
group by 1, 2, 3

select * from xtablea left join xtableb on a.k1=b.k1 and a.k2=b.k2 and a.k3=b.k3

the answertime is about 8 seconds !!!!

I think, firebird solve this with "temporary views" instead of "temporary table".
In Firebird 1.5 i tried this obove with views an i have the same query-time 8 minutes.

So, why can't Firebird do use automatic "temporary tables" ?

firebird-automations · 2006-11-30T15:01:14Z

Commented by: Sean Leyne (seanleyne)

Please post the PLAN for the initial statement.

firebird-automations · 2006-11-30T15:04:03Z

Commented by: Sean Leyne (seanleyne)

Please clarify: By 8 seconds do you mean that your can perform the INSERTs and SELECT in 8 seconds?

If not, please post the total time.

firebird-automations · 2006-11-30T15:08:55Z

Commented by: Sean Leyne (seanleyne)

Finally, you realize that your statement can be better expressed as:

select
1 as K1, 1 as K2, a.k3, a.v1, b.v1
from (
select
k3, sum(v1) as v1
from atable
where k1='1' and K2='1'
group by 1
) a
left join (
select k3, sum(v1) as v1
from btable
where k1='1' and K2='1'
group by 1
) b on a.k3=b.k3

The k1 and k2 values can be replaced with constants, since they are specified in the WHERE clause.

firebird-automations · 2006-12-01T04:42:13Z

Commented by: @pcisar

I have deleted the previous comment and reinserted it with access rights for FB developers only, as the original poster requested restricted access to it, but failed to assign proper access right to his comment.

----------------
Ok now some internals of my software, please don't show this to all users !

Table 1:

/******************************************************************************/
/**** Generated by IBExpert 30.11.2006 20:00:55 ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES NONE;

/******************************************************************************/
/**** Tables ****/
/******************************************************************************/

CREATE TABLE UMDATEN (
LEVELDATE DATE NOT NULL,
LEVELWEEK INTEGER NOT NULL,
LEVELMONTH INTEGER NOT NULL,
LEVELYEAR INTEGER NOT NULL,
ERRORV VARCHAR(255) NOT NULL,
LEVEL0 CHAR(1) NOT NULL,
LEVEL1 CHAR(3) NOT NULL,
LEVEL2 CHAR(6) NOT NULL,
LEVEL3 CHAR(1) NOT NULL,
LEVEL4 CHAR(6) NOT NULL,
LEVEL5 CHAR(3) NOT NULL,
LEVEL6 CHAR(8) NOT NULL,
LEVEL7 CHAR(2) NOT NULL,
LEVEL8 CHAR(2) NOT NULL,
LEVEL9 CHAR(3) NOT NULL,
LEVEL10 CHAR(2) NOT NULL,
LEVEL11 CHAR(3) NOT NULL,
LEVEL12 CHAR(2) NOT NULL,
LEVEL13 CHAR(4) NOT NULL,
LEVEL14 CHAR(2) NOT NULL,
LEVEL15 CHAR(4) NOT NULL,
LEVEL16 CHAR(4) NOT NULL,
LEVEL17 CHAR(1) NOT NULL,
LEVEL18 CHAR(13) NOT NULL,
LEVEL19 CHAR(2) NOT NULL,
LEVEL20 CHAR(3) NOT NULL,
LEVEL21 CHAR(3) NOT NULL,
LEVEL22 CHAR(4) NOT NULL,
LEVEL23 CHAR(6) NOT NULL,
LEVEL24 CHAR(8) NOT NULL,
LEVEL25 CHAR(3) NOT NULL,
LEVEL26 CHAR(3) NOT NULL,
LEVEL27 CHAR(4) NOT NULL,
LEVEL28 CHAR(2) NOT NULL,
LEVEL29 CHAR(2) NOT NULL,
LEVEL30 CHAR(2) NOT NULL,
LEVEL31 CHAR(6) NOT NULL,
LEVEL32 CHAR(3) NOT NULL,
LEVEL33 CHAR(3) NOT NULL,
LEVEL34 CHAR(1) NOT NULL,
LEVEL35 CHAR(3) NOT NULL,
LEVEL36 CHAR(3) NOT NULL,
LEVEL37 CHAR(2) NOT NULL,
LEVEL38 CHAR(1) NOT NULL,
LEVEL39 CHAR(2) NOT NULL,
LEVEL40 CHAR(4) NOT NULL,
LEVEL41 CHAR(4) NOT NULL,
LEVEL42 CHAR(1) NOT NULL,
LEVEL43 CHAR(2) NOT NULL,
LEVEL44 CHAR(4) NOT NULL,
LEVEL45 CHAR(1) NOT NULL,
LEVEL46 CHAR(6) NOT NULL,
LEVEL47 CHAR(6) NOT NULL,
LEVEL48 CHAR(6) NOT NULL,
LEVEL49 CHAR(1) NOT NULL,
LEVEL50 CHAR(20) NOT NULL,
VALUE0 DECIMAL(15,4) NOT NULL,
VALUE1 DECIMAL(15,4) NOT NULL,
VALUE2 DECIMAL(15,4) NOT NULL,
VALUE3 DECIMAL(15,4) NOT NULL,
VALUE4 DECIMAL(15,4) NOT NULL,
VALUE5 DECIMAL(15,4) NOT NULL,
VALUE6 DECIMAL(15,4) NOT NULL,
VALUE7 DECIMAL(15,4) NOT NULL,
VALUE8 DECIMAL(15,4) NOT NULL,
VALUE9 DECIMAL(15,4) NOT NULL,
VALUE10 DECIMAL(15,4) NOT NULL,
VALUE11 DECIMAL(15,4) NOT NULL,
INFO0 DOUBLE PRECISION NOT NULL,
INFO1 DOUBLE PRECISION NOT NULL,
INFO2 DECIMAL(15,4) NOT NULL,
INFO3 INTEGER NOT NULL,
INFO4 INTEGER NOT NULL,
INFO5 INTEGER NOT NULL,
INFO6 DATE NOT NULL,
INFO7 INTEGER NOT NULL,
INFO8 INTEGER NOT NULL,
INFO9 INTEGER NOT NULL,
INFO10 DATE NOT NULL,
INFO11 DATE NOT NULL,
INFO12 VARCHAR(3) NOT NULL,
INFO13 DECIMAL(15,4) NOT NULL,
INFO14 DECIMAL(15,4) NOT NULL,
INFO15 DECIMAL(15,4) NOT NULL,
INFOQ VARCHAR(1) NOT NULL
);

/******************************************************************************/
/**** Indices ****/
/******************************************************************************/

CREATE DESCENDING INDEX UMDATEN_DLEVEL0 ON UMDATEN (LEVEL0);
CREATE DESCENDING INDEX UMDATEN_DLEVELDATE ON UMDATEN (LEVELDATE);
CREATE INDEX UMDATEN_IDX01QI345 ON UMDATEN (LEVEL0, LEVEL1, INFO3, INFO4, INFO5);
CREATE INDEX UMDATEN_IX_LEVEL0 ON UMDATEN (LEVEL0);
CREATE INDEX UMDATEN_IX_LEVEL1 ON UMDATEN (LEVEL1);
CREATE INDEX UMDATEN_IX_LEVEL10 ON UMDATEN (LEVEL10);
CREATE INDEX UMDATEN_IX_LEVEL11 ON UMDATEN (LEVEL11);
CREATE INDEX UMDATEN_IX_LEVEL12 ON UMDATEN (LEVEL12);
CREATE INDEX UMDATEN_IX_LEVEL13 ON UMDATEN (LEVEL13);
CREATE INDEX UMDATEN_IX_LEVEL14 ON UMDATEN (LEVEL14);
CREATE INDEX UMDATEN_IX_LEVEL15 ON UMDATEN (LEVEL15);
CREATE INDEX UMDATEN_IX_LEVEL16 ON UMDATEN (LEVEL16);
CREATE INDEX UMDATEN_IX_LEVEL17 ON UMDATEN (LEVEL17);
CREATE INDEX UMDATEN_IX_LEVEL18 ON UMDATEN (LEVEL18);
CREATE INDEX UMDATEN_IX_LEVEL19 ON UMDATEN (LEVEL19);
CREATE INDEX UMDATEN_IX_LEVEL2 ON UMDATEN (LEVEL2);
CREATE INDEX UMDATEN_IX_LEVEL20 ON UMDATEN (LEVEL20);
CREATE INDEX UMDATEN_IX_LEVEL21 ON UMDATEN (LEVEL21);
CREATE INDEX UMDATEN_IX_LEVEL22 ON UMDATEN (LEVEL22);
CREATE INDEX UMDATEN_IX_LEVEL23 ON UMDATEN (LEVEL23);
CREATE INDEX UMDATEN_IX_LEVEL24 ON UMDATEN (LEVEL24);
CREATE INDEX UMDATEN_IX_LEVEL25 ON UMDATEN (LEVEL25);
CREATE INDEX UMDATEN_IX_LEVEL26 ON UMDATEN (LEVEL26);
CREATE INDEX UMDATEN_IX_LEVEL27 ON UMDATEN (LEVEL27);
CREATE INDEX UMDATEN_IX_LEVEL28 ON UMDATEN (LEVEL28);
CREATE INDEX UMDATEN_IX_LEVEL29 ON UMDATEN (LEVEL29);
CREATE INDEX UMDATEN_IX_LEVEL3 ON UMDATEN (LEVEL3);
CREATE INDEX UMDATEN_IX_LEVEL30 ON UMDATEN (LEVEL30);
CREATE INDEX UMDATEN_IX_LEVEL31 ON UMDATEN (LEVEL31);
CREATE INDEX UMDATEN_IX_LEVEL32 ON UMDATEN (LEVEL32);
CREATE INDEX UMDATEN_IX_LEVEL33 ON UMDATEN (LEVEL33);
CREATE INDEX UMDATEN_IX_LEVEL34 ON UMDATEN (LEVEL34);
CREATE INDEX UMDATEN_IX_LEVEL35 ON UMDATEN (LEVEL35);
CREATE INDEX UMDATEN_IX_LEVEL36 ON UMDATEN (LEVEL36);
CREATE INDEX UMDATEN_IX_LEVEL37 ON UMDATEN (LEVEL37);
CREATE INDEX UMDATEN_IX_LEVEL38 ON UMDATEN (LEVEL38);
CREATE INDEX UMDATEN_IX_LEVEL39 ON UMDATEN (LEVEL39);
CREATE INDEX UMDATEN_IX_LEVEL4 ON UMDATEN (LEVEL4);
CREATE INDEX UMDATEN_IX_LEVEL40 ON UMDATEN (LEVEL40);
CREATE INDEX UMDATEN_IX_LEVEL41 ON UMDATEN (LEVEL41);
CREATE INDEX UMDATEN_IX_LEVEL42 ON UMDATEN (LEVEL42);
CREATE INDEX UMDATEN_IX_LEVEL43 ON UMDATEN (LEVEL43);
CREATE INDEX UMDATEN_IX_LEVEL44 ON UMDATEN (LEVEL44);
CREATE INDEX UMDATEN_IX_LEVEL45 ON UMDATEN (LEVEL45);
CREATE INDEX UMDATEN_IX_LEVEL46 ON UMDATEN (LEVEL46);
CREATE INDEX UMDATEN_IX_LEVEL47 ON UMDATEN (LEVEL47);
CREATE INDEX UMDATEN_IX_LEVEL48 ON UMDATEN (LEVEL48);
CREATE INDEX UMDATEN_IX_LEVEL49 ON UMDATEN (LEVEL49);
CREATE INDEX UMDATEN_IX_LEVEL5 ON UMDATEN (LEVEL5);
CREATE INDEX UMDATEN_IX_LEVEL50 ON UMDATEN (LEVEL50);
CREATE INDEX UMDATEN_IX_LEVEL6 ON UMDATEN (LEVEL6);
CREATE INDEX UMDATEN_IX_LEVEL7 ON UMDATEN (LEVEL7);
CREATE INDEX UMDATEN_IX_LEVEL8 ON UMDATEN (LEVEL8);
CREATE INDEX UMDATEN_IX_LEVEL9 ON UMDATEN (LEVEL9);
CREATE INDEX UMDATEN_IX_LEVELDATE ON UMDATEN (LEVELDATE);
CREATE INDEX UMDATEN_IX_LEVELMONTH ON UMDATEN (LEVELMONTH);
CREATE INDEX UMDATEN_IX_LEVELWEEK ON UMDATEN (LEVELWEEK);
CREATE INDEX UMDATEN_IX_LEVELYEAR ON UMDATEN (LEVELYEAR);

/******************************************************************************/
/**** Privileges ****/
/******************************************************************************/

Table 2

/******************************************************************************/
/**** Generated by IBExpert 30.11.2006 20:08:23 ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES NONE;

/******************************************************************************/
/**** Tables ****/
/******************************************************************************/

CREATE TABLE AEDATEN (
LEVELDATE DATE NOT NULL,
LEVELWEEK INTEGER NOT NULL,
LEVELMONTH INTEGER NOT NULL,
LEVELYEAR INTEGER NOT NULL,
ERRORV VARCHAR(255) NOT NULL,
LEVEL0 CHAR(1) NOT NULL,
LEVEL1 CHAR(3) NOT NULL,
LEVEL2 CHAR(6) NOT NULL,
LEVEL3 CHAR(1) NOT NULL,
LEVEL4 CHAR(6) NOT NULL,
LEVEL5 CHAR(3) NOT NULL,
LEVEL6 CHAR(8) NOT NULL,
LEVEL7 CHAR(2) NOT NULL,
LEVEL8 CHAR(2) NOT NULL,
LEVEL9 CHAR(3) NOT NULL,
LEVEL10 CHAR(2) NOT NULL,
LEVEL11 CHAR(3) NOT NULL,
LEVEL12 CHAR(2) NOT NULL,
LEVEL13 CHAR(4) NOT NULL,
LEVEL14 CHAR(2) NOT NULL,
LEVEL15 CHAR(4) NOT NULL,
LEVEL16 CHAR(4) NOT NULL,
LEVEL17 CHAR(1) NOT NULL,
LEVEL18 CHAR(13) NOT NULL,
LEVEL19 CHAR(2) NOT NULL,
LEVEL20 CHAR(3) NOT NULL,
LEVEL21 CHAR(3) NOT NULL,
LEVEL22 CHAR(4) NOT NULL,
LEVEL23 CHAR(6) NOT NULL,
LEVEL24 CHAR(8) NOT NULL,
LEVEL25 CHAR(3) NOT NULL,
LEVEL26 CHAR(3) NOT NULL,
LEVEL27 CHAR(4) NOT NULL,
LEVEL28 CHAR(2) NOT NULL,
LEVEL29 CHAR(2) NOT NULL,
LEVEL30 CHAR(2) NOT NULL,
LEVEL31 CHAR(6) NOT NULL,
LEVEL32 CHAR(3) NOT NULL,
LEVEL33 CHAR(3) NOT NULL,
LEVEL34 CHAR(1) NOT NULL,
LEVEL35 CHAR(3) NOT NULL,
LEVEL36 CHAR(3) NOT NULL,
LEVEL37 CHAR(2) NOT NULL,
LEVEL38 CHAR(1) NOT NULL,
LEVEL39 CHAR(2) NOT NULL,
LEVEL40 CHAR(4) NOT NULL,
LEVEL41 CHAR(4) NOT NULL,
LEVEL42 CHAR(1) NOT NULL,
LEVEL43 CHAR(2) NOT NULL,
LEVEL44 CHAR(4) NOT NULL,
LEVEL45 CHAR(1) NOT NULL,
LEVEL46 CHAR(6) NOT NULL,
LEVEL47 CHAR(6) NOT NULL,
LEVEL48 CHAR(6) NOT NULL,
LEVEL49 CHAR(1) NOT NULL,
LEVEL50 CHAR(20) NOT NULL,
VALUE0 DECIMAL(15,4) NOT NULL,
VALUE1 DECIMAL(15,4) NOT NULL,
VALUE2 DECIMAL(15,4) NOT NULL,
VALUE3 DECIMAL(15,4) NOT NULL,
VALUE4 DECIMAL(15,4) NOT NULL,
VALUE5 DECIMAL(15,4) NOT NULL,
VALUE6 DECIMAL(15,4) NOT NULL,
VALUE7 DECIMAL(15,4) NOT NULL,
VALUE8 DECIMAL(15,4) NOT NULL,
VALUE9 DECIMAL(15,4) NOT NULL,
VALUE10 DECIMAL(15,4) NOT NULL,
VALUE11 DECIMAL(15,4) NOT NULL,
INFO0 DOUBLE PRECISION NOT NULL,
INFO1 DOUBLE PRECISION NOT NULL,
INFO2 DECIMAL(15,4) NOT NULL,
INFO3 INTEGER NOT NULL,
INFO4 INTEGER NOT NULL,
INFO5 VARCHAR(3) NOT NULL,
INFO13 DECIMAL(15,4) NOT NULL,
INFO14 DECIMAL(15,4) NOT NULL
);

/******************************************************************************/
/**** Indices ****/
/******************************************************************************/

CREATE DESCENDING INDEX AEDATEN_DLEVEL0 ON AEDATEN (LEVEL0);
CREATE DESCENDING INDEX AEDATEN_DLEVELDATE ON AEDATEN (LEVELDATE);
CREATE INDEX AEDATEN_IX_LEVEL0 ON AEDATEN (LEVEL0);
CREATE INDEX AEDATEN_IX_LEVEL1 ON AEDATEN (LEVEL1);
CREATE INDEX AEDATEN_IX_LEVEL10 ON AEDATEN (LEVEL10);
CREATE INDEX AEDATEN_IX_LEVEL11 ON AEDATEN (LEVEL11);
CREATE INDEX AEDATEN_IX_LEVEL12 ON AEDATEN (LEVEL12);
CREATE INDEX AEDATEN_IX_LEVEL13 ON AEDATEN (LEVEL13);
CREATE INDEX AEDATEN_IX_LEVEL14 ON AEDATEN (LEVEL14);
CREATE INDEX AEDATEN_IX_LEVEL15 ON AEDATEN (LEVEL15);
CREATE INDEX AEDATEN_IX_LEVEL16 ON AEDATEN (LEVEL16);
CREATE INDEX AEDATEN_IX_LEVEL17 ON AEDATEN (LEVEL17);
CREATE INDEX AEDATEN_IX_LEVEL18 ON AEDATEN (LEVEL18);
CREATE INDEX AEDATEN_IX_LEVEL19 ON AEDATEN (LEVEL19);
CREATE INDEX AEDATEN_IX_LEVEL2 ON AEDATEN (LEVEL2);
CREATE INDEX AEDATEN_IX_LEVEL20 ON AEDATEN (LEVEL20);
CREATE INDEX AEDATEN_IX_LEVEL21 ON AEDATEN (LEVEL21);
CREATE INDEX AEDATEN_IX_LEVEL22 ON AEDATEN (LEVEL22);
CREATE INDEX AEDATEN_IX_LEVEL23 ON AEDATEN (LEVEL23);
CREATE INDEX AEDATEN_IX_LEVEL24 ON AEDATEN (LEVEL24);
CREATE INDEX AEDATEN_IX_LEVEL25 ON AEDATEN (LEVEL25);
CREATE INDEX AEDATEN_IX_LEVEL26 ON AEDATEN (LEVEL26);
CREATE INDEX AEDATEN_IX_LEVEL27 ON AEDATEN (LEVEL27);
CREATE INDEX AEDATEN_IX_LEVEL28 ON AEDATEN (LEVEL28);
CREATE INDEX AEDATEN_IX_LEVEL29 ON AEDATEN (LEVEL29);
CREATE INDEX AEDATEN_IX_LEVEL3 ON AEDATEN (LEVEL3);
CREATE INDEX AEDATEN_IX_LEVEL30 ON AEDATEN (LEVEL30);
CREATE INDEX AEDATEN_IX_LEVEL31 ON AEDATEN (LEVEL31);
CREATE INDEX AEDATEN_IX_LEVEL32 ON AEDATEN (LEVEL32);
CREATE INDEX AEDATEN_IX_LEVEL33 ON AEDATEN (LEVEL33);
CREATE INDEX AEDATEN_IX_LEVEL34 ON AEDATEN (LEVEL34);
CREATE INDEX AEDATEN_IX_LEVEL35 ON AEDATEN (LEVEL35);
CREATE INDEX AEDATEN_IX_LEVEL36 ON AEDATEN (LEVEL36);
CREATE INDEX AEDATEN_IX_LEVEL37 ON AEDATEN (LEVEL37);
CREATE INDEX AEDATEN_IX_LEVEL38 ON AEDATEN (LEVEL38);
CREATE INDEX AEDATEN_IX_LEVEL39 ON AEDATEN (LEVEL39);
CREATE INDEX AEDATEN_IX_LEVEL4 ON AEDATEN (LEVEL4);
CREATE INDEX AEDATEN_IX_LEVEL40 ON AEDATEN (LEVEL40);
CREATE INDEX AEDATEN_IX_LEVEL41 ON AEDATEN (LEVEL41);
CREATE INDEX AEDATEN_IX_LEVEL42 ON AEDATEN (LEVEL42);
CREATE INDEX AEDATEN_IX_LEVEL43 ON AEDATEN (LEVEL43);
CREATE INDEX AEDATEN_IX_LEVEL44 ON AEDATEN (LEVEL44);
CREATE INDEX AEDATEN_IX_LEVEL45 ON AEDATEN (LEVEL45);
CREATE INDEX AEDATEN_IX_LEVEL46 ON AEDATEN (LEVEL46);
CREATE INDEX AEDATEN_IX_LEVEL47 ON AEDATEN (LEVEL47);
CREATE INDEX AEDATEN_IX_LEVEL48 ON AEDATEN (LEVEL48);
CREATE INDEX AEDATEN_IX_LEVEL49 ON AEDATEN (LEVEL49);
CREATE INDEX AEDATEN_IX_LEVEL5 ON AEDATEN (LEVEL5);
CREATE INDEX AEDATEN_IX_LEVEL50 ON AEDATEN (LEVEL50);
CREATE INDEX AEDATEN_IX_LEVEL6 ON AEDATEN (LEVEL6);
CREATE INDEX AEDATEN_IX_LEVEL7 ON AEDATEN (LEVEL7);
CREATE INDEX AEDATEN_IX_LEVEL8 ON AEDATEN (LEVEL8);
CREATE INDEX AEDATEN_IX_LEVEL9 ON AEDATEN (LEVEL9);
CREATE INDEX AEDATEN_IX_LEVELDATE ON AEDATEN (LEVELDATE);
CREATE INDEX AEDATEN_IX_LEVELMONTH ON AEDATEN (LEVELMONTH);
CREATE INDEX AEDATEN_IX_LEVELWEEK ON AEDATEN (LEVELWEEK);
CREATE INDEX AEDATEN_IX_LEVELYEAR ON AEDATEN (LEVELYEAR);

/******************************************************************************/
/**** Privileges ****/
/******************************************************************************/

The original SQL with IB-Expert

select a.*, b.*
from (
select level0, level1, level2
,sum(cast(Value1*Info0 as decimal(13, 2))) as f1
,count(distinct info3) as f2
,count(*) as f3
from umdaten
where level0='1' and level1='001'
group by 1,2,3
) a
left join (
select level0, level1, level2
,sum(cast(Value1*Info0 as decimal(13, 2))) as f4
,count(distinct info3) as f5
,count(*) as f6
from aedaten
where level0='1' and level1='001'
group by 1,2,3
) b
on a.level0=b.level0 and a.level1=b.level1 and a.level2=b.level2

Table AEDaten contains 252394 Records
Table UMDaten contains 173437 Records

Output from SQL-Monitor

[30.11.2006 20:12:27.975] : [Execute]
select a.*, b.*
from (
select level0, level1, level2
,sum(cast(Value1*Info0 as decimal(13, 2))) as f1
,count(distinct info3) as f2
,count(*) as f3
from umdaten
where level0='1' and level1='001'
group by 1,2,3
) a
left join (
select level0, level1, level2
,sum(cast(Value1*Info0 as decimal(13, 2))) as f4
,count(distinct info3) as f5
,count(*) as f6
from aedaten
where level0='1' and level1='001'
group by 1,2,3
) b
on a.level0=b.level0 and a.level1=b.level1 and a.level2=b.level2

[30.11.2006 20:12:46.362] : [Fetch]
LEVEL0 = 1
LEVEL1 = 001
LEVEL2 =
F1 = 134024524,32
F2 = 6222
F3 = 31065
LEVEL01 = 1
LEVEL11 = 001
LEVEL21 =
F4 = 142449217,04
F5 = 9322
F6 = 71143

And now i'm wondering !!!
The responsetime is 19 Seconds !?!

Output from IB-Expert

Plan:
PLAN JOIN (SORT (A UMDATEN INDEX (UMDATEN_IDX01QI345)), SORT (B AEDATEN INDEX (AEDATEN_IX_LEVEL2, AEDATEN_IX_LEVEL1, AEDATEN_IX_LEVEL0)))

Adapted plan:
PLAN JOIN (SORT (A UMDATEN INDEX (UMDATEN_IDX01QI345)), SORT (B AEDATEN INDEX (AEDATEN_IX_LEVEL2, AEDATEN_IX_LEVEL1, AEDATEN_IX_LEVEL0)))

Resultset contains 205 Records (this is correct).

And now the responsetimes from my application:

First Insert/Select from AEDaten 4,036 Seconds, Resultset 206 Records
Second Insert/Select from UMDaten 2,503 Seconds, Resultset 205 Records

Join-Query with 205-Records 0,161 Seconds

Responsetime including Create Tables and Index 6,87 Seconds !!!

I don't know the reason, why the Firebird-Engine answers for the Main-Select now in 19 Seconds ?!

But, i am faster with my own solution, solved for Firebird 1.5 because Views don't work so fast.

Additional Informations:

With Level1='001' are 283996 Records processed (sum from both tables, i don't have this from each one).
With Level1='042' (a second plant), only 5232 Records processed

In the second example my responsetime is 0,551
and from the main select:
[30.11.2006 20:33:44.942] : [Execute]
[30.11.2006 20:33:45.462] : [Fetch]

It seams near the same.

2. additional information.

I think, i found the reason for the long time response.
Because my application needs for statistics the processed records (see above), i have changed the obove select with an additional calulation. And this is it !

[30.11.2006 20:37:09.155] : [Execute]
[30.11.2006 20:42:11.290] : [Fetch]

This are only 5 Minutes, but i think that a cache is active when i retrieve the same data often.

Here the changed select:

select a.*, b.*
,(select count(*) from umdaten)
+ (select count(*) from aedaten)
, 0 as flag
from (
select level0, level1, level2
,sum(cast(Value1*Info0 as decimal(13, 2))) as f1
,count(distinct info3) as f2
,count(*) as f3
from umdaten
where level0='1' and level1='001'
group by 1,2,3
) a
left join (
select level0, level1, level2
,sum(cast(Value1*Info0 as decimal(13, 2))) as f4
,count(distinct info3) as f5
,count(*) as f6
from aedaten
where level0='1' and level1='001'
group by 1,2,3
) b
on a.level0=b.level0 and a.level1=b.level1 and a.level2=b.level2

The additional subselect's Count(*) i don't need in my application.

A word to my application:

I have programmed a new Cube-Analysis-Report and this work's very fast and robust with your Firebird 1.5 !

I have solved some problems by insert/select with temporary tables, but this currupts sometimes the garbage so i must save/restore the database with gbak (known 1.5-Problem).
Also i need temporary tables for the Index-Problem of Firebird 1.5 (solved in 2.0) with count(distinct field) if this field containes only 1 value in millions of records.

So my hopeness is, that i make combined select's "select from (select ...)" with up to 10 and more tables, because the temporary created tables are created and delete every time the user change his report.
Many metadata updates occurs so the garbage sometimes destroyes (endless loop in the Firebird server).

Puh, i hope you have enough informations, and sorry about my english.
Regards
Baldur F?rchau

If you understand german, have a look to http://www.ftsolutions.de, there is a short description of the program.

firebird-automations · 2008-01-28T15:22:50Z

Modified by: @pcisar

Workflow: jira [ 11368 ] => Firebird [ 15397 ]

firebird-automations added affect-version: 2.0.0 priority: major component: engine type: improvement labels Apr 25, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Performance with Subselect containing Group-By [CORE1034] #1451

Performance with Subselect containing Group-By [CORE1034] #1451

firebird-automations commented Nov 30, 2006

firebird-automations commented Nov 30, 2006

firebird-automations commented Nov 30, 2006

firebird-automations commented Nov 30, 2006

firebird-automations commented Nov 30, 2006

firebird-automations commented Dec 1, 2006

firebird-automations commented Jan 28, 2008

Performance with Subselect containing Group-By [CORE1034] #1451

Performance with Subselect containing Group-By [CORE1034] #1451

Comments

firebird-automations commented Nov 30, 2006

firebird-automations commented Nov 30, 2006

firebird-automations commented Nov 30, 2006

firebird-automations commented Nov 30, 2006

firebird-automations commented Nov 30, 2006

firebird-automations commented Dec 1, 2006

firebird-automations commented Jan 28, 2008