Lazarus

Programming => Databases => Topic started by: Soner on October 16, 2019, 02:01:26 pm

Title: [solved]DB-System recommendation for frequently update/inserts?
Post by: Soner on October 16, 2019, 02:01:26 pm
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?
Title: Re: DB-System recommendation for frequently update/inserts?
Post by: marcov on October 16, 2019, 02:06:03 pm
So, what is your primary key?
Title: Re: DB-System recommendation for frequently update/inserts?
Post by: Soner on October 16, 2019, 02:41:19 pm
So, 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.

Title: Re: [solved]DB-System recommendation for frequently update/inserts?
Post by: marcov on October 16, 2019, 02:56:38 pm
(on some DBsystems I was told you can also simply change a setting to stop deduplicating rows, that was about mysql)
Title: Re: [solved]DB-System recommendation for frequently update/inserts?
Post by: Soner on October 16, 2019, 03:14:32 pm
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.
TinyPortal © 2005-2018