Smart refresh using zeos components

[leofsilv]

How to load only changed data into the resultset instead of loading all table in database? Every refresh i use sends a new query to the database loading always the whole table. It is very slow. Are there any way to retrieve only the changed data?

[seawolf]

At the moment there is no way to retrieve only changed data. I think this feature will not be added in a short time

[leofsilv]

Thanks for the answer. Considering the fact that both of the methods "open" and "refresh" load all data so, what is the real difference between using tzquery.open and tzquery.refresh?

[guidoaerts]

sorry if i am wrong, but i thought that the difference between the ztable and zquery is that ztable fetches the whole table, and zquery just the 'queried' resultset ?
guido

[slai]

is there any change in Zeos 7? I have a really big performance issue on refresh a table with many datas in it.

I have several ZTables i open on starting the Programm and this is really slow, is there no possibility to only load the datas from the DB if really needed, and then just the records who are needed?

THX a lot for reply!

[EgonHugeist]

slai,

which compiler do you use? And which protocol do you use here?

Well yes there are many changes done latly. The most of them are done concering the encoding stuff.

Has someody expieriences with speed increase for inline declarations?

[slai]

i use lazarus (fpc) 1.0.4 and zeos 7 with firebird 2.1 Transaction Level is set to RedCommited. Maybe FetchRow is what i need i'll do some tests now. What FetchRow Value would be the best for a Table, lets say of 1000 records, is there any formula of getting the best value?

[EgonHugeist]

Do you use FPC2.7.1 too? Here we've a compiler issue. Compare or assigna string from a PChar (PAnsiChar) is much slower (someone told me 100x slower) than with FPC2.6 because of the newly implemented AnsiString codepages and theire String origin tracking..

I don't know a nice number for fetch rows. What i know is:

There are protocols like SQLite where we can't edit the opened table if the fetch of all rows is incomple. But AFAIK FireBird is no such one. If you check for the TZTable.RecordCount or execute TZTable.Last then Zeos fetches all rows.

[EgonHugeist]

slai,

another thing:
which default characterset do you use? Is it possible your default database characterset is 'NONE'?

If yes then have a look to http://zeosbugs.firmos.at/view.php?id=272

before made this patch i did allways check the encoding for columns with no spezial collation. And check the encoding eats time...

[slai]

codepage is utf8 i also tried it with lazarus 0.9.28 because i have a project that runs only with that version correct at this momemt. But i have the same issue there. Maybe i have to do a huge refactoring to not use TZTable and only get the datas which i really need with zquery.

[EgonHugeist]

slai,

codepage is utf8

which one? Zeos-CodePage or the DEFAULT CHARACTERSET of FireBird. This is a big differnence accordingly the string processing of incoming values..

[slai]

I use DBDataMod.ZCDB.Properties.Add('codepage=UTF8'); so zeos codepage.

[slai]

i did perfomance mesurements, i have 1 table adressstamm this needs about 12 seconds to activate the ztable:

Can you see a problem from the ddl?

Code: Select all

CREATE TABLE ADRESSSTAMM (
  ID BIGINT NOT NULL,
  IANREDE INTEGER,
  IPLZORT1 BIGINT,
  IPLZORT2 BIGINT,
  BMITARBEITER T_BOOLEAN_CHAR NOT NULL,
  BLIEFERANT T_BOOLEAN_CHAR NOT NULL,
  BALTLIEFERANT T_BOOLEAN_CHAR NOT NULL,
  BKUNDE T_BOOLEAN_CHAR NOT NULL,
  BWIRTH T_BOOLEAN_CHAR NOT NULL,
  IPREISKLASSERAMPE BIGINT,
  IPREISKLASSEKUNDENKARTE BIGINT,
  IPREISKLASSEHAUSLIEFERDIENST BIGINT,
  IPREISKLASSERESTAURLIEFERDIENST BIGINT,
  BWIR T_BOOLEAN_CHAR NOT NULL,
  IWIRPROZENT INTEGER,
  IBCKONTO INTEGER,
  BOLMWSTPFLICHTIG T_BOOLEAN_CHAR NOT NULL,
  SKREDITLIMITE VARCHAR(500) CHARACTER SET UTF8 COLLATE UTF8,
  IZAHLUNGSKONDITION BIGINT,
  BGESCHAEFTSPARTNERID BLOB SUB_TYPE 2 SEGMENT SIZE 1,
  BGESPERT T_BOOLEAN_CHAR NOT NULL,
  BOLEIGENERGESCHAEFTSORT T_BOOLEAN_CHAR NOT NULL,
  BOLMONATSRECHNUNGBEWILLIGT T_BOOLEAN_CHAR NOT NULL,
  BOLGELOESCHT T_BOOLEAN_CHAR NOT NULL,
  IZAHLUNGSTEXT BIGINT,
  DGEBURTSTAG DATE,
  DMONATSRECHNUNGLIMITE DECIMAL(12, 2) DEFAULT 0.0,
  BOLKEINEDETAILMONATSRECHNUNG T_BOOLEAN_CHAR NOT NULL,
  IRECHNUNGSADRBEIBESTELLUNG BIGINT,
  IVERTRETERPROZENTSATZ SMALLINT,
  IKUNDENNR VARCHAR(20) CHARACTER SET UTF8 COLLATE UTF8,
  SFIRMENNAME VARCHAR(134) CHARACTER SET UTF8 COLLATE UTF8,
  SNAME1 VARCHAR(74) CHARACTER SET UTF8 COLLATE UTF8,
  SVORNAME1 VARCHAR(46) CHARACTER SET UTF8 COLLATE UTF8,
  STRASSENR1 VARCHAR(70) CHARACTER SET UTF8 COLLATE UTF8,
  STELNR1 VARCHAR(76) CHARACTER SET UTF8 COLLATE UTF8,
  SNATELNR1 VARCHAR(68) CHARACTER SET UTF8 COLLATE UTF8,
  SEMAILADRESSE1 VARCHAR(74) CHARACTER SET UTF8 COLLATE UTF8,
  STRASSENR2 VARCHAR(26) CHARACTER SET UTF8 COLLATE UTF8,
  STELNR2 VARCHAR(74) CHARACTER SET UTF8 COLLATE UTF8,
  SEMAILADRESSE2 VARCHAR(72) CHARACTER SET UTF8 COLLATE UTF8,
  SFAXNR VARCHAR(46) CHARACTER SET UTF8 COLLATE UTF8,
  SPCKONTO VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8,
  SBANKKONTONR VARCHAR(42) CHARACTER SET UTF8 COLLATE UTF8,
  STEILNEHMERNR VARCHAR(42) CHARACTER SET UTF8 COLLATE UTF8,
  IMWSTNUMMER VARCHAR(36) CHARACTER SET UTF8 COLLATE UTF8,
  SLAENDERKENNZEICHEN VARCHAR(4) CHARACTER SET UTF8 COLLATE UTF8,
  SIBANNR VARCHAR(56) CHARACTER SET UTF8 COLLATE UTF8,
  SPOSTFACH VARCHAR(36) CHARACTER SET UTF8 COLLATE UTF8,
  SHOMEPAGE VARCHAR(72) CHARACTER SET UTF8 COLLATE UTF8,
  SESRZKBVORGABE VARCHAR(12) CHARACTER SET UTF8 COLLATE UTF8);


ALTER TABLE ADRESSSTAMM ADD PRIMARY KEY (ID);

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_ANREDE FOREIGN KEY (IANREDE) REFERENCES ANREDEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_PKHAUSLDIENST FOREIGN KEY (IPREISKLASSEHAUSLIEFERDIENST) REFERENCES PREISKLASSEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_PKKUNDENKARTE FOREIGN KEY (IPREISKLASSEKUNDENKARTE) REFERENCES PREISKLASSEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_PKRESTLIEFER FOREIGN KEY (IPREISKLASSERESTAURLIEFERDIENST) REFERENCES PREISKLASSEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_PLRORT2 FOREIGN KEY (IPLZORT2) REFERENCES ORTSANGABEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_PLZORT FOREIGN KEY (IPLZORT1) REFERENCES ORTSANGABEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_PREISKLASSERAMPE FOREIGN KEY (IPREISKLASSERAMPE) REFERENCES PREISKLASSEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_ZAHLUNGSKOND FOREIGN KEY (IZAHLUNGSKONDITION) REFERENCES ZAHLUNGSKONDITIONEN(ID) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE ADRESSSTAMM ADD CONSTRAINT FK_ADRESSSTAMM_ZAHLUNGSTEXT FOREIGN KEY (IZAHLUNGSTEXT) REFERENCES ZAHLUNGSTEXTKUNDE(ID) ON DELETE SET NULL ON UPDATE CASCADE;


SET TERM ^ ;

CREATE TRIGGER BI_ADRESSSTAMM_ID FOR ADRESSSTAMM
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
      NEW.ID = GEN_ID(ADRESSSTAMM_ID_GEN, 1);
END^

SET TERM ; ^

thx

[slai]

i found the problem, it has on the ZTable IndexFieldNames SBEZEICHNUNG ASC, this is a dbColumn with VARCHAR(500) and has an Ascending index on it. If i have this IndexFieldNames configured to SBEZEICHNUNG Asc then it takes about 12 seconds if i have nothin in IndexFieldNames then it takes under 1 second to Activate the ZTable.

[EgonHugeist]

slai,

Can you see a problem from the ddl?

nope, i can't see an issue here. What i see is you use allways spezial collations for your string fields. So i ask again: What is your default characterset for your fdb? Is it possible this is charset 'NONE'? Then you can expect a speed decrease for columns without collation spezifications with Zeos7.0.x! On Zeos7.1 i've allready fixed this behavior: http://zeosbugs.firmos.at/view.php?id=272

ZTable IndexFieldNames SBEZEICHNUNG ASC, this is a dbColumn with VARCHAR(500) and has an Ascending index on it.

Oh there happens a big compare for 2 String values!

Mal so unter uns: Es ist meiner bescheidenen Meining nach nie eine gute Idee derartig große string-Felder für einen Index zu verwenden. ID's GUUID oder Integer-Values eignen sich immer besser dafür.

Anyway you told me this was no problem with the 6.6 series but now we've got a speed decrease.

Hmm all string compare processing happens in ZDataSetUtils.pas AFAIK. But i see no real differneces to the 6.6 Components accordingly this behavior.

Are you really sure your code didn't change?