Page 1 of 1

large database transfer

Posted: Wed Jan 30, 2008 10:06 am
by yacahuma
I was trying to move data between a database (flat file based) using odbc and oracle.

The program works if I just read a hundreds of rows. But if I do a select * from table on the source database,
php just tries to read everything, since there are millions of rows it dies with the bytes exhausted error.


I thought this will be just like reading a file, that the driver will take care of reading a row at a time. I was wrong.

I know there are products out there that does this but now I wonder if I am doing something wrong or is it the drivers fault or PHP fault. There has to be a way to read large amounts of data (select * from table) without dying.

Re: large database transfer

Posted: Wed Jan 30, 2008 10:22 am
by rsmarsha
Not sure with oracle, but I use Navicat for mysql database work. It has a data transfer feature with works really well.

If your using php to do it, could you not work the information your fetching in some kind of loop, with a limit clause on the sql query? Each iteration of the loop could change the limit query to pull out the next set of rows for transfer. Failing that maybe a command line dump and restore may work, as i've said i'm talking from experience of mysql not oracle, but it might point you in the right direction. :)

Re: large database transfer

Posted: Wed Jan 30, 2008 11:05 am
by RobertGonzalez
Navivat is a MySQL interface.

Is your data on a server that you manage?

Re: large database transfer

Posted: Wed Jan 30, 2008 1:51 pm
by BDKR
yacahuma wrote:I was trying to move data between a database (flat file based) using odbc and oracle.

The program works if I just read a hundreds of rows. But if I do a select * from table on the source database,
php just tries to read everything, since there are millions of rows it dies with the bytes exhausted error.


I thought this will be just like reading a file, that the driver will take care of reading a row at a time. I was wrong.

I know there are products out there that does this but now I wonder if I am doing something wrong or is it the drivers fault or PHP fault. There has to be a way to read large amounts of data (select * from table) without dying.
How large is large?

And remember that PHP should have a configured time out. Perhaps I should say that it's not going to just run and run. Unless something has changed in PHP5, there is a time limit after which a script will die. Have you checked out set_time_limit().

There is als the question of your logic. How are returning the rows after executing the query. And please don't just dump out the entire script. :banghead:

Re: large database transfer

Posted: Wed Jan 30, 2008 2:25 pm
by yacahuma
i am not using mysql for this one

i set the time to be unlimited and memory to 800MB

the problem is that it seems that PHP just tries to read everything at once, at least using php adodb, easting up the 800MB and then dies. My coworker is using .net and he says that his api provides the ability to read the records one at a time.

Re: large database transfer

Posted: Wed Jan 30, 2008 2:44 pm
by RobertGonzalez
PHP makes a query call. The query resource comes back in full. If you are pulling a lot of data (a lot being whatever you deem to be a lot) then this could have an effect on your query call.

Personally I wouldn't use PHP to hit the database and try to get all of the data out of it. Can't you use a client tool for the database to dump the data in some form of structured format so that it can be easily read by another SQL engine? Or worse case, perhaps you could dump the data as plain text (CSV or something) then import it into whatever other database you are using?

Re: large database transfer

Posted: Wed Jan 30, 2008 3:48 pm
by Kieran Huggins
Those are fine suggestions, and worth trying first.

If they fail you could try doing the import in blocks, but that could be a major pain in the ass.

Re: large database transfer

Posted: Thu Jan 31, 2008 4:24 am
by yacahuma
I will just use an external tool like winsql.


It just bothers me that I was not able to do it with PHP. I will think it is somehow the drivers faults

if I do(the code might not be correct, it it just for the idea)

Code: Select all

 
$rs = $db->Execute($sql);
while (!$rs->EOF)
{
  $rs->MoveNext();
}
 
I would expect things to be read one at a time. If I had the time it will be something interesting to look into.

Re: large database transfer

Posted: Thu Jan 31, 2008 10:52 am
by RobertGonzalez
Let's explain this a little bit....

Code: Select all

<?php
// Read the result of the $sql instruction into a recordset object
$rs = $db->Execute($sql);
// At this point the entire data set is now wrapped up in the resultset object
// One trip to the database, one result back
// Yes, the result is huge, but that is the nature of the fetching data from a query
// Even in a client app you are more than likely going to fetch the data then 
// loop through the result...
 
// Loop through the result until we get to the end of it
while (!$rs->EOF)
{
  // Move to the next record
  $rs->MoveNext();
}
?>
As you can see, the web app hits the databases with the query, the database responds with the result. All at once.

The app then needs to move through the result.

I ran into something like this once on a larger than normal dataset coming from a Sybase call. What I ended up having to do was set my memory limit from the default of 8MB to 64MB and set my max execution time to one minute. But I did it only on that one page, not application wide.

Re: large database transfer

Posted: Thu Jan 31, 2008 11:15 am
by JAM
Regarding Kierans statement (blocks = pain in the ass) is most usually correct, but that also depends on what and how the data looks and is presented, where it originates from aso, aso.

Do you have to use ODBC? It's not the most reliable solution to transfer data imho. Neither is the speeds.

Re: large database transfer

Posted: Thu Jan 31, 2008 4:55 pm
by BDKR
yacahuma wrote:My coworker is using .net and he says that his api provides the ability to read the records one at a time.
Uh.... So does PHP.

That's why I said this was a question of logic.