Copying a large dataset

I’m developing a dashboard using Yii2. The data is held in a remote SQL Server database and I’m copying it in to a local MySQL database. The data is constantly updated with the addition of new rows. I need to update the local version at least once a day. There are currently about 1.5 million lines in the relevant table though each row doesn’t contain much data.

In the short term I have no control over the structure of the remote table. It has no primary key set and the rows aren’t time-stamped either so (I think) I have no way of knowing at the time of each update which rows are new and which ones already existed.

My approach is every 24 hours to take a complete copy of the remote table. The problem is that PHP runs out of memory when I do this. I found the Yii2 yii\db\Query->batch() function (http://www.yiiframework.com/doc-2.0/yii-db-query.html#batch()-detail) which looked like it should do the job but I’m not sure how to use it and when I’ve tried so far, it runs out of memory as well.

What I’ve got so far looks like this -


foreach ($query->batch(1000) as $rows) {

    do some stuff

}

So I suppose my question is -

Is the batch() function the right thing to use for this and how should I be using it if the above doesn’t work?

Do it without PHP.

Hi,

Like samdark already said…

I also would not use PHP for such tasks and would ersonally avoid a "custom solution" as much as possble… Most databases today have already existing tools & applications to handle such things for you.

Best Regards

Thanks both. I was wondering if that might be the better way forward.