interbase slow inserts/locking?

bsides2

Hi all,

I have a performance issue with simple INSERTs. Say I try to perform 300 inserts in a for loop. It appears as though interbase is running/executing in 60 second intervals - it takes exactly 60 seconds before any anything happens, and at that time I get the results of the 1st 130 insert statements, all at once. Then precisely 60 seconds later I get the results of the next 150 insert statements. Then after about 10 seconds I get the results of the remaining few queries. All of this is wrapped in a transaction.

I am trying to insert the records from our web server (linux+apache 1.3.x, PHP 4.2.3) to an interbase db running on the client pc (win2k running IB6 as a service). No problems connecting to the database, selecting records is fine too, the problem I am having seems to be with INSERTs (haven't tried updates yet!). After each insert I print the results to the browser so I know whats happening, yet I only get output at 60 second intervals.

Here's what I am doing:
$cn = ibase_connect('11.22.33.44:C:/path/to/data/MYDB.GDB', 'USERNAME', 'pAsSwOrD');
if ($cn) {
$trans=ibase_trans($cn);
$q = ibase_prepare($cn, 'INSERT INTO mytable (f1, f2, f3, f4) VALUES (?, ?, ?, ?)');
for ($i=0; $i < 300; $i++) {
echo $i.'<br>';
ibase_execute($q, $i, $i, $i, $i);
}
ibase_commit($trans);
ibase_free_query($q);
ibase_close($cn);
echo "completed OK!<br>";
} else echo "unable to connect!<br>";

I have tried all sorts of variations on this. It doesn't matter whether I commit after each executed query or not, or use the inherent transactions of interbase (ie, don't start my own transaction and just use a commit at the end), or use ibase_query alone instead of the ibase_prepare/execute approach. Running this locally takes less than 1 second (local apache web service and local interbase db), compared to more than 3 minutes remotely (remote webserver to view web page, connecting to local interbase db). Even the output being printed to the browse withing the for loop only appears in 60 second intervals - and if I exclude the ibase_execute() everything runs in under 5 seconds.

Does anyone know what may be causing this? Is it a configuration setting? Is it a database locking issue? Any suggestions are greatly appreciated.

Thanks in advance,

-MK

Sxooter

Try setting ob_implicit_flush(true) and see if the output is line by line what's happening. It may just be buffering in the way.

bsides2

Hi Sxooter,

Thanks for that, ob_implicit_flush(), thats handy to know.

While it doesn't make the whole process any faster atleast I know what it's up to. I've got it down to 1min20sec for 200 simple inserts via tcp/ip from the U.S to Aust, that about 2 per second. Not too bad I guess but the live database has more than 3000 records! My calculations indicate it will take around 20 minutes, there has to be a better way, especially as this will be a regular process.

Cheers,

Sxooter

Well, many open source databases are notoriously slow at inserts. Postgresql can be quite a dog at them as well.

In postgresql it's important to make sure that all the inserts that can be grouped together in a transaction can be.

Are you sure that interbase isn't running in autocommit on mode and you think it's in autocommit off? i.e. have you tried doing it this way:

begin;
insert 1000 rows
commit;

So that you explicitly start and stop the transcation? My experience with Interbase has been limited (I've downloaded it and played around a bit, but it's so foreign to Postgresql and MySQL that I'm not sure I understand it all that well.

BeastRider

You might try this question also in the Borland newsgroups since they are the ones that had the original interbase, then put it out as open source. There are several interbase groups at

newsgroups.borland.com

bsides2

Thanks to both Sxooter and BeastRider,

I have posted this to a borland/interbase newsgroup - they say to try a php group :-)

As for Sxooter's suggestion to use autocommits OFF, I would assume that by using ibase_trans() to start a transaction and ibase_commit() to end it that all the inserts will not be autocommited and instead be pending my own commit.

My guess is the slow performance is mainly to do with issuing a series of individual commands via tcp/ip. Maybe it would be better to send one very large command/string comprising the complete transaction. Something for me to try later.

(I'll leave the debate about different DB products to another time, postgresql is my choice for opensource database)

Rgds,

-MK

Sxooter

What I was worried about was that the interface was autocommitting each line behind your back, the way Postgresql does with some jdbc connection classes (CMP to be exact.)

You might be able to turn on some kind of logging on the interbase site and kinda look over the shoulder of the database so to speak.