Frequently updated table suggestion

[Ertan Küçükoglu]

Hello,

I am using FirebirdSQL 4.0.2 on Windows.

There is an electronic invoice users table with a single secondary index on a varchar identification column. There is no primary index. Table is updated according to the latest version of data received from a web service. Data is an XML file compressed in ZIP format.

XML is mostly increasing each day/week by several or more records. Sometimes there are users removed from that list. There is no information of removed users in that XML. In order not to file a wrong invoice, I completely delete all records in that table and insert all received from the XML. This is about 700.000 records in total for now.

Having no information on deleted records, I cannot use insert or update + delete. I remember reading that frequent delete and insert is not advised. I cannot find the source of that information now. Assuming there is a way to identify deleted records, I will still have to update all information as there may be an update to it.

I would like to have suggestions on alternative methods of updating that table data as I do not like the existing way of solution, but I cannot think of a way to identify these deleted user records.

Any help is appreciated.

Thanks & Regards,

Ertan

[Mark Rotteveel]

On 06-11-2022 10:06, Ertan Küçükoglu wrote:
> I would like to have suggestions on alternative methods of updating that
> table data as I do not like the existing way of solution, but I cannot
> think of a way to identify these deleted user records.

You could use the RDB$RECORD_VERSION pseudo-column (introduced in
Firebird 3.0[1]) to identify records that were not updated.

Basically, RDB$RECORD_VERSION < CURRENT_TRANSACTION would find records
not modified by the current transaction.

Mark

[1]:
https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html#rnfb3-engine-misc-record-version
--
Mark Rotteveel

[Dimitry Sibiryakov]

Ertan Küçükoglu wrote 06.11.2022 10:06:
> I would like to have suggestions on alternative methods of updating that table
> data as I do not like the existing way of solution, but I cannot think of a way
> to identify these deleted user records.

It depends on your circumstances and goals.

a) Use RECREATE TABLE to clean the table. It will solve problem with garbage
collection.
b) Navigation of both databasets ordered by the unique key will give you ability
to insert missing, update existing and delete those records that are not in the
new source. Volume of garbage collection will not change, neither speed of
process but you will be satisfied because of "doing things right way".
c) Make the web site working with the database directly (or vice versa - work
with site database directly). Only really changed data will get to database
decreasing volume of work dramatically. This is the best way, BTW.
d) Give up Firebird database and use for your applications the XML directly.
Fast and descent XML parsers are hard to find but they may exist.

--
WBR, SD.

[Ertan Küçükoglu]

After reading suggestions,

- I decided to add a modified_at column in that table and use it for determining records not modified aka deleted.

- I also decided not to delete all records but use a merge statement to update existing records or insert new ones.

However, my below merge statement is not inserting new records in my tests.

merge into einvoiceusers e

using (select vkntckn from einvoiceusers where vkntckn='12345') e2

on (e.vkntckn = e2.vkntckn)

when matched then update set pk='PK',name='NAME',type='TYPE',"date"='2022-11-06',mailbox='MAILBOX',created_at='2022-11-06',modified_at=current_timestamp

when not matched then insert(vkntckn,pk,type,"date",mailbox,created_at) values('12345','PK','NAME','2022-11-06','MAILBOX','2022-11-06');

I do know there is no record with vkntckn='12345' in the table. I could not find what I am missing obvious here.

Table create statement is as follows

CREATE DOMAIN ATEXT AS BLOB SUB_TYPE TEXT;

CREATE TABLE EINVOICEUSERS (

  VKNTCKN     VARCHAR(25) NOT NULL,

  PK          ATEXT,

  "NAME"      ATEXT,

  "TYPE"      ATEXT,

  "date"      TIMESTAMP,

  MAILBOX     ATEXT,

  CREATED_AT  TIMESTAMP DEFAULT LOCALTIMESTAMP,

  MODIFIED_AT TIMESTAMP DEFAULT LOCALTIMESTAMP

);

CREATE INDEX IDX_EINVOICEUSERS_VKNTCKN ON EINVOICEUSERS (VKNTCKN);

Any help is appreciated.

Thanks & Regards,

Ertan

'Dimitry Sibiryakov' via firebird-support <firebird...@googlegroups.com>, 6 Kas 2022 Paz, 14:02 tarihinde şunu yazdı:

--
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 on the web, visit https://groups.google.com/d/msgid/firebird-support/9898a94c-3e50-ce0e-dace-12901b3ff6b0%40ibphoenix.com.

[Mark Rotteveel]

On 06-11-2022 15:43, Ertan Küçükoglu wrote:
> However, my below merge statement is not inserting new records in my tests.
>
> merge into einvoiceusers e
> using (select vkntckn from einvoiceusers where vkntckn='12345') e2
> on (e.vkntckn = e2.vkntckn)
> when matched then update set
> pk='PK',name='NAME',type='TYPE',"date"='2022-11-06',mailbox='MAILBOX',created_at='2022-11-06',modified_at=current_timestamp
> when not matched then insert(vkntckn,pk,type,"date",mailbox,created_at)
> values('12345','PK','NAME','2022-11-06','MAILBOX','2022-11-06');
>
> I do know there is no record with vkntckn='12345' in the table. I could
> not find what I am missing obvious here.

Your statement makes no sense. You may want to read up on how merge
works. The `using` clause is the *source* of data, which drives
subsequent actions on the target table. For each row in the source of
data, it is matched with the *target* of the merge (specified in the
`into` clause).

Given there is no record with vkntckn='12345' in einvoiceusers,
*nothing* happens.

In equivalent PSQL code, the merge you wrote is like

for select vkntckn from einvoiceusers where vkntckn='12345' into vkntckn do
begin
if (exists(select * from einvoiceusers where vkntckn = :vkntckn) then
begin
update set ... where vkntckn = :vkntckn;
end
else
begin
insert ...;
end
end

The FOR SELECT would loop zero times, because there are no matching
rows. The exact same thing applies to MERGE.

Maybe you're thinking of UPDATE OR INSERT[1] instead.

Mark

[1]:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-dml-update-or-insert
--
Mark Rotteveel

[Dimitry Sibiryakov]

Ertan Küçükoglu wrote 06.11.2022 15:43:
> After reading suggestions,
>
> - I decided to add a modified_at column in that table and use it for determining
> records not modified aka deleted.
> - I also decided not to delete all records but use a merge statement to update
> existing records or insert new ones.

Both of these make no sense from performance POV so I repeat once again: what
are you trying to get?

--
WBR, SD.

[Karol Bieniaszewski]

Simply create global temporary table with e.g. ON COMMIT DELETE ROWS and fill it with new xml data.

 

And do below 2 statements in same transaction as above inserts into global temporary table:

merge into einvoiceusers e

using (select * from TMP_NEW_XML) e2

on (e.vkntckn = e2.vkntckn)

when matched

AND (

e.PK IS DISTINCT FROM e2.PK

or e.NAME IS DISTINCT FROM e2.NAME

or e.TYPE IS DISTINCT FROM e2.TYPE

or e.date IS DISTINCT FROM e2.date

or e.MAILBOX IS DISTINCT FORM e2. MAILBOX) then update set pk=e2.pk, name=e2.name, type=e2.type, e.date=e2.date,mailbox=e2.mailbox, created_at=e2.create_at,modified_at=current_timestamp

when not matched then insert(vkntckn,pk,type,"date",mailbox,created_at) values(e2. Vkntckn, e2.pk, e2.pk, e2.Type, e2. Date, e2.Mailbox, e2.created_at);

 

DELETE FROM einvoiceusers e WHERE NOT EXISTS(SELECT * FROM TMP_NEW_XML T where T. vkntckn=e. vkntckn)

 

Regards,

Karol Bieniaszewski

--

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 on the web, visit https://groups.google.com/d/msgid/firebird-support/CAH2i4yd_ypG0u%2Bdt45e5YgR1Z0c%2BOOv79vaBNDPWtxob1Xw17w%40mail.gmail.com.

 

[Dimitry Sibiryakov]

Karol Bieniaszewski wrote 06.11.2022 18:06:
> Simply create global temporary table with e.g. ON COMMIT DELETE ROWS and fill it
> with new xml data.

It will be slower than RECREATE TABLE + INSERT.

--
WBR, SD.

[Ertan Küçükoglu]

Hello,

At the beginning, I was trying to find a solution not to drop the table and insert everything from scratch. Right now, I am trying to have an SQL statement that works both on SQL Server and FirebirdSQL. Identical merge statements work on both.

I did read about merge before I came up with my initial version. It turns out I didn't understand it well. Reading Mark's answer, the updated version below works fine.

merge into einvoiceusers e

using (select '12345' as vkntckn from rdb$database) e2

on (e.vkntckn = e2.vkntckn)

when matched then update set pk='PK',name='NAME',type='TYPE',"date"='2022-11-06',mailbox='MAILBOX',created_at='2022-11-06',modified_at=current_timestamp

when not matched then insert(vkntckn,pk,type,"date",mailbox,created_at) values('12345','PK','NAME','2022-11-06','MAILBOX','2022-11-06');

I know about update and insert on FirebirdSQL, but the SQL Server version is something I do not want to use.

Now, I am going to build an SQL Server version. Later FirebirdSQL version is to be followed.

Thanks & Regards,

Ertan

'Dimitry Sibiryakov' via firebird-support <firebird...@googlegroups.com>, 6 Kas 2022 Paz, 18:56 tarihinde şunu yazdı:

--
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 on the web, visit https://groups.google.com/d/msgid/firebird-support/119245bc-04b9-6d5b-98cd-fc4c7486e3bf%40ibphoenix.com.