---In [email protected], <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.
