Need help getting a script for huge inserts

[Freebird]

Hello everyone ...

I've a problem in here,
I need to make a 70 000 rows insert, and it's taking a lot more than 30
seconds, my problem is that it will be a script that will work in many
servers, I've no idea of how many, the set_time_limit does not work for
many of the servers out there, so I'm in trouble, I've tryed to do a refresh
every 100 inserts, but it doesn't work at all, does anyone have a class,
function or piece of code that will do that ?

Or help me doing one ? Remembering that set_time_limit will not work for me.

If anybody has something that can help me, send to peoplel@hotmail please,

tks a lot

Freebird

[Sjoerd]

Which DB server are you using? Maybe it is possible to speed up the
inserts by using transactions, postponing index updating or something
like that.

[Sean]

If you are using mysql instead of using INSERTS you should save the
data to a file and then use the LOAD DATA statement it will speed
things up considerably. LOAD DATA is about 20 times faster than INSERT.

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

However since you have mentioned very little about the problem I dont
know if this will help.

[Freebird]

> If you are using mysql instead of using INSERTS you should save the

data to a file and then use the LOAD DATA statement it will speed
things up considerably. LOAD DATA is about 20 times faster than INSERT.

Wont work as well, as I said, I need a script that will do that, because
sometimes, can be a 1 000 000 rows insert.
As I said, header location did not work. I did a script, but doesn't work.

Is this something impossible or what ?
I've never seen a script that does such a task, never.
I'm using php+mysql !
Can't use LOAD DATA because it takes more than 30 seconds, header location
doesn't work, can't use set_time_limit, what do I do ?

Tks a lot

Freebird

[Sjoerd]

Why does header location does not work? I tried the following script,
and it just writes numbers into test.txt. The only problem is that
Firefox stops after 20 redirects.

<?php
header('Locatio n: test.php?item=' .($_GET['item']+1));

sleep(1);
$fp = fopen('test.txt ', 'a');
fwrite($fp, $_GET['item']."\n");
fclose($fp);
?>

[Freebird]

tks, what about IE ?

how many times will I be able to use refresh ?

Tks

Freebird

[john_ramsden]

Freebird wrote:

Hello everyone ...

I've a problem in here,
I need to make a 70 000 rows insert, and it's taking a lot more than 30
seconds, my problem is that it will be a script that will work in many
servers, I've no idea of how many, the set_time_limit does not work for
many of the servers out there, so I'm in trouble, I've tryed to do a refresh
every 100 inserts, but it doesn't work at all, does anyone have a class,
function or piece of code that will do that ?

Or help me doing one ? Remembering that set_time_limit will not work for me.

If anybody has something that can help me, send to peoplel@hotmail please,

It's selfish to ask people to email just you a solution, because then
others don't get to see it.

Anyway, preaching aside, the following can be run via HTTP and respawns
itself to do a long operation in its own time. It works on either Unix
or Windows.

The only snag with this "asynchrono us" approach is that the user gets
no direct feedback when the load completes or aborts, unless the code
sends them an email or something.

(Note that you'll probably need to elaborate this code, e.g. to add
more arguments specific to your requirements.)
function insert_rows ()
{
:: blah blah ::
}
# main code at end ..

$arg_rerun = 'rerun'; # Rerun argument value

$args = array ();

if (array_key_exis ts ('argv', $_SERVER))
{
for ($i = count ($_SERVER['argv']); 0 < --$i; )
{
$arg = $_SERVER['argv'][$i];

$args[$arg] = 1;
}
}

if (! array_key_exist s ($arg_rerun, $args))
{
$file = __FILE__; # Our absolute path

if (PHP_OS == 'WINNT') # Windows
{
proc_close (proc_open ("start /b php $file $arg_rerun",
array(), $foo));
}
else # Unix (e.g. PHP_OS ==
'Linux')
{
proc_close (proc_open ("php $file $arg_rerun &", array(),
$foo));
}
}
else
{
insert_rows ();
}

?>
Cheers

John R Ramsden (jh******@yahoo .com.uk)

* Remove m from com ro reply
* From address is defunct

[Freebird]

Tks a lot, you're right, in here is better, but there's a problem, I can
open the file, grab it all, and have all inside a array like this:

$info[0][0] = 'row 1';
$info[1][0] = 'row 2';
$info[2][0] = 'row 3';

But I've done a lot of tests, and can't get that running, how would I place
my array inside this code ?

Tks a lot for your help

Freebird

function insert_rows ()
{
:: blah blah ::
}
# main code at end ..

$arg_rerun = 'rerun'; # Rerun argument value

$args = array ();

if (array_key_exis ts ('argv', $_SERVER))
{
for ($i = count ($_SERVER['argv']); 0 < --$i; )
{
$arg = $_SERVER['argv'][$i];

$args[$arg] = 1;
}
}

if (! array_key_exist s ($arg_rerun, $args))
{
$file = __FILE__; # Our absolute path

if (PHP_OS == 'WINNT') # Windows
{
proc_close (proc_open ("start /b php $file $arg_rerun",
array(), $foo));
}
else # Unix (e.g. PHP_OS ==
'Linux')
{
proc_close (proc_open ("php $file $arg_rerun &", array(),
$foo));
}
}
else
{
insert_rows ();
}

?>

[john_ramsden]

Freebird wrote:

Tks a lot, you're right, in here is better, but there's a problem, I can
open the file, grab it all, and have all inside a array like this:

$info[0][0] = 'row 1';
$info[1][0] = 'row 2';
$info[2][0] = 'row 3';

But I've done a lot of tests, and can't get that running, how would I place
my array inside this code ?

Assuming you mean you're uploading data from a client, I'd just write
that to a temp file with a unique name, with minimal processing, and
then rerun yourself supplying that file name as an argument so the
rerun can open the file and do the full processing.

(But note that on Unix I think temp files are deleted as soon as they
are opened, so that they are visible only to the process that created
them and they vanish when closed! So you may need to construct
a unique name by hand, e.g. epoch time and PID concatenated,
and open the temp file explicitly via fopen().)

The general idea is to do the minimum amount of work in the HTTP
stage to get yourself set up, and then do the time-consuming donkey
work in the asynchronous rerun.

[pwiegers]

How are you gonna name it - KY.php? :-)