Welcome Guest, Not a member yet? Register   Sign In
Insert/Save performance on 1,000 records/minute
#1

[eluser]onecurlyfry[/eluser]
I'm writing about 1,000 records per minute to a db (yes, that's 1.44 million records/day). I'm iterating through an XML document. For each entry, parse out the data I need, then write it to the DB.

I also write to a summary and status table... eg...

$this->records_model->save($rData);
$this->jobStatus_model->save($jData);
$this->summaryCount_model->save($sData);

This allows me to save the record, let my status table know my job is still running, and my summary table to aggregate my counts by date.

Since I'm making about 3000 inserts/minute, what I want to know is if there's a more performant way to do this. Would I be better off with $rData[0] = array(fill with data), $rData[1] = array(fill with data), etc & then running one gigantic save to my records table? What's the memory implications of this? Is there a better way?

[edit to add] - When I say 'one gigantic save' - I mean rather than calling each model 1000 times, I call it once, then iterate through the array & save from within the model [/edit]
Thanks,

Scott
#2

[eluser]tonanbarbarian[/eluser]
i would assume that currently your biggest overhead is processing the xml
there are a couple of questions i would ask yourself before deciding to change the process you have

1. is it working acceptably? if so dont change it unless you really must
2. is your database on the same server as the code or is it remote?
if it is remote then looking to reduce the number of queries you need to run would improve performance
3. is there any common data, i.e. is there data in jobStatus that has to come from records, such as an id?
if so then leave the process as it is
4. are there any queries being run in the save methods apart from inserts?
for example do you lookup data before inserting for whatever reason?
if so then you might want to look at ways to cache the lookup data in memory if possible

since you are processing 1000 records per minute, and I assume this limit is based on the speed of the code, storing the data to be saved in arrays is going to use up memory very quickly. As I alluded to earlier the xml processing may use considerable memory to begin with, if you try to put your data into arrays and then insert in batches you may end up running out of memory




Theme © iAndrew 2016 - Forum software by © MyBB