[solved]DB-System recommendation for frequently update/inserts?
lazarus / 16-Databases

----------------------------------------------------------------------
Soner · 2019-10-16 12:01

I am using Firebird 2.5 Superclassic server and the database file is
550 MB.
The table with slow updates has 456018 rows.
I must often update ~100 rows at once. ~100 updates consumes from 55
to 65 seconds. (I deactivated for testing all indexes and triggers.
Also I am starting transaction at start and commit at end.)
I have another table with 17559 row, in this table is updating
multiple rows very fast.
I think updates are getting slower as tables are getting bigger.
Selecting is overall very fast.

Has anyone experiences with other databases with faster updates. Can
someone recommend a database with faster updates ?
What is with mysql/mariadb is there updates faster than in firebird?

----------------------------------------------------------------------
marcov · 2019-10-16 12:06

So, what is your primary key?

----------------------------------------------------------------------
Soner · 2019-10-16 12:41

Quote from: marcov on October 16, 2019, 02:06:03 pmSo, what is your
primary key?
Thanks for this question.
I looked again and find out that I forget for this table to add
primary index.
Now, it is very fast, less than 1 second.

----------------------------------------------------------------------
marcov · 2019-10-16 12:56

(on some DBsystems I was told you can also simply change a setting to
stop deduplicating rows, that was about mysql)

----------------------------------------------------------------------
Soner · 2019-10-16 13:14

I had a integer field in this table that I used it as primary key.
This field gets its value from generator but I forget it to mark as
primary key.
After marking it as primary key, updates/inserts are super fast now.
I can't belive how I forgot it in one of most important tables.