Subject Re: [firebird-support] Re: Simultaneous inserts to the same table.
Author Fabiano Kureck - Desenvolvimento SCI
---In, <fabiano@...> wrote :

>>IMHO what is going on is that Firebird is using the lock file (for Classic installation) to synchronize between >>various connections (threads in this case) to allow one by one to write to FDB file.

I hope that Vlad or Dmitry will tell us how this works for real, so we wouldn't have to guess anymore:)

>>I also tested this and if you run various threads inserting among different tables there are no performance >>issues.

I've tested it briefly and by using two threads and two different tables I can double the performance. However more threads/tables do not seem to speed up things.

>>The fastest way I found to insert multiple rows in a Firebird database is run a Stored Procedure that >>contains various inserts in form of a long string. The SP's splits this string in various inserts.

This looks like a variant of method in which all inserts all generated as a big textfile on the client side and then executed on the server like one script. Have you compared your method to this? Because I think your SP_INSERTS might introduce additional overhead (you need build this script on server side).
Yes. Text file is faster however there is a overhead of creating a new file before Firebird can you it, so my approach is faster, overall. When I finish building first scrip I call a thread that send this to Firebird server. While the thread is sengind (waiting) I'm free to collect and create a new 'line'. When this second line is done I send again to the Thread. If thread is busy due waiting from Firebird server I wait until it is free and then send to it.
So I can process a new line until I'm sending data do Firebird server. 2 Threads, one for creating data and one for sending data.
This approach is faster than Text file because I don't need to wait while entire text file is done and there is no overhead.
Got it?

I think both variants might speed things up because you send only one request via Firebird API and network. I will definitely test this out.
Yes, this is the trick.

>>With this >>approach I can insert around 11,000 recods/second! (if there are no Indexes, even PK).

11000 records / seconds does not say much, we do not know what system do you have and so on. If you would put this in comparision to the standard method of inserting then we would know how much faster it is.
For example, as you see from my test, I am using a standard method and on SSD it is faster than your method: 100000 / 7 seconds gives 14000 records / second.

Sorry, is in my developer machine. W7, 4Gb RAM, 1 SATA HDD (it is very slow IMPOV), FB 2.5.1, nothing special.

>>I hope I can help you, and sorry about some English mistakes.

No need to appologize, I've made many mistakes in my previous post (and in this probably too :) ). Thank you for your help Fabiano!

Best regards.