My application was built for MySQL 5.0, and I'm seriously looking at
converting to Firebird 2.0. So far there appears to be only one
problem in doing so (as far as this application is concerned), and
this involves the INSERT statement.

The application is located on a PC and connects to the database server
via internet. Certain routines insert ultimately thousands of rows
per session. The original algorithm literally took about an hour to
complete:

START TRANSACTION
Create prepared statement
loop: insert data with prepared statement
END TRANSACTION

First, in MySQL, prepared statements really aren't faster than just
sending the whole query. Secondly, the application was waiting for a
round trip response: send data, wait, get response from database
server, send next insertion.

To address this, I changed the MySQL algorithm to use multiple row
inserts per query:

START TRANSACTION
INSERT INTO table1 (col1, col2) VALUES (row1_1, row1_2), (row2_1,
row2_2), (row3_1, row3_2);
END TRANSACTION

This technique resulted in literally being 1/100th of the original
time, completing in well under a minute.


The problem is that I just discovered Firebird 2.0 does not support
multiple inserts per query. (Right? can somebody confirm?)
Since I believe the original problem was mainly caused by the
round-trips, then the obvious solution is that the inserts must be
sent all at once. MySQL also has the ability for the client to send
multiple statements with one query, even getting by multiple rowsets,
e.g. ("INSERT INTO table1 VALUES (row1_1, row1_2); INSERT INTO table1
VALUES (row2_1, row2_2);")

Does Firebird have the ability to process multiple statements per query?
Is there any other technique where the entire data can be sent in
large batches?
Is there a way to tell firebird not to index the separate insertions
until the transaction is complete?

Truthfully I was surprised that Firebird 2.0 doesn't have multiple-row
inserts. I think this will be a dealbreaker since I don't expect
Firebird to fair any better on long-distance single insertion
statements than mysql did. I appreciate any insight you guys may have
on being able to resolve this cleanly.

Thanks,
John