> > So I can only expect 68 transaction per second or roughly
> > 250,000 transactions per hour (I agree, row count is beside
> > the point)? I will run a recalibrated test to see how far
> > this gets me.
>
> It appears a second opinion is in order.
> I decided to perform a similar test to you: 4 million inserts, and timing
> them.
> Hardware:
> P2 350MHz, 128MB ram, IDE disks, Firebird 1.0.2
>
> Using the advice and feedback from your original email:
> Don't commit on every insert, but commit every 10k or so
> Do prepare the query in advance, otherwise Firebird will prepare it EVERY
> SINGLE QUERY
>
> SO I did the following:
>
> --- Table Schema ---
> create table tbl_test (
> int_id NUMERIC(18,0) NOT NULL,
> PRIMARY KEY(int_id)
> );
>
> So it's got an index to update(pkey), and a unique constraint to
> check(pkey)
>
>
>
> --- PHP file --- (I don't have the money for commercial software) ;P
> #!/usr/local/bin/php -e
> <?php
> $start = microtime();
>
> // Connect to the DB
> $conn = ibase_connect("twister:/u1/db/test.gdb","sysdba","masterkey");
>
> // Prepare a statement
> // (Cause firebird will do it for us for EVERY query if we don't)
> $prep = ibase_prepare("INSERT INTO tbl_test VALUES (?)");
>
> // Do the loop
> echo "Inserting 4M records\n";
> for($a = 0; $a < 4000000;$a++){
> //Run the query
> $res = ibase_execute($prep,$a);
> if($a%10000 == 0 && $a > 0){
> // WOrk out how fast were going
> $smicro = substr($start,2,8);
> $ssec = substr($start,11);
> $begin = "$ssec.$smicro";
>
> $finish = microtime();
> $fmicro = substr($finish,2,8);
> $fsec = substr($finish,11);
> $end = "$fsec.$fmicro";
> $perf = $end - $begin;
> echo "$a records. Recs/Sec:".number_format($a/$perf,2)."\n";
> ibase_commit_ret();
> }
> }
>
> ?>
>
>
>
> --- Results ---
> Inserting 4M records
> 10000 records. Recs/Sec:1,369.73
> 20000 records. Recs/Sec:1,387.93
> 30000 records. Recs/Sec:1,384.66
> 40000 records. Recs/Sec:1,378.88
> 50000 records. Recs/Sec:1,366.23
> 60000 records. Recs/Sec:1,356.47
> 70000 records. Recs/Sec:1,357.22
> 80000 records. Recs/Sec:1,358.60
> 90000 records. Recs/Sec:1,355.69
> 100000 records. Recs/Sec:1,353.22
> 110000 records. Recs/Sec:1,355.22
> 120000 records. Recs/Sec:1,357.30
> ...
> 360000 records. Recs/Sec:1,341.41
> 370000 records. Recs/Sec:1,341.66
> 380000 records. Recs/Sec:1,339.19
> 390000 records. Recs/Sec:1,339.83
> 400000 records. Recs/Sec:1,340.72
> 410000 records. Recs/Sec:1,341.28
> 420000 records. Recs/Sec:1,340.68
> 430000 records. Recs/Sec:1,340.47
> 440000 records. Recs/Sec:1,340.84
> 450000 records. Recs/Sec:1,341.69
> 460000 records. Recs/Sec:1,339.42
> 470000 records. Recs/Sec:1,339.61
> 480000 records. Recs/Sec:1,339.58
> ...
> 1110000 records. Recs/Sec:1,347.70
> 1120000 records. Recs/Sec:1,347.78
> 1130000 records. Recs/Sec:1,347.80
> 1140000 records. Recs/Sec:1,347.72
> 1150000 records. Recs/Sec:1,347.65
> 1160000 records. Recs/Sec:1,347.57
> 1170000 records. Recs/Sec:1,348.00
> 1180000 records. Recs/Sec:1,348.16
> 1190000 records. Recs/Sec:1,347.96
> 1200000 records. Recs/Sec:1,348.19
> ...you get the idea.
>
>
> As you can see, it's quick, and it doesn't slow down.
>
> Need a method to see if the table 'tbl_test' exists:
> select first 1 1 from tbl_table;
>
> Result: 1
>
> Hope this helps
>
> Nigel.
>

Have you tried ibase_commit and not _ret? which is retaining the transaction
context? I would imagine that it's even faster.
I assume this script is running in the console?

Alan