Different plan in 3.0.10 comparing to 2.5.7 resulting in slow execution
131 views
Skip to first unread message
Tomasz Dubiel
Aug 23, 2022, 5:51:19 AM8/23/22
to firebird-support
Hello.
Could you tell me why there is a difference in performance between 3.0.10 and 2.5.7?
I'll give below DDL of table, but first the update:
update KOSZT
set KOSZT = KOSZT + WARTOSC - WARTOSC
where ID_ZLECENIE = 10032 and
ID_OPERACJA = 10063 and
ID_MELDUNEK is null and
PLANOWANY = 0;
set KOSZT = KOSZT + WARTOSC - WARTOSC
where ID_ZLECENIE = 10032 and
ID_OPERACJA = 10063 and
ID_MELDUNEK is null and
PLANOWANY = 0;
Firebird 3.0.10: PLAN (KOSZT INDEX (FK_KOSZT_MELDUNEK)). Execution: 62 ms.
Select Expression
-> Filter
-> Table "KOSZT" Access By ID
-> Bitmap
-> Index "FK_KOSZT_MELDUNEK" Range Scan (full match)
-> Filter
-> Table "KOSZT" Access By ID
-> Bitmap
-> Index "FK_KOSZT_MELDUNEK" Range Scan (full match)
Firebird 2.5.7: PLAN (KOSZT INDEX (FK_KOSZT_MELDUNEK, FK_KOSZT_OPERACJA)). Execution: 16 ms!!
As you can see, Firebird 3.0.10 uses only one foreign key, Firebird 2.5.7 uses 2. This is serious issue. In our system one activity can take several or more such updates. The more updates, the more performance decrease.
DDL:
/******************************************************************************/
/**** Generated by IBExpert 23.08.2022 11:50:10 ****/
/******************************************************************************/
/******************************************************************************/
/**** Following SET SQL DIALECT is just for the Database Comparer ****/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE TABLE KOSZT (
ID_KOSZT T_ID NOT NULL /* T_ID = INTEGER */,
ID_ZLECENIE T_ID NOT NULL /* T_ID = INTEGER */,
ID_OPERACJA T_ID NOT NULL /* T_ID = INTEGER */,
ID_MELDUNEK T_ID /* T_ID = INTEGER */,
PLANOWANY T_BOOLEANT DEFAULT 1 NOT NULL /* T_BOOLEANT = SMALLINT DEFAULT 1 */,
KM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KM_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KM_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KMD T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KMD_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KMD_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KO T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KO_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KO_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KK T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KK_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KK_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KR T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KR_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KR_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KG T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KG_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KG_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KN T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KN_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KN_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KD T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KD_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KD_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KOSZT T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
WARTOSC T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KP T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KP_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KP_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */
);
/******************************************************************************/
/**** Primary keys ****/
/******************************************************************************/
ALTER TABLE KOSZT ADD CONSTRAINT PK_KOSZT PRIMARY KEY (ID_KOSZT);
/******************************************************************************/
/**** Foreign keys ****/
/******************************************************************************/
ALTER TABLE KOSZT ADD CONSTRAINT FK_KOSZT_MELDUNEK FOREIGN KEY (ID_MELDUNEK) REFERENCES MELDUNEK (ID_MELDUNEK) ON DELETE CASCADE;
ALTER TABLE KOSZT ADD CONSTRAINT FK_KOSZT_OPERACJA FOREIGN KEY (ID_OPERACJA) REFERENCES OPERACJA (ID_OPERACJA) ON DELETE CASCADE;
ALTER TABLE KOSZT ADD CONSTRAINT FK_KOSZT_ZLECENIE FOREIGN KEY (ID_ZLECENIE) REFERENCES ZLECENIE (ID_ZLECENIE) ON DELETE CASCADE;
/******************************************************************************/
/**** Indices ****/
/******************************************************************************/
CREATE INDEX MK_PLANOWANY ON KOSZT (PLANOWANY);
/******************************************************************************/
/**** Triggers ****/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/**** Triggers for tables ****/
/******************************************************************************/
/* Trigger: KOSZT_AD */
CREATE OR ALTER TRIGGER KOSZT_AD FOR KOSZT
ACTIVE AFTER DELETE POSITION 0
AS
begin
IF ((old.id_meldunek is not null) and (old.planowany = 0)) THEN
update koszt set
km = km - old.km_razem,
kp = kp - old.kp_razem,
kmd = kmd - old.kmd_razem,
ko = ko - old.ko_razem,
kk = kk - old.kk_razem,
kr = kr - old.kr_razem,
kg = kg - old.kg_razem,
kn = kn - old.kn_razem,
kd = kd - old.kd_razem,
koszt = koszt - old.wartosc
where id_zlecenie = old.id_zlecenie
and id_operacja = old.id_operacja
and id_meldunek is null
and planowany = 0;
end
^
/* Trigger: KOSZT_AU */
CREATE OR ALTER TRIGGER KOSZT_AU FOR KOSZT
ACTIVE AFTER UPDATE POSITION 0
AS
begin
IF ((new.id_meldunek is not null) and (new.planowany = 0)) THEN
update koszt set
km = km + new.km_razem - old.km_razem,
kp = kp + new.kp_razem - old.kp_razem,
kmd = kmd + new.kmd_razem - old.kmd_razem,
ko = ko + new.ko_razem - old.ko_razem,
kk = kk + new.kk_razem - old.kk_razem,
kr = kr + new.kr_razem - old.kr_razem,
kg = kg + new.kg_razem - old.kg_razem,
kn = kn + new.kn_razem - old.kn_razem,
kd = kd + new.kd_razem - old.kd_razem,
koszt = koszt + new.wartosc - old.wartosc
where id_zlecenie = new.id_zlecenie
and id_operacja = new.id_operacja
and id_meldunek is null
and planowany = 0;
end
^
/* Trigger: KOSZT_BU0 */
CREATE OR ALTER TRIGGER KOSZT_BU0 FOR KOSZT
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE wNIEWLICZAJDOKOSZT integer;
begin
new.km_razem = new.KM + cast((new.KM * new.KM_NARZUT / 100) as numeric(18,4));
new.kp_razem = new.Kp + cast((new.Kp * new.Kp_NARZUT / 100) as numeric(18,4));
new.kmd_razem = new.KMd + cast((new.KMd * new.KMd_NARZUT / 100) as numeric(18,4));
new.ko_razem = new.Ko + cast((new.Ko * new.Ko_NARZUT / 100) as numeric(18,4));
new.kk_razem = new.Kk + cast((new.Kk * new.Kk_NARZUT / 100) as numeric(18,4));
new.kr_razem = new.Kr + cast((new.Kr * new.Kr_NARZUT / 100) as numeric(18,4));
new.kg_razem = new.Kg + cast((new.Kg * new.Kg_NARZUT / 100) as numeric(18,4));
new.kn_razem = new.Kn + cast((new.Kn * new.Kn_NARZUT / 100) as numeric(18,4));
new.kd_razem = new.Kd + cast((new.Kd * new.Kd_NARZUT / 100) as numeric(18,4));
IF ((new.planowany = 1) or (new.id_meldunek is not null)) THEN begin
wNIEWLICZAJDOKOSZT = 0;
select NIEWLICZAJDOKOSZT
from operacja
where id_Operacja = new.id_operacja
into :wNIEWLICZAJDOKOSZT;
new.koszt = new.kd_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 1) = 0) THEN
new.koszt = new.koszt + new.km_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 2) = 0) THEN
new.koszt = new.koszt + new.kp_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 4) = 0) THEN
new.koszt = new.koszt + new.kmd_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 8) = 0) THEN
new.koszt = new.koszt + new.ko_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 16) = 0) THEN
new.koszt = new.koszt + new.kk_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 32) = 0) THEN
new.koszt = new.koszt + new.kr_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 64) = 0) THEN
new.koszt = new.koszt + new.kg_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 128) = 0) THEN
new.koszt = new.koszt + new.kn_razem;
end
IF (new.koszt < 0) THEN
new.koszt = 0;
new.wartosc = new.koszt + cast((new.koszt * new.narzut / 100) as numeric(18,4));
end
^
SET TERM ; ^
/******************************************************************************/
/**** Fields descriptions ****/
/******************************************************************************/
COMMENT ON COLUMN KOSZT.KM_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KMD_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KO_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KK_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KR_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KG_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KN_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KD_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KOSZT IS
'Trigger';
COMMENT ON COLUMN KOSZT.WARTOSC IS
'Trigger';
COMMENT ON COLUMN KOSZT.KP_RAZEM IS
'Trigger';
/**** Generated by IBExpert 23.08.2022 11:50:10 ****/
/******************************************************************************/
/******************************************************************************/
/**** Following SET SQL DIALECT is just for the Database Comparer ****/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE TABLE KOSZT (
ID_KOSZT T_ID NOT NULL /* T_ID = INTEGER */,
ID_ZLECENIE T_ID NOT NULL /* T_ID = INTEGER */,
ID_OPERACJA T_ID NOT NULL /* T_ID = INTEGER */,
ID_MELDUNEK T_ID /* T_ID = INTEGER */,
PLANOWANY T_BOOLEANT DEFAULT 1 NOT NULL /* T_BOOLEANT = SMALLINT DEFAULT 1 */,
KM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KM_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KM_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KMD T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KMD_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KMD_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KO T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KO_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KO_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KK T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KK_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KK_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KR T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KR_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KR_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KG T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KG_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KG_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KN T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KN_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KN_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KD T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KD_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KD_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KOSZT T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
WARTOSC T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KP T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KP_NARZUT T_PROCENT DEFAULT 0 NOT NULL /* T_PROCENT = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
KP_RAZEM T_KWOTA DEFAULT 0 NOT NULL /* T_KWOTA = NUMERIC(18,4) DEFAULT 0 NOT NULL */
);
/******************************************************************************/
/**** Primary keys ****/
/******************************************************************************/
ALTER TABLE KOSZT ADD CONSTRAINT PK_KOSZT PRIMARY KEY (ID_KOSZT);
/******************************************************************************/
/**** Foreign keys ****/
/******************************************************************************/
ALTER TABLE KOSZT ADD CONSTRAINT FK_KOSZT_MELDUNEK FOREIGN KEY (ID_MELDUNEK) REFERENCES MELDUNEK (ID_MELDUNEK) ON DELETE CASCADE;
ALTER TABLE KOSZT ADD CONSTRAINT FK_KOSZT_OPERACJA FOREIGN KEY (ID_OPERACJA) REFERENCES OPERACJA (ID_OPERACJA) ON DELETE CASCADE;
ALTER TABLE KOSZT ADD CONSTRAINT FK_KOSZT_ZLECENIE FOREIGN KEY (ID_ZLECENIE) REFERENCES ZLECENIE (ID_ZLECENIE) ON DELETE CASCADE;
/******************************************************************************/
/**** Indices ****/
/******************************************************************************/
CREATE INDEX MK_PLANOWANY ON KOSZT (PLANOWANY);
/******************************************************************************/
/**** Triggers ****/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/**** Triggers for tables ****/
/******************************************************************************/
/* Trigger: KOSZT_AD */
CREATE OR ALTER TRIGGER KOSZT_AD FOR KOSZT
ACTIVE AFTER DELETE POSITION 0
AS
begin
IF ((old.id_meldunek is not null) and (old.planowany = 0)) THEN
update koszt set
km = km - old.km_razem,
kp = kp - old.kp_razem,
kmd = kmd - old.kmd_razem,
ko = ko - old.ko_razem,
kk = kk - old.kk_razem,
kr = kr - old.kr_razem,
kg = kg - old.kg_razem,
kn = kn - old.kn_razem,
kd = kd - old.kd_razem,
koszt = koszt - old.wartosc
where id_zlecenie = old.id_zlecenie
and id_operacja = old.id_operacja
and id_meldunek is null
and planowany = 0;
end
^
/* Trigger: KOSZT_AU */
CREATE OR ALTER TRIGGER KOSZT_AU FOR KOSZT
ACTIVE AFTER UPDATE POSITION 0
AS
begin
IF ((new.id_meldunek is not null) and (new.planowany = 0)) THEN
update koszt set
km = km + new.km_razem - old.km_razem,
kp = kp + new.kp_razem - old.kp_razem,
kmd = kmd + new.kmd_razem - old.kmd_razem,
ko = ko + new.ko_razem - old.ko_razem,
kk = kk + new.kk_razem - old.kk_razem,
kr = kr + new.kr_razem - old.kr_razem,
kg = kg + new.kg_razem - old.kg_razem,
kn = kn + new.kn_razem - old.kn_razem,
kd = kd + new.kd_razem - old.kd_razem,
koszt = koszt + new.wartosc - old.wartosc
where id_zlecenie = new.id_zlecenie
and id_operacja = new.id_operacja
and id_meldunek is null
and planowany = 0;
end
^
/* Trigger: KOSZT_BU0 */
CREATE OR ALTER TRIGGER KOSZT_BU0 FOR KOSZT
ACTIVE BEFORE UPDATE POSITION 0
AS
DECLARE VARIABLE wNIEWLICZAJDOKOSZT integer;
begin
new.km_razem = new.KM + cast((new.KM * new.KM_NARZUT / 100) as numeric(18,4));
new.kp_razem = new.Kp + cast((new.Kp * new.Kp_NARZUT / 100) as numeric(18,4));
new.kmd_razem = new.KMd + cast((new.KMd * new.KMd_NARZUT / 100) as numeric(18,4));
new.ko_razem = new.Ko + cast((new.Ko * new.Ko_NARZUT / 100) as numeric(18,4));
new.kk_razem = new.Kk + cast((new.Kk * new.Kk_NARZUT / 100) as numeric(18,4));
new.kr_razem = new.Kr + cast((new.Kr * new.Kr_NARZUT / 100) as numeric(18,4));
new.kg_razem = new.Kg + cast((new.Kg * new.Kg_NARZUT / 100) as numeric(18,4));
new.kn_razem = new.Kn + cast((new.Kn * new.Kn_NARZUT / 100) as numeric(18,4));
new.kd_razem = new.Kd + cast((new.Kd * new.Kd_NARZUT / 100) as numeric(18,4));
IF ((new.planowany = 1) or (new.id_meldunek is not null)) THEN begin
wNIEWLICZAJDOKOSZT = 0;
select NIEWLICZAJDOKOSZT
from operacja
where id_Operacja = new.id_operacja
into :wNIEWLICZAJDOKOSZT;
new.koszt = new.kd_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 1) = 0) THEN
new.koszt = new.koszt + new.km_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 2) = 0) THEN
new.koszt = new.koszt + new.kp_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 4) = 0) THEN
new.koszt = new.koszt + new.kmd_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 8) = 0) THEN
new.koszt = new.koszt + new.ko_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 16) = 0) THEN
new.koszt = new.koszt + new.kk_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 32) = 0) THEN
new.koszt = new.koszt + new.kr_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 64) = 0) THEN
new.koszt = new.koszt + new.kg_razem;
IF (bin_AND(:wNIEWLICZAJDOKOSZT, 128) = 0) THEN
new.koszt = new.koszt + new.kn_razem;
end
IF (new.koszt < 0) THEN
new.koszt = 0;
new.wartosc = new.koszt + cast((new.koszt * new.narzut / 100) as numeric(18,4));
end
^
SET TERM ; ^
/******************************************************************************/
/**** Fields descriptions ****/
/******************************************************************************/
COMMENT ON COLUMN KOSZT.KM_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KMD_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KO_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KK_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KR_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KG_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KN_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KD_RAZEM IS
'Trigger';
COMMENT ON COLUMN KOSZT.KOSZT IS
'Trigger';
COMMENT ON COLUMN KOSZT.WARTOSC IS
'Trigger';
COMMENT ON COLUMN KOSZT.KP_RAZEM IS
'Trigger';
Tomasz Dubiel
Aug 24, 2022, 2:10:39 AM8/24/22
to firebird-support
Does this look like a bug? Should I create an issue on GitHub?
Dmitry Yemanov
Aug 24, 2022, 2:43:52 AM8/24/22
to firebird...@googlegroups.com
24.08.2022 09:10, Tomasz Dubiel wrote:
> Does this look like a bug? Should I create an issue on GitHub?
This more looks like different cost estimation between these versions.
> Does this look like a bug? Should I create an issue on GitHub?
Which may be good for some queries and bad for other queries.
Are index statistics up-to-date in both versions?
How many records are in table KOSZT?
How many of them have ID_MELDUNEK is null?
What are selectivities for indices FK_KOSZT_MELDUNEK and FK_KOSZT_OPERACJA?
Dmitry
Tomasz Dubiel
Aug 24, 2022, 2:54:36 AM8/24/22
to firebird-support
Statistics are surely up to date. We got gbak from the customer, restored the database on FB 2.5 and on FB 3.0 to confirm the difference in the perfomance. We confirmed that.
There are 484 202 records in table KOSZT.
135 056 of them have ID_MELDUNEK is null.
Selectivities:
FK_KOSZT_MELDUNEK 0,00000572823000....
FK_KOSZT_MELDUNEK 0,00000572823000....
FK_KOSZT_OPERACJA 0,000014808671949...
Tomasz Dubiel
Aug 24, 2022, 3:01:49 AM8/24/22
to firebird-support
And
Execution: 62 ms
is the execution on the other server. By a mistake I gave that time, because I moved the database to other server to check whether it would behave differently.
The execution on the same server, with FB 3.0 is ~125 ms, so in this case FB 3.0 performs 7 times slower.
Dmitry Yemanov
Aug 24, 2022, 5:00:37 AM8/24/22
to firebird...@googlegroups.com
24.08.2022 09:54, Tomasz Dubiel wrote:
> Statistics are surely up to date. We got gbak from the customer,
> restored the database on FB 2.5 and on FB 3.0 to confirm the difference
> in the perfomance. We confirmed that.
> There are 484 202 records in table KOSZT.
> 135 056 of them have ID_MELDUNEK is null.
> Selectivities:
> FK_KOSZT_MELDUNEK 0,00000572823000....
> FK_KOSZT_OPERACJA 0,000014808671949...
So FK_KOSZT_OPERACJA (alone) is expected to return ~7 rows and
> Statistics are surely up to date. We got gbak from the customer,
> restored the database on FB 2.5 and on FB 3.0 to confirm the difference
> in the perfomance. We confirmed that.
> There are 484 202 records in table KOSZT.
> 135 056 of them have ID_MELDUNEK is null.
> Selectivities:
> FK_KOSZT_MELDUNEK 0,00000572823000....
> FK_KOSZT_OPERACJA 0,000014808671949...
FK_KOSZT_MELDUNEK (alone) is expected to return 2-3 rows so it wins. The
optimizer decides that scanning also FK_KOSZT_OPERACJA increases the IO
without gaining something.
The engine has no idea that the distribution is so much skewed and
actually there are 135056 NULLs there (instead of expected 2-3). This is
a known problem that cannot be solved easily (without having more stored
statistics).
A workaround could be to force using FK_KOSZT_OPERACJA instead of
FK_KOSZT_MELDUNEK:
where ID_ZLECENIE = 10032 and
ID_OPERACJA = 10063 and
PLANOWANY = 0;
This is likely to be a bit worse than using both indices but still
better than using FK_KOSZT_MELDUNEK.
Dmitry
Tomasz Dubiel
Aug 24, 2022, 5:26:38 AM8/24/22
to firebird-support
Thanks, it looks good.
"
This is a known problem that cannot be solved easily (without having more stored
statistics)"
I can provide you with more informations if you tell me what to do and I wil be able to do it
Tomasz Dubiel
Aug 25, 2022, 4:30:17 AM8/25/22
to firebird-support
That would be really appreciated to have this fixed without trying to change the plan with some tricks. This solution works only on given environment and is really not so much useful when having customers with big and small databases and with very different data in database tables. Firstly, I've already found other examples of SQL with various delay comparing to FB 2.5.
Secondly, our customers have different databases (the same structure, but various data in it). Your solution only works in situation when in table in given column is many NULLS, but is counterproductive in other situations.
Dmitry, I'm ready to cooperate with you and give you more needed details so that this problem could be solved globally.
Dmitry Yemanov
Aug 25, 2022, 4:39:38 AM8/25/22
to firebird...@googlegroups.com
25.08.2022 11:30, Tomasz Dubiel wrote:
> That would be really appreciated to have this fixed without trying to
> change the plan with some tricks. This solution works only on given
> environment and is really not so much useful when having customers with
> big and small databases and with very different data in database tables.
> Firstly, I've already found other examples of SQL with various delay
> comparing to FB 2.5.
Are they similar to this one (less indices are used) or completely
> That would be really appreciated to have this fixed without trying to
> change the plan with some tricks. This solution works only on given
> environment and is really not so much useful when having customers with
> big and small databases and with very different data in database tables.
> Firstly, I've already found other examples of SQL with various delay
> comparing to FB 2.5.
different cases?
> Secondly, our customers have different databases (the same structure,
> but various data in it). Your solution only works in situation when in
> table in given column is many NULLS, but is counterproductive in other
> situations.
> Dmitry, I'm ready to cooperate with you and give you more needed details
> so that this problem could be solved globally.
collecting/maintaining more statistics (number of NULLs, in particular),
which requires an ODS change and thus a non-option for all production FB
versions.
I may try fine-tuning the optimizer to please your examples, but there's
always a chance that some other queries will work slower after that...
Can you share some of these databases (privately)? Or create test
databases that demonstrate the same issue as production ones?
Dmitry
Ertan Küçükoglu
Aug 25, 2022, 4:59:12 AM8/25/22
to firebird...@googlegroups.com
Dmitry Yemanov <fire...@yandex.ru>, 25 Ağu 2022 Per, 11:39 tarihinde şunu yazdı:
25.08.2022 11:30, Tomasz Dubiel wrote:
As I said, I don't see any global solution without the engine
collecting/maintaining more statistics (number of NULLs, in particular),
which requires an ODS change and thus a non-option for all production FB
versions.
I vote for collecting more statistics for the next available version where ODS change is feasible.
Is there a bug report/feature request on that matter? I can go and support it.
It may be collected both for NULLs and EMPTY columns like '' for varchar-like types.
Tomasz Dubiel
Aug 25, 2022, 5:00:24 AM8/25/22
to firebird-support
Some SQL without specifying plan:
PLAN JOIN (D ORDER PK_DOSTAWA INDEX (FK_DOSTAWA_ST_MAG_DO_STANMAG), P INDEX (FK_PLANDOSTAWZP_DOSTAWA))
and much more indexed reads; table PLANDOSTAWZP 62 409, DOSTAWA 18 024
and with plan which is used on FB 2.5:
PLAN JOIN (P ORDER FK_PLANDOSTAWZP_DOSTAWA INDEX (FK_PLANDOSTAWZP_ZLECENIE, FK_PLANDOSTAWZP_MATERIAL, FK_PLANDOSTAWZP_MATERIAL), D INDEX (PK_DOSTAWA))
only 3 028 indexed reads from both tables
When it comes to the database, I will let you know later what can be done
Dmitry Yemanov
Aug 25, 2022, 5:13:03 AM8/25/22
to firebird...@googlegroups.com
25.08.2022 12:00, Tomasz Dubiel wrote:
> Some SQL without specifying plan:
> PLAN JOIN (D ORDER PK_DOSTAWA INDEX (FK_DOSTAWA_ST_MAG_DO_STANMAG), P
> INDEX (FK_PLANDOSTAWZP_DOSTAWA))
> and much more indexed reads; table PLANDOSTAWZP 62 409, DOSTAWA 18 024
> and with plan which is used on FB 2.5:
> PLAN JOIN (P ORDER FK_PLANDOSTAWZP_DOSTAWA INDEX
> (FK_PLANDOSTAWZP_ZLECENIE, FK_PLANDOSTAWZP_MATERIAL,
> FK_PLANDOSTAWZP_MATERIAL), D INDEX (PK_DOSTAWA))
> only 3 028 indexed reads from both tables
This is a different case, join order is changed here (was: P->D, now:
> Some SQL without specifying plan:
> PLAN JOIN (D ORDER PK_DOSTAWA INDEX (FK_DOSTAWA_ST_MAG_DO_STANMAG), P
> INDEX (FK_PLANDOSTAWZP_DOSTAWA))
> and much more indexed reads; table PLANDOSTAWZP 62 409, DOSTAWA 18 024
> and with plan which is used on FB 2.5:
> PLAN JOIN (P ORDER FK_PLANDOSTAWZP_DOSTAWA INDEX
> (FK_PLANDOSTAWZP_ZLECENIE, FK_PLANDOSTAWZP_MATERIAL,
> FK_PLANDOSTAWZP_MATERIAL), D INDEX (PK_DOSTAWA))
> only 3 028 indexed reads from both tables
D->P). But also worth investigating with an example, if possible.
Dmitry
Dmitry Yemanov
Aug 25, 2022, 5:13:17 AM8/25/22
to firebird...@googlegroups.com
25.08.2022 11:58, Ertan Küçükoglu wrote:
>
> I vote for collecting more statistics for the next available version
> where ODS change is feasible.
> Is there a bug report/feature request on that matter? I can go and
> support it.
https://github.com/FirebirdSQL/firebird/issues/1503
>
> I vote for collecting more statistics for the next available version
> where ODS change is feasible.
> Is there a bug report/feature request on that matter? I can go and
> support it.
> It may be collected both for NULLs and EMPTY columns like '' for
> varchar-like types.
value" case?
Anyway, empty strings IMO better should be accounted using histograms:
https://github.com/FirebirdSQL/firebird/issues/2111
Dmitry
Ertan Küçükoglu
Aug 25, 2022, 5:16:09 AM8/25/22
to firebird...@googlegroups.com
Dmitry Yemanov <fire...@yandex.ru>, 25 Ağu 2022 Per, 12:13 tarihinde şunu yazdı:
Because some developers tend to save empty strings and others NULLs for databases that I need to deal with. Probably lazy coding practice? I am not sure. It is a pain writing triggers just for fixing those and it may cause errors in the applications anyway.
Thanks for the links.
Tomasz Dubiel
Nov 18, 2022, 4:34:44 AM11/18/22
to firebird-support
Hello.
I came up with a pretty simple solution to this problem. In order to increase the performance in such situations when we put the column with many NULL values to the WHERE clause in statement,
it's enough to replace this NULL values in this column with minus value of primary key, for instance. We must assume that we never normally insert negative value to this column.
it's enough to replace this NULL values in this column with minus value of primary key, for instance. We must assume that we never normally insert negative value to this column.
czwartek, 25 sierpnia 2022 o 10:39:38 UTC+2 Dmitry Yemanov napisał(a):
Tomasz Dubiel
Nov 18, 2022, 4:35:52 AM11/18/22
to firebird-support
And, of course, after that, we need to recalculate the statistics for a given index.
Reply all
Reply to author
Forward
0 new messages