Firebird 5 performance worse than Firebird 4
33 views
Skip to first unread message
Fabiano SCI
Aug 11, 2026, 9:57:39 AM (2 days ago) Aug 11
to firebird-support
Hello folks!
We are migrating our installed customer base from Firebird 4.0.7 to 5.0.3, and we are encountering some queries that are not performing as well as they did in Firebird 4.
As a example is the following query:
select 0 BDSESSAO, PRO.BDCODPROD BDEDITCHAVE, PRO.BDCODPROD, PROV.BDCODEMP, coalesce(PROV.BDCODSAIDA, '') BDCODSAIDA,
PROV.BDAPEPROD BDAPEPROD, PROV.BDDESCPROD BDDESCPROD, PRO.BDAPELIDOUNICO BDAPELIDOUNICO,
PRO.BDDESCRICAOUNICA BDDESCRICAOUNICA, PRO.BDCODIGOEAN BDCODIGOEAN, coalesce(PRO.BDCODTIPO, 0) BDCODTIPO,
coalesce(PRO.BDCODGEN, 0) BDCODGEN, coalesce(PRO.BDCODSER, 0) BDCODSER,
coalesce(PRO.BDCODCOMBANP, 0) BDCODCOMBANP, coalesce(PROV.BDCODUNIMED, 0) BDCODUNIMED,
coalesce(PROV.BDNCMPROD, 0) BDNCMPROD, coalesce(PROV.BDCODTABINCID, 0) BDCODTABINCID,
coalesce(PROV.BDGRUPO, 0) BDGRUPO, coalesce(PROV.BDORDEMMARCA, 0) BDORDEMMARCA,
coalesce(PROV.BDCODCSTIPISAI, 0) BDCODCSTIPISAI, coalesce(PROV.BDCODCSTIPIENT, 0) BDCODCSTIPIENT,
coalesce(PROV.BDCODCSTICMS, 0) BDCODCSTICMS, coalesce(PROV.BDCODCSTPISSAI, 0) BDCODCSTPISSAI,
coalesce(PROV.BDCODCSTPISENT, 0) BDCODCSTPISENT, coalesce(PROV.BDCODCSTCOFENT, 0) BDCODCSTCOFENT,
coalesce(PROV.BDCODCSTCOFSAI, 0) BDCODCSTCOFSAI, PROV.BDINATIVO, PROV.BDPRODINCENTIVADO, PROV.BDCODITEMGIAF,
PROV.BDINCENTIVOTRANSP, PROV.BDOPEDIFIMPORTACAO, PROV.BDALIQFCP, PROV.BDPERCMVA
from TCADPRO PRO
left outer join TCADPRO_VINC PROV on PROV.BDCODPROD = PRO.BDCODPROD
left outer join TMP_TCADPRO TMP on (TMP.BDSESSAO = 0 and
TMP.BDCODPROD = PROV.BDCODPROD and
TMP.BDCODEMP = PROV.BDCODEMP)
where ((PROV.BDCODEMP is not null) and
(PROV.BDCODEMP > 0)) and
TMP.BDSESSAO is null
PROV.BDAPEPROD BDAPEPROD, PROV.BDDESCPROD BDDESCPROD, PRO.BDAPELIDOUNICO BDAPELIDOUNICO,
PRO.BDDESCRICAOUNICA BDDESCRICAOUNICA, PRO.BDCODIGOEAN BDCODIGOEAN, coalesce(PRO.BDCODTIPO, 0) BDCODTIPO,
coalesce(PRO.BDCODGEN, 0) BDCODGEN, coalesce(PRO.BDCODSER, 0) BDCODSER,
coalesce(PRO.BDCODCOMBANP, 0) BDCODCOMBANP, coalesce(PROV.BDCODUNIMED, 0) BDCODUNIMED,
coalesce(PROV.BDNCMPROD, 0) BDNCMPROD, coalesce(PROV.BDCODTABINCID, 0) BDCODTABINCID,
coalesce(PROV.BDGRUPO, 0) BDGRUPO, coalesce(PROV.BDORDEMMARCA, 0) BDORDEMMARCA,
coalesce(PROV.BDCODCSTIPISAI, 0) BDCODCSTIPISAI, coalesce(PROV.BDCODCSTIPIENT, 0) BDCODCSTIPIENT,
coalesce(PROV.BDCODCSTICMS, 0) BDCODCSTICMS, coalesce(PROV.BDCODCSTPISSAI, 0) BDCODCSTPISSAI,
coalesce(PROV.BDCODCSTPISENT, 0) BDCODCSTPISENT, coalesce(PROV.BDCODCSTCOFENT, 0) BDCODCSTCOFENT,
coalesce(PROV.BDCODCSTCOFSAI, 0) BDCODCSTCOFSAI, PROV.BDINATIVO, PROV.BDPRODINCENTIVADO, PROV.BDCODITEMGIAF,
PROV.BDINCENTIVOTRANSP, PROV.BDOPEDIFIMPORTACAO, PROV.BDALIQFCP, PROV.BDPERCMVA
from TCADPRO PRO
left outer join TCADPRO_VINC PROV on PROV.BDCODPROD = PRO.BDCODPROD
left outer join TMP_TCADPRO TMP on (TMP.BDSESSAO = 0 and
TMP.BDCODPROD = PROV.BDCODPROD and
TMP.BDCODEMP = PROV.BDCODEMP)
where ((PROV.BDCODEMP is not null) and
(PROV.BDCODEMP > 0)) and
TMP.BDSESSAO is null
It runs in 4m 51s 219ms but in Firebird 5 it runs on 9m 5s 875ms.
According to my observations its probably due to the plan used.
There is something I can do to improve the performance of Firebird 4 to match Firebird 5 that do not involve rewriting (and founding) all slow querys?
Firebird 4 plan:
PLAN JOIN (JOIN (PRO NATURAL, PROV INDEX (PK_TCADPRO_VINC)), TMP INDEX (TMP_TCADPRO_IDX5))
Firebird 5 plan:
PLAN JOIN (JOIN (PROV INDEX (IDX_EMPRESA_CODIGOPROD), PRO INDEX (PK_TCADPRO)), TMP INDEX (TMP_TCADPRO_IDX5))
Bellow the metada in case it is needed:
All Windows 11, 64 bits.
/******************************************************************************/
/**** Generated by IBExpert 11/08/2026 10:41:09 ****/
/******************************************************************************/
/******************************************************************************/
/**** Following SET SQL DIALECT is just for the Database Comparer ****/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE TABLE TCADPRO (
BDCODPROD INTEGER NOT NULL,
BDCODGEN INTEGER,
BDCODSER INTEGER,
BDCODTIPOSER INTEGER,
BDCODCOMBANP INTEGER,
BDAPELIDOUNICO VARCHAR(30) COLLATE WIN_PTBR,
BDDESCRICAOUNICA VARCHAR(200) COLLATE WIN_PTBR,
BDCODIGOEAN VARCHAR(20),
BDCODTIPO INTEGER,
BDCODCEST INTEGER,
BDPRODINCENTIVADO DM_BOOLEAN,
BDCODITEMGIAF INTEGER,
BDINCENTIVOTRANSP DM_BOOLEAN,
BDOPEDIFIMPORTACAO DM_BOOLEAN,
BDQTDARQUIVADO INTEGER
);
/******************************************************************************/
/**** Primary keys ****/
/******************************************************************************/
ALTER TABLE TCADPRO ADD CONSTRAINT PK_TCADPRO PRIMARY KEY (BDCODPROD);
/******************************************************************************/
/**** Foreign keys ****/
/******************************************************************************/
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_1 FOREIGN KEY (BDCODTIPO) REFERENCES TIPOS_LANCAMENTOS_PADRAO (BDCODTIPO);
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_2_TABGIAF FOREIGN KEY (BDCODITEMGIAF) REFERENCES VEF_BASE_TTABINFAD56 (BDORDEM56);
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_FKEF00487_TCEST FOREIGN KEY (BDCODCEST) REFERENCES TCEST (BDCODCEST);
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_FKSCI100_TGENEROP FOREIGN KEY (BDCODGEN) REFERENCES TGENEROPRO (BDCODGEN);
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_FKSCI101_TCODIGO_ FOREIGN KEY (BDCODSER) REFERENCES TCODIGO_SERVICO (BDCODSER);
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_FKSCI102_TTIPO_SE FOREIGN KEY (BDCODTIPOSER) REFERENCES TTIPO_SERVICO (BDCODTIPOSER);
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_FKVSUP950_VEF_BASE FOREIGN KEY (BDCODCOMBANP) REFERENCES VEF_BASE_TTABCOMBANP (BDCODCOMBANP);
/******************************************************************************/
/**** Indices ****/
/******************************************************************************/
CREATE INDEX IX_EAN ON TCADPRO (BDCODIGOEAN);
CREATE DESCENDING INDEX TCADPRONOVACHAVE ON TCADPRO (BDCODPROD);
CREATE INDEX TCADPRO_IDX_BDAPELIDOUNICO ON TCADPRO (BDAPELIDOUNICO);
CREATE INDEX TCADPRO_IDX_BDDESCRICAOUNICA ON TCADPRO (BDDESCRICAOUNICA);
/******************************************************************************/
/**** Triggers ****/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/**** Triggers for tables ****/
/******************************************************************************/
/* Trigger: TCADPRO_BD0 */
CREATE OR ALTER TRIGGER TCADPRO_BD0 FOR TCADPRO
ACTIVE BEFORE DELETE POSITION 0
AS
begin
if (EXISTS(SELECT FIRST 1 SAI.BDCODEMP
FROM VEF_EMP_TC197SAI SAI
WHERE (SAI.BDCODPROD = OLD.BDCODPROD))) then
EXCEPTION ex_delete 'VEF_EMP_TC197SAI';
if (EXISTS(SELECT FIRST 1 SAI.BDCODEMP
FROM VEF_EMP_TMOVPROSAI SAI
WHERE (SAI.BDCODPROD = OLD.BDCODPROD))) then
EXCEPTION ex_delete 'VEF_EMP_TMOVPROSAI';
if (EXISTS(SELECT FIRST 1 SAI.BDCODEMP
FROM VEF_EMP_TMOVPROSAIIMPORI SAI
WHERE (SAI.BDCODPROD = OLD.BDCODPROD))) then
EXCEPTION ex_delete 'VEF_EMP_TMOVPROSAIIMPORI';
end
^
/* Trigger: TCADPRO_BI0 */
CREATE OR ALTER TRIGGER TCADPRO_BI0 FOR TCADPRO
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE vCodigo INTEGER;
BEGIN
IF (NEW.BDCODTIPO = 1) THEN
BEGIN
/* Trigger text */
vcodigo = 0;
IF ((NEW.bdcodprod IS NULL) OR (NEW.bdcodprod = 0)) THEN
BEGIN
SELECT COALESCE((MAX(bdcodprod)+1),1) FROM tcadpro INTO vcodigo;
NEW.bdcodprod = vcodigo;
END
ELSE
BEGIN
SELECT bdcodprod FROM tcadpro WHERE bdcodprod = NEW.bdcodprod INTO vcodigo;
IF (vcodigo > 0) THEN
BEGIN
SELECT COALESCE((MAX(bdcodprod)+1),1) FROM tcadpro INTO vcodigo;
NEW.bdcodprod = vcodigo;
END
END
END
END
^
SET TERM ; ^
/******************************************************************************/
/**** Descriptions ****/
/******************************************************************************/
COMMENT ON TABLE TCADPRO IS
'Cadastro de produtos';
/******************************************************************************/
/**** Fields descriptions ****/
/******************************************************************************/
COMMENT ON COLUMN TCADPRO.BDCODPROD IS
'Código do produto';
COMMENT ON COLUMN TCADPRO.BDCODGEN IS
'Código genérico';
COMMENT ON COLUMN TCADPRO.BDCODSER IS
'Código do serviço';
COMMENT ON COLUMN TCADPRO.BDCODTIPOSER IS
'Código tipo do serviço';
COMMENT ON COLUMN TCADPRO.BDCODCOMBANP IS
'Código combinado';
COMMENT ON COLUMN TCADPRO.BDAPELIDOUNICO IS
'Apelido do produto no sistema';
COMMENT ON COLUMN TCADPRO.BDDESCRICAOUNICA IS
'Descrição do produto (única)';
COMMENT ON COLUMN TCADPRO.BDCODIGOEAN IS
'Código EAN';
COMMENT ON COLUMN TCADPRO.BDCODTIPO IS
'Código tipo';
COMMENT ON COLUMN TCADPRO.BDCODCEST IS
'Código cesta básica';
COMMENT ON COLUMN TCADPRO.BDPRODINCENTIVADO IS
'Produto com incentivo';
COMMENT ON COLUMN TCADPRO.BDCODITEMGIAF IS
'Código do item na GIA';
COMMENT ON COLUMN TCADPRO.BDINCENTIVOTRANSP IS
'Incentivo transportadora';
COMMENT ON COLUMN TCADPRO.BDOPEDIFIMPORTACAO IS
'Operação importação';
/******************************************************************************/
/**** Generated by IBExpert 11/08/2026 10:41:50 ****/
/******************************************************************************/
/******************************************************************************/
/**** Following SET SQL DIALECT is just for the Database Comparer ****/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE TABLE TCADPRO_VINC (
BDCODPROD INTEGER NOT NULL,
BDCODEMP INTEGER NOT NULL,
BDCODSAIDA VARCHAR(60),
BDDESCPROD VARCHAR(200) COLLATE WIN_PTBR,
BDAPEPROD VARCHAR(30) COLLATE WIN_PTBR,
BDCODUNIMED INTEGER,
BDNCMPROD INTEGER,
BDGRUPO INTEGER,
BDCODCSTIPIENT INTEGER,
BDCODCSTCOFENT INTEGER,
BDCODCSTPISENT INTEGER,
BDCODCSTPISSAI INTEGER,
BDCODCSTCOFSAI INTEGER,
BDCODCSTIPISAI INTEGER,
BDCODCSTICMS INTEGER,
BDCODTABINCID INTEGER,
BDORDEMMARCA INTEGER,
BDALIQICMS NUMERIC(7,4),
BDALIQICMSST NUMERIC(7,4),
BDALIQIPI NUMERIC(7,4),
BDALIQPIS NUMERIC(7,4),
BDALIQPISENT NUMERIC(7,4),
BDALIQCOF NUMERIC(7,4),
BDALIQCOFENT NUMERIC(7,4),
BDCAPVOL INTEGER,
BDINATIVO VARCHAR(5) NOT NULL,
BDCODTIPO INTEGER NOT NULL,
BDCODTIPOSER INTEGER,
BDCODGEN INTEGER,
BDCODSER INTEGER,
BDCODCOMBANP INTEGER,
BDCODTIPI INTEGER,
BDCODCEST INTEGER,
BDCREDOUT NUMERIC(7,4),
BDALIQICMSSTRET DM_NUMERIC_7_4_ALIQ,
BDALIQICMSEFETIVO DM_NUMERIC_7_4_ALIQ,
BDPRODINCENTIVADO DM_BOOLEAN,
BDCODITEMGIAF INTEGER,
BDINCENTIVOTRANSP DM_BOOLEAN,
BDOPEDIFIMPORTACAO DM_BOOLEAN,
BDBENEFINCENTFISCAL DM_BOOLEAN COLLATE WIN_PTBR,
BDALIQFCP DM_NUMERIC_7_4_ALIQ,
BDPERCMVA NUMERIC(7,2),
BDVALORUNI DM_NUMERIC_15_2,
BDCODNATBCREC INTEGER,
BDTIPOPROD INTEGER,
BDCODIAT VARCHAR(10) DEFAULT '',
BDCODCCLASSTRIBIBSCBS INTEGER,
BDCODCSTIBSCBS INTEGER,
BDALIQUOTAREDUCAO DM_NUMERIC_7_4_ALIQ,
BDALIQUOTACBS DM_NUMERIC_7_4_ALIQ,
BDCODCCLASSTRIBIS INTEGER,
BDCODCSTIBIS INTEGER,
BDCODUNTRIBUTAVEL INTEGER,
BDALIQUOTAIS DM_NUMERIC_7_4_ALIQ,
BDALIQUOTAESPECIFICAS DM_NUMERIC_7_4_ALIQ
);
/******************************************************************************/
/**** Primary keys ****/
/******************************************************************************/
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT PK_TCADPRO_VINC PRIMARY KEY (BDCODPROD, BDCODEMP);
/******************************************************************************/
/**** Foreign keys ****/
/******************************************************************************/
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKSCI092_TUNIDADE FOREIGN KEY (BDCODUNIMED) REFERENCES TUNIDADEMEDIDA (BDCODUNIMED);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKSCI093_TNCM FOREIGN KEY (BDNCMPROD) REFERENCES TNCM (BDNCMPROD);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKSCI264_TCODEXTIPI FOREIGN KEY (BDNCMPROD, BDCODTIPI) REFERENCES TCODEXTIPI (BDNCMPROD, BDCODTIPI);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP102_TIPOS_LA FOREIGN KEY (BDCODTIPO) REFERENCES TIPOS_LANCAMENTOS_PADRAO (BDCODTIPO);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP952_VEF_BASE FOREIGN KEY (BDCODTABINCID) REFERENCES VEF_BASE_TTABINCID (BDCODTABINCID);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP954_VEF_BASE FOREIGN KEY (BDCODTABINCID, BDGRUPO, BDORDEMMARCA) REFERENCES VEF_BASE_TTABMARCA (BDCODTABINCID, BDGRUPO, BDORDEMMARCA);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP955_VEF_BASE FOREIGN KEY (BDCODTABINCID, BDGRUPO) REFERENCES VEF_BASE_TTABGRUPOMC (BDCODTABINCID, BDGRUPO);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP956_VEF_BASE FOREIGN KEY (BDCODCSTIPISAI) REFERENCES VEF_BASE_TTABCSTIPI (BDCODCSTIPI);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP957_VEF_BASE FOREIGN KEY (BDCODCSTIPIENT) REFERENCES VEF_BASE_TTABCSTIPI (BDCODCSTIPI);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP958_VEF_BASE FOREIGN KEY (BDCODCSTICMS) REFERENCES VEF_BASE_TTABCSTICMS (BDCODCSTICMS);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP959_VEF_BASE FOREIGN KEY (BDCODCSTPISSAI) REFERENCES VEF_BASE_TTABCSTPIS (BDCODCSTPIS);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP960_VEF_BASE FOREIGN KEY (BDCODCSTPISENT) REFERENCES VEF_BASE_TTABCSTPIS (BDCODCSTPIS);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP961_VEF_BASE FOREIGN KEY (BDCODCSTCOFENT) REFERENCES VEF_BASE_TTABCSTCOF (BDCODCSTCOF);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP962_VEF_BASE FOREIGN KEY (BDCODCSTCOFSAI) REFERENCES VEF_BASE_TTABCSTCOF (BDCODCSTCOF);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC__FKSCI117_TCADPRO FOREIGN KEY (BDCODPROD) REFERENCES TCADPRO (BDCODPROD);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_1 FOREIGN KEY (BDCODPROD) REFERENCES TCADPRO (BDCODPROD) ON DELETE CASCADE;
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_2 FOREIGN KEY (BDCODTIPOSER) REFERENCES TTIPO_SERVICO (BDCODTIPOSER);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_3 FOREIGN KEY (BDCODGEN) REFERENCES TGENEROPRO (BDCODGEN);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_4 FOREIGN KEY (BDCODSER) REFERENCES TCODIGO_SERVICO (BDCODSER);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_5 FOREIGN KEY (BDCODCOMBANP) REFERENCES VEF_BASE_TTABCOMBANP (BDCODCOMBANP);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_6_TABGIAF FOREIGN KEY (BDCODITEMGIAF) REFERENCES VEF_BASE_TTABINFAD56 (BDORDEM56)
USING INDEX FK_TCADPRO_VINC_6;
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_CCLASSTRIBIBSCB FOREIGN KEY (BDCODCCLASSTRIBIBSCBS) REFERENCES VEF_BASE_TCLASSTRIB (BDCODCCLASSTRIB) ON DELETE SET NULL;
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_CCLASSTRIBIS FOREIGN KEY (BDCODCCLASSTRIBIS) REFERENCES VEF_BASE_TCLASSTRIB (BDCODCCLASSTRIB) ON DELETE SET NULL;
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_NATREC FOREIGN KEY (BDCODNATBCREC) REFERENCES VEF_BASE_TTAB_NATREC (BDCODNATREC) ON DELETE SET NULL;
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO__FKEF00486_TCEST FOREIGN KEY (BDCODCEST) REFERENCES TCEST (BDCODCEST);
/******************************************************************************/
/**** Indices ****/
/******************************************************************************/
CREATE INDEX IDX_EMPRESA_CODIGOPROD ON TCADPRO_VINC (BDCODEMP, BDCODPROD);
CREATE INDEX TCADPRO_VINC_IDX1 ON TCADPRO_VINC (BDCODEMP, BDCODSAIDA);
/******************************************************************************/
/**** Descriptions ****/
/******************************************************************************/
COMMENT ON TABLE TCADPRO_VINC IS
'Cadastro de produtos';
/******************************************************************************/
/**** Fields descriptions ****/
/******************************************************************************/
COMMENT ON COLUMN TCADPRO_VINC.BDCODIAT IS
'Código do produto vinculado no IAT';
/******************************************************************************/
/**** Generated by IBExpert 11/08/2026 10:42:09 ****/
/******************************************************************************/
/******************************************************************************/
/**** Following SET SQL DIALECT is just for the Database Comparer ****/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE TABLE TMP_TCADPRO (
BDSESSAO INTEGER NOT NULL,
BDEDITCHAVE INTEGER,
BDCODPROD INTEGER NOT NULL,
BDCODEMP INTEGER,
BDCODSAIDA VARCHAR(60) COLLATE WIN_PTBR,
BDAPEPROD VARCHAR(30) COLLATE WIN_PTBR,
BDDESCPROD VARCHAR(200) COLLATE WIN_PTBR,
BDAPELIDOUNICO VARCHAR(30) COLLATE WIN_PTBR,
BDDESCRICAOUNICA VARCHAR(200) COLLATE WIN_PTBR,
BDCODIGOEAN VARCHAR(20) COLLATE WIN_PTBR,
BDCODTIPO INTEGER,
BDCODGEN INTEGER,
BDCODSER INTEGER,
BDCODCOMBANP INTEGER,
BDCODUNIMED INTEGER,
BDAPEUNIMED VARCHAR(5) COLLATE WIN_PTBR,
BDNCMPROD INTEGER,
BDCODTABINCID INTEGER,
BDGRUPO INTEGER,
BDORDEMMARCA INTEGER,
BDCODCSTIPISAI INTEGER,
BDCODCSTIPIENT INTEGER,
BDCODCSTICMS INTEGER,
BDCODCSTPISSAI INTEGER,
BDCODCSTPISENT INTEGER,
BDCODCSTCOFENT INTEGER,
BDCODCSTCOFSAI INTEGER,
BDCODTER INTEGER,
BDCODENTRADA VARCHAR(60) COLLATE WIN_PTBR,
BDDESCPRODBUSCA VARCHAR(200) COLLATE WIN_PTBR,
BDINCONSISTENTE INTEGER,
BDORIGEMPROD INTEGER,
BDCODTERLAYOUT INTEGER,
BDCNPJTERLAYOUT VARCHAR(18) COLLATE WIN_PTBR,
BDSEQ_PART INTEGER,
BDINATIVO DM_BOOLEAN,
BDIETERLAYOUT VARCHAR(20),
BDPRODINCENTIVADO DM_BOOLEAN,
BDCODITEMGIAF INTEGER,
BDINCENTIVOTRANSP DM_BOOLEAN,
BDOPEDIFIMPORTACAO DM_BOOLEAN,
BDALIQFCP DM_NUMERIC_7_4_ALIQ,
BDPERCMVA NUMERIC(7,2)
);
/******************************************************************************/
/**** Indices ****/
/******************************************************************************/
CREATE INDEX TMP_TCADPRO_BDCODEMP_CODENT ON TMP_TCADPRO (BDSESSAO, BDCODENTRADA);
CREATE INDEX TMP_TCADPRO_BDCODEMP_CODSAI ON TMP_TCADPRO (BDSESSAO, BDCODSAIDA);
CREATE INDEX TMP_TCADPRO_BDEDITCHAVE ON TMP_TCADPRO (BDEDITCHAVE, BDCODEMP);
CREATE INDEX TMP_TCADPRO_CODSAIDESC ON TMP_TCADPRO (BDCODSAIDA, BDDESCPROD, BDCODEMP);
CREATE INDEX TMP_TCADPRO_DESCEAN ON TMP_TCADPRO (BDDESCPROD, BDCODIGOEAN);
CREATE INDEX TMP_TCADPRO_DESCEMP ON TMP_TCADPRO (BDDESCPROD, BDCODEMP);
CREATE INDEX TMP_TCADPRO_IDX1 ON TMP_TCADPRO (BDCODPROD, BDCODEMP);
CREATE INDEX TMP_TCADPRO_IDX10 ON TMP_TCADPRO (BDCODIGOEAN, BDDESCRICAOUNICA, BDCODEMP);
CREATE INDEX TMP_TCADPRO_IDX11 ON TMP_TCADPRO (BDCODSAIDA, BDCODEMP);
CREATE INDEX TMP_TCADPRO_IDX2 ON TMP_TCADPRO (BDDESCRICAOUNICA, BDCODEMP);
CREATE INDEX TMP_TCADPRO_IDX3 ON TMP_TCADPRO (BDEDITCHAVE, BDCODPROD, BDCODSAIDA, BDCODENTRADA, BDSESSAO);
CREATE INDEX TMP_TCADPRO_IDX4 ON TMP_TCADPRO (BDCODEMP, BDEDITCHAVE);
CREATE INDEX TMP_TCADPRO_IDX5 ON TMP_TCADPRO (BDSESSAO, BDCODPROD, BDCODEMP);
CREATE INDEX TMP_TCADPRO_IDX6 ON TMP_TCADPRO (BDCODEMP, BDCODSAIDA, BDCODPROD);
CREATE INDEX TMP_TCADPRO_IDX9 ON TMP_TCADPRO (BDCODIGOEAN, BDCODPROD);
CREATE INDEX TMP_TCADPRO_SESSAO_CHAVE ON TMP_TCADPRO (BDEDITCHAVE, BDSESSAO);
/**** Generated by IBExpert 11/08/2026 10:41:09 ****/
/******************************************************************************/
/******************************************************************************/
/**** Following SET SQL DIALECT is just for the Database Comparer ****/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE TABLE TCADPRO (
BDCODPROD INTEGER NOT NULL,
BDCODGEN INTEGER,
BDCODSER INTEGER,
BDCODTIPOSER INTEGER,
BDCODCOMBANP INTEGER,
BDAPELIDOUNICO VARCHAR(30) COLLATE WIN_PTBR,
BDDESCRICAOUNICA VARCHAR(200) COLLATE WIN_PTBR,
BDCODIGOEAN VARCHAR(20),
BDCODTIPO INTEGER,
BDCODCEST INTEGER,
BDPRODINCENTIVADO DM_BOOLEAN,
BDCODITEMGIAF INTEGER,
BDINCENTIVOTRANSP DM_BOOLEAN,
BDOPEDIFIMPORTACAO DM_BOOLEAN,
BDQTDARQUIVADO INTEGER
);
/******************************************************************************/
/**** Primary keys ****/
/******************************************************************************/
ALTER TABLE TCADPRO ADD CONSTRAINT PK_TCADPRO PRIMARY KEY (BDCODPROD);
/******************************************************************************/
/**** Foreign keys ****/
/******************************************************************************/
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_1 FOREIGN KEY (BDCODTIPO) REFERENCES TIPOS_LANCAMENTOS_PADRAO (BDCODTIPO);
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_2_TABGIAF FOREIGN KEY (BDCODITEMGIAF) REFERENCES VEF_BASE_TTABINFAD56 (BDORDEM56);
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_FKEF00487_TCEST FOREIGN KEY (BDCODCEST) REFERENCES TCEST (BDCODCEST);
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_FKSCI100_TGENEROP FOREIGN KEY (BDCODGEN) REFERENCES TGENEROPRO (BDCODGEN);
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_FKSCI101_TCODIGO_ FOREIGN KEY (BDCODSER) REFERENCES TCODIGO_SERVICO (BDCODSER);
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_FKSCI102_TTIPO_SE FOREIGN KEY (BDCODTIPOSER) REFERENCES TTIPO_SERVICO (BDCODTIPOSER);
ALTER TABLE TCADPRO ADD CONSTRAINT FK_TCADPRO_FKVSUP950_VEF_BASE FOREIGN KEY (BDCODCOMBANP) REFERENCES VEF_BASE_TTABCOMBANP (BDCODCOMBANP);
/******************************************************************************/
/**** Indices ****/
/******************************************************************************/
CREATE INDEX IX_EAN ON TCADPRO (BDCODIGOEAN);
CREATE DESCENDING INDEX TCADPRONOVACHAVE ON TCADPRO (BDCODPROD);
CREATE INDEX TCADPRO_IDX_BDAPELIDOUNICO ON TCADPRO (BDAPELIDOUNICO);
CREATE INDEX TCADPRO_IDX_BDDESCRICAOUNICA ON TCADPRO (BDDESCRICAOUNICA);
/******************************************************************************/
/**** Triggers ****/
/******************************************************************************/
SET TERM ^ ;
/******************************************************************************/
/**** Triggers for tables ****/
/******************************************************************************/
/* Trigger: TCADPRO_BD0 */
CREATE OR ALTER TRIGGER TCADPRO_BD0 FOR TCADPRO
ACTIVE BEFORE DELETE POSITION 0
AS
begin
if (EXISTS(SELECT FIRST 1 SAI.BDCODEMP
FROM VEF_EMP_TC197SAI SAI
WHERE (SAI.BDCODPROD = OLD.BDCODPROD))) then
EXCEPTION ex_delete 'VEF_EMP_TC197SAI';
if (EXISTS(SELECT FIRST 1 SAI.BDCODEMP
FROM VEF_EMP_TMOVPROSAI SAI
WHERE (SAI.BDCODPROD = OLD.BDCODPROD))) then
EXCEPTION ex_delete 'VEF_EMP_TMOVPROSAI';
if (EXISTS(SELECT FIRST 1 SAI.BDCODEMP
FROM VEF_EMP_TMOVPROSAIIMPORI SAI
WHERE (SAI.BDCODPROD = OLD.BDCODPROD))) then
EXCEPTION ex_delete 'VEF_EMP_TMOVPROSAIIMPORI';
end
^
/* Trigger: TCADPRO_BI0 */
CREATE OR ALTER TRIGGER TCADPRO_BI0 FOR TCADPRO
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE vCodigo INTEGER;
BEGIN
IF (NEW.BDCODTIPO = 1) THEN
BEGIN
/* Trigger text */
vcodigo = 0;
IF ((NEW.bdcodprod IS NULL) OR (NEW.bdcodprod = 0)) THEN
BEGIN
SELECT COALESCE((MAX(bdcodprod)+1),1) FROM tcadpro INTO vcodigo;
NEW.bdcodprod = vcodigo;
END
ELSE
BEGIN
SELECT bdcodprod FROM tcadpro WHERE bdcodprod = NEW.bdcodprod INTO vcodigo;
IF (vcodigo > 0) THEN
BEGIN
SELECT COALESCE((MAX(bdcodprod)+1),1) FROM tcadpro INTO vcodigo;
NEW.bdcodprod = vcodigo;
END
END
END
END
^
SET TERM ; ^
/******************************************************************************/
/**** Descriptions ****/
/******************************************************************************/
COMMENT ON TABLE TCADPRO IS
'Cadastro de produtos';
/******************************************************************************/
/**** Fields descriptions ****/
/******************************************************************************/
COMMENT ON COLUMN TCADPRO.BDCODPROD IS
'Código do produto';
COMMENT ON COLUMN TCADPRO.BDCODGEN IS
'Código genérico';
COMMENT ON COLUMN TCADPRO.BDCODSER IS
'Código do serviço';
COMMENT ON COLUMN TCADPRO.BDCODTIPOSER IS
'Código tipo do serviço';
COMMENT ON COLUMN TCADPRO.BDCODCOMBANP IS
'Código combinado';
COMMENT ON COLUMN TCADPRO.BDAPELIDOUNICO IS
'Apelido do produto no sistema';
COMMENT ON COLUMN TCADPRO.BDDESCRICAOUNICA IS
'Descrição do produto (única)';
COMMENT ON COLUMN TCADPRO.BDCODIGOEAN IS
'Código EAN';
COMMENT ON COLUMN TCADPRO.BDCODTIPO IS
'Código tipo';
COMMENT ON COLUMN TCADPRO.BDCODCEST IS
'Código cesta básica';
COMMENT ON COLUMN TCADPRO.BDPRODINCENTIVADO IS
'Produto com incentivo';
COMMENT ON COLUMN TCADPRO.BDCODITEMGIAF IS
'Código do item na GIA';
COMMENT ON COLUMN TCADPRO.BDINCENTIVOTRANSP IS
'Incentivo transportadora';
COMMENT ON COLUMN TCADPRO.BDOPEDIFIMPORTACAO IS
'Operação importação';
/******************************************************************************/
/**** Generated by IBExpert 11/08/2026 10:41:50 ****/
/******************************************************************************/
/******************************************************************************/
/**** Following SET SQL DIALECT is just for the Database Comparer ****/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE TABLE TCADPRO_VINC (
BDCODPROD INTEGER NOT NULL,
BDCODEMP INTEGER NOT NULL,
BDCODSAIDA VARCHAR(60),
BDDESCPROD VARCHAR(200) COLLATE WIN_PTBR,
BDAPEPROD VARCHAR(30) COLLATE WIN_PTBR,
BDCODUNIMED INTEGER,
BDNCMPROD INTEGER,
BDGRUPO INTEGER,
BDCODCSTIPIENT INTEGER,
BDCODCSTCOFENT INTEGER,
BDCODCSTPISENT INTEGER,
BDCODCSTPISSAI INTEGER,
BDCODCSTCOFSAI INTEGER,
BDCODCSTIPISAI INTEGER,
BDCODCSTICMS INTEGER,
BDCODTABINCID INTEGER,
BDORDEMMARCA INTEGER,
BDALIQICMS NUMERIC(7,4),
BDALIQICMSST NUMERIC(7,4),
BDALIQIPI NUMERIC(7,4),
BDALIQPIS NUMERIC(7,4),
BDALIQPISENT NUMERIC(7,4),
BDALIQCOF NUMERIC(7,4),
BDALIQCOFENT NUMERIC(7,4),
BDCAPVOL INTEGER,
BDINATIVO VARCHAR(5) NOT NULL,
BDCODTIPO INTEGER NOT NULL,
BDCODTIPOSER INTEGER,
BDCODGEN INTEGER,
BDCODSER INTEGER,
BDCODCOMBANP INTEGER,
BDCODTIPI INTEGER,
BDCODCEST INTEGER,
BDCREDOUT NUMERIC(7,4),
BDALIQICMSSTRET DM_NUMERIC_7_4_ALIQ,
BDALIQICMSEFETIVO DM_NUMERIC_7_4_ALIQ,
BDPRODINCENTIVADO DM_BOOLEAN,
BDCODITEMGIAF INTEGER,
BDINCENTIVOTRANSP DM_BOOLEAN,
BDOPEDIFIMPORTACAO DM_BOOLEAN,
BDBENEFINCENTFISCAL DM_BOOLEAN COLLATE WIN_PTBR,
BDALIQFCP DM_NUMERIC_7_4_ALIQ,
BDPERCMVA NUMERIC(7,2),
BDVALORUNI DM_NUMERIC_15_2,
BDCODNATBCREC INTEGER,
BDTIPOPROD INTEGER,
BDCODIAT VARCHAR(10) DEFAULT '',
BDCODCCLASSTRIBIBSCBS INTEGER,
BDCODCSTIBSCBS INTEGER,
BDALIQUOTAREDUCAO DM_NUMERIC_7_4_ALIQ,
BDALIQUOTACBS DM_NUMERIC_7_4_ALIQ,
BDCODCCLASSTRIBIS INTEGER,
BDCODCSTIBIS INTEGER,
BDCODUNTRIBUTAVEL INTEGER,
BDALIQUOTAIS DM_NUMERIC_7_4_ALIQ,
BDALIQUOTAESPECIFICAS DM_NUMERIC_7_4_ALIQ
);
/******************************************************************************/
/**** Primary keys ****/
/******************************************************************************/
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT PK_TCADPRO_VINC PRIMARY KEY (BDCODPROD, BDCODEMP);
/******************************************************************************/
/**** Foreign keys ****/
/******************************************************************************/
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKSCI092_TUNIDADE FOREIGN KEY (BDCODUNIMED) REFERENCES TUNIDADEMEDIDA (BDCODUNIMED);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKSCI093_TNCM FOREIGN KEY (BDNCMPROD) REFERENCES TNCM (BDNCMPROD);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKSCI264_TCODEXTIPI FOREIGN KEY (BDNCMPROD, BDCODTIPI) REFERENCES TCODEXTIPI (BDNCMPROD, BDCODTIPI);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP102_TIPOS_LA FOREIGN KEY (BDCODTIPO) REFERENCES TIPOS_LANCAMENTOS_PADRAO (BDCODTIPO);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP952_VEF_BASE FOREIGN KEY (BDCODTABINCID) REFERENCES VEF_BASE_TTABINCID (BDCODTABINCID);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP954_VEF_BASE FOREIGN KEY (BDCODTABINCID, BDGRUPO, BDORDEMMARCA) REFERENCES VEF_BASE_TTABMARCA (BDCODTABINCID, BDGRUPO, BDORDEMMARCA);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP955_VEF_BASE FOREIGN KEY (BDCODTABINCID, BDGRUPO) REFERENCES VEF_BASE_TTABGRUPOMC (BDCODTABINCID, BDGRUPO);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP956_VEF_BASE FOREIGN KEY (BDCODCSTIPISAI) REFERENCES VEF_BASE_TTABCSTIPI (BDCODCSTIPI);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP957_VEF_BASE FOREIGN KEY (BDCODCSTIPIENT) REFERENCES VEF_BASE_TTABCSTIPI (BDCODCSTIPI);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP958_VEF_BASE FOREIGN KEY (BDCODCSTICMS) REFERENCES VEF_BASE_TTABCSTICMS (BDCODCSTICMS);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP959_VEF_BASE FOREIGN KEY (BDCODCSTPISSAI) REFERENCES VEF_BASE_TTABCSTPIS (BDCODCSTPIS);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP960_VEF_BASE FOREIGN KEY (BDCODCSTPISENT) REFERENCES VEF_BASE_TTABCSTPIS (BDCODCSTPIS);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP961_VEF_BASE FOREIGN KEY (BDCODCSTCOFENT) REFERENCES VEF_BASE_TTABCSTCOF (BDCODCSTCOF);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC_FKVSUP962_VEF_BASE FOREIGN KEY (BDCODCSTCOFSAI) REFERENCES VEF_BASE_TTABCSTCOF (BDCODCSTCOF);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_PROVINC__FKSCI117_TCADPRO FOREIGN KEY (BDCODPROD) REFERENCES TCADPRO (BDCODPROD);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_1 FOREIGN KEY (BDCODPROD) REFERENCES TCADPRO (BDCODPROD) ON DELETE CASCADE;
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_2 FOREIGN KEY (BDCODTIPOSER) REFERENCES TTIPO_SERVICO (BDCODTIPOSER);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_3 FOREIGN KEY (BDCODGEN) REFERENCES TGENEROPRO (BDCODGEN);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_4 FOREIGN KEY (BDCODSER) REFERENCES TCODIGO_SERVICO (BDCODSER);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_5 FOREIGN KEY (BDCODCOMBANP) REFERENCES VEF_BASE_TTABCOMBANP (BDCODCOMBANP);
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_6_TABGIAF FOREIGN KEY (BDCODITEMGIAF) REFERENCES VEF_BASE_TTABINFAD56 (BDORDEM56)
USING INDEX FK_TCADPRO_VINC_6;
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_CCLASSTRIBIBSCB FOREIGN KEY (BDCODCCLASSTRIBIBSCBS) REFERENCES VEF_BASE_TCLASSTRIB (BDCODCCLASSTRIB) ON DELETE SET NULL;
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_CCLASSTRIBIS FOREIGN KEY (BDCODCCLASSTRIBIS) REFERENCES VEF_BASE_TCLASSTRIB (BDCODCCLASSTRIB) ON DELETE SET NULL;
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO_VINC_NATREC FOREIGN KEY (BDCODNATBCREC) REFERENCES VEF_BASE_TTAB_NATREC (BDCODNATREC) ON DELETE SET NULL;
ALTER TABLE TCADPRO_VINC ADD CONSTRAINT FK_TCADPRO__FKEF00486_TCEST FOREIGN KEY (BDCODCEST) REFERENCES TCEST (BDCODCEST);
/******************************************************************************/
/**** Indices ****/
/******************************************************************************/
CREATE INDEX IDX_EMPRESA_CODIGOPROD ON TCADPRO_VINC (BDCODEMP, BDCODPROD);
CREATE INDEX TCADPRO_VINC_IDX1 ON TCADPRO_VINC (BDCODEMP, BDCODSAIDA);
/******************************************************************************/
/**** Descriptions ****/
/******************************************************************************/
COMMENT ON TABLE TCADPRO_VINC IS
'Cadastro de produtos';
/******************************************************************************/
/**** Fields descriptions ****/
/******************************************************************************/
COMMENT ON COLUMN TCADPRO_VINC.BDCODIAT IS
'Código do produto vinculado no IAT';
/******************************************************************************/
/**** Generated by IBExpert 11/08/2026 10:42:09 ****/
/******************************************************************************/
/******************************************************************************/
/**** Following SET SQL DIALECT is just for the Database Comparer ****/
/******************************************************************************/
SET SQL DIALECT 3;
/******************************************************************************/
/**** Tables ****/
/******************************************************************************/
CREATE TABLE TMP_TCADPRO (
BDSESSAO INTEGER NOT NULL,
BDEDITCHAVE INTEGER,
BDCODPROD INTEGER NOT NULL,
BDCODEMP INTEGER,
BDCODSAIDA VARCHAR(60) COLLATE WIN_PTBR,
BDAPEPROD VARCHAR(30) COLLATE WIN_PTBR,
BDDESCPROD VARCHAR(200) COLLATE WIN_PTBR,
BDAPELIDOUNICO VARCHAR(30) COLLATE WIN_PTBR,
BDDESCRICAOUNICA VARCHAR(200) COLLATE WIN_PTBR,
BDCODIGOEAN VARCHAR(20) COLLATE WIN_PTBR,
BDCODTIPO INTEGER,
BDCODGEN INTEGER,
BDCODSER INTEGER,
BDCODCOMBANP INTEGER,
BDCODUNIMED INTEGER,
BDAPEUNIMED VARCHAR(5) COLLATE WIN_PTBR,
BDNCMPROD INTEGER,
BDCODTABINCID INTEGER,
BDGRUPO INTEGER,
BDORDEMMARCA INTEGER,
BDCODCSTIPISAI INTEGER,
BDCODCSTIPIENT INTEGER,
BDCODCSTICMS INTEGER,
BDCODCSTPISSAI INTEGER,
BDCODCSTPISENT INTEGER,
BDCODCSTCOFENT INTEGER,
BDCODCSTCOFSAI INTEGER,
BDCODTER INTEGER,
BDCODENTRADA VARCHAR(60) COLLATE WIN_PTBR,
BDDESCPRODBUSCA VARCHAR(200) COLLATE WIN_PTBR,
BDINCONSISTENTE INTEGER,
BDORIGEMPROD INTEGER,
BDCODTERLAYOUT INTEGER,
BDCNPJTERLAYOUT VARCHAR(18) COLLATE WIN_PTBR,
BDSEQ_PART INTEGER,
BDINATIVO DM_BOOLEAN,
BDIETERLAYOUT VARCHAR(20),
BDPRODINCENTIVADO DM_BOOLEAN,
BDCODITEMGIAF INTEGER,
BDINCENTIVOTRANSP DM_BOOLEAN,
BDOPEDIFIMPORTACAO DM_BOOLEAN,
BDALIQFCP DM_NUMERIC_7_4_ALIQ,
BDPERCMVA NUMERIC(7,2)
);
/******************************************************************************/
/**** Indices ****/
/******************************************************************************/
CREATE INDEX TMP_TCADPRO_BDCODEMP_CODENT ON TMP_TCADPRO (BDSESSAO, BDCODENTRADA);
CREATE INDEX TMP_TCADPRO_BDCODEMP_CODSAI ON TMP_TCADPRO (BDSESSAO, BDCODSAIDA);
CREATE INDEX TMP_TCADPRO_BDEDITCHAVE ON TMP_TCADPRO (BDEDITCHAVE, BDCODEMP);
CREATE INDEX TMP_TCADPRO_CODSAIDESC ON TMP_TCADPRO (BDCODSAIDA, BDDESCPROD, BDCODEMP);
CREATE INDEX TMP_TCADPRO_DESCEAN ON TMP_TCADPRO (BDDESCPROD, BDCODIGOEAN);
CREATE INDEX TMP_TCADPRO_DESCEMP ON TMP_TCADPRO (BDDESCPROD, BDCODEMP);
CREATE INDEX TMP_TCADPRO_IDX1 ON TMP_TCADPRO (BDCODPROD, BDCODEMP);
CREATE INDEX TMP_TCADPRO_IDX10 ON TMP_TCADPRO (BDCODIGOEAN, BDDESCRICAOUNICA, BDCODEMP);
CREATE INDEX TMP_TCADPRO_IDX11 ON TMP_TCADPRO (BDCODSAIDA, BDCODEMP);
CREATE INDEX TMP_TCADPRO_IDX2 ON TMP_TCADPRO (BDDESCRICAOUNICA, BDCODEMP);
CREATE INDEX TMP_TCADPRO_IDX3 ON TMP_TCADPRO (BDEDITCHAVE, BDCODPROD, BDCODSAIDA, BDCODENTRADA, BDSESSAO);
CREATE INDEX TMP_TCADPRO_IDX4 ON TMP_TCADPRO (BDCODEMP, BDEDITCHAVE);
CREATE INDEX TMP_TCADPRO_IDX5 ON TMP_TCADPRO (BDSESSAO, BDCODPROD, BDCODEMP);
CREATE INDEX TMP_TCADPRO_IDX6 ON TMP_TCADPRO (BDCODEMP, BDCODSAIDA, BDCODPROD);
CREATE INDEX TMP_TCADPRO_IDX9 ON TMP_TCADPRO (BDCODIGOEAN, BDCODPROD);
CREATE INDEX TMP_TCADPRO_SESSAO_CHAVE ON TMP_TCADPRO (BDEDITCHAVE, BDSESSAO);
Tomasz Dubiel
Aug 11, 2026, 10:00:09 AM (2 days ago) Aug 11
to firebird...@googlegroups.com
Hello.
Try with OuterJoinConversion = false in firebird.conf and restart Firebird service.
Best regards,
Tomek.
--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/078c4faf-edd5-4dbf-8ad0-894ba3f5a554n%40googlegroups.com.
liviuslivius
Aug 11, 2026, 10:27:04 AM (2 days ago) Aug 11
to firebird...@googlegroups.com
Or siply add +0 in ON condition for side you like to not use an index.
Regards,
Karol Bieniaszewski
-------- Oryginalna wiadomość --------
Od: Tomasz Dubiel <tkdu...@gmail.com>
Data: 11.08.2026 15:59 (GMT+01:00)
Temat: Re: [firebird-support] Firebird 5 performance worse than Firebird 4
To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/CAKyWEHWaSGfhBE6CtvyDB8DHTwduYtr33oJXMD8GpXhn%3DChhWQ%40mail.gmail.com.
Fabiano SCI
Aug 11, 2026, 11:24:39 AM (2 days ago) Aug 11
to firebird-support
Tomek that worked!
Fabiano SCI
Aug 11, 2026, 2:46:25 PM (2 days ago) Aug 11
to firebird-support
I would like to point also that the lastest Firebird version 5.0.4 (that I was not aware of) is slight faster now too.
Reply all
Reply to author
Forward
0 new messages