Downloading Large Data Sets -> Text to MySQL or just to MySQL?

I'm downloading large sets of data via an XML Query through PHP with the following scenario: - Query for records 1-1000, 开发者_C百科download all parts (1000 parts has roughly 4.5 megs of text), then store those in memory while i query the next 1001 - 2000, store in mem (up to potentially 400k)

I'm wondering if it would be better to write these entries to a text field, rather than storing them in memory and once the complete download is done trying to insert them all up into the DB or to try and write them to the DB as they come in.

Any suggestions would be greatly appreciated.

Cheers

You can run a query like this:

INSERT INTO table (id, text)
VALUES (null, 'foo'), (null, 'bar'), ..., (null, 'value no 1000');

Doing this you'll do the thing in one shoot, and the parser will be called once. The best you can do, is running something like this with the MySQL's Benchmark function, running 1000 times a query that inserts 1000 records, or 1000000 of inserts of one record.

(Sorry about the prev. answer, I've misunderstood the question).

I think write them to database as soon as you receive them. This will save memory and u don't have to execute a 400 times slower query at the end. You will need mechanism to deal with any problems that may occur in this process like a disconnection after 399K results.

In my experience it would be better to download everything in a temporary area and then, when you are sure that everything went well, to move the data (or the files) in place.

As you are using a database you may want to dump everything into a table, something like this code:

$error=false;
while ( ($row = getNextRow($db)) && !error ) {
    $sql = "insert into temptable(key, value) values ($row[0], $row[1])";
    if (mysql_query ($sql) ) {
         echo '#';
    } else {
         $error=true;
    }
}

if (!error) {
    $sql = "insert into myTable (select * from temptable)";
    if (mysql_query($sql) {
        echo 'Finished';
    } else {
        echo 'Error';
    }
}

Alternatively, if you know the table well, you can add a "new" flag field for newly inserted lines and update everything when you are finished.