Recent

Author Topic: [solved]DB-System recommendation for frequently update/inserts?  (Read 1104 times)

Soner

  • Sr. Member
  • ****
  • Posts: 305
[solved]DB-System recommendation for frequently update/inserts?
« 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?
« Last Edit: October 16, 2019, 02:41:35 pm by Soner »

marcov

  • Administrator
  • Hero Member
  • *
  • Posts: 11383
  • FPC developer.
Re: DB-System recommendation for frequently update/inserts?
« Reply #1 on: October 16, 2019, 02:06:03 pm »
So, what is your primary key?

Soner

  • Sr. Member
  • ****
  • Posts: 305
Re: DB-System recommendation for frequently update/inserts?
« Reply #2 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.


marcov

  • Administrator
  • Hero Member
  • *
  • Posts: 11383
  • FPC developer.
Re: [solved]DB-System recommendation for frequently update/inserts?
« Reply #3 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)

Soner

  • Sr. Member
  • ****
  • Posts: 305
Re: [solved]DB-System recommendation for frequently update/inserts?
« Reply #4 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