Multiple inserts with interbase and PHP

[Anon]

Hi,

I need to do many inserts (3-4000)
in a ibase db with php.

I have some performance problems, after
the first 1000 queries, performances begin to
slow down and it took approx 1 hour
to complete the process.

How can I speed it up.

It's a while cycle that open a connection
(persistent) read row from a file and
then insert it in the db.

Awaiting suggestions...

[[deleted]]

I'm not familiar with interbase,
but there are a few things that speed inserts up in general:

use transactions if possible.
lock the table you are inserting to so only you can read-write from/to it.

I pretty nasty hack could be to remove any indexes on the table and re-create them when the update is complete.

MySQL (and a few other DBs) have a feature that allows you to upload data from a textfile directly into the database, that is about as fast as it gets.

[Anon]

I already use transactions,
but the bottleck seems to be php, not IBase.

[smarlowe]

Are you using persistant connections and maybe running out of memory, or maybe building a big structure in memory that grows each time you insert a row, and maybe losing memory that way?

[[deleted]]

How does using pconnect matter? Wouldn't doing 1000 updates to the same table in one script all run over the same connection?

Franco, did you run "top" on both machines to see which is causing the slowdown?

[Anon]

I don't build any structure.

Maybe It runs out of memory, I tried
reserving more memory for scripts in php.ini
file, but nothing changed.

I also tried committing after every insert..no way...

[Anon]

Yes,
It seems to be the httpd daemon

but i can look further...

[Anon]

Try to make a plain-text file with the data
and then acquire it into a external-file table as follows...

• The file must contain the data in plain fixed-width format like ...

mydata.txt:

123PRODUCT ONE 20000
4321PRODUCT TWO 8990
3321PRODUCT THREE 2991
...

• Create a temporal table into your database
  to import the data file..

create table import_table
external file "mydata.txt" (
key char(5),
description char(29),
amount char(8),
newline char(1));

/ the "newline" field must be char(2) for windows text files /

• At this point, you can access to each line
  in the data file as a record in the "import_table", in example:

  SELECT KEY, DESCRIPTION, AMOUNT
  FROM IMPORT_TABLE;

..And so, you can insert into another table..

INSERT INTO MY_TABLE (<My_Field_List>)
SELECT <My_Field_List> FROM IMPORT_TABLE;

I have inserted up to 100.000 records
with reasonable speed.

Read the Interbase documentation about EXTERNAL-FILE tables.

Sorry, if my English is not the best..

[Anon]

Thanx,
I'm gonna try it as soon as possible.

[Anon]

Hi Franco,
I've come accross similar problem. Have
you created index for the table you're
inserting in? If you only insert into table,
then index should not help, but if you also select before inserting new item, then as more items are being selected from, the performance slows down without index.

Maybe I have come too late or completely
missed the point, so in that case sorry
for bothering you.

Dan

[Anon]

If you have index for the table, you can also deactivate
it before inserting records..

alter index <my_ix_name> inactive;

So, the index will be inactive during the transaction and will not make the performance slows down.
Finally, you can reactivate index again...

alter index <my_ix_name> active;

This will reactivate and regenerate the index.

Luiggi