I'm using PHP (Yii framework) and Postgresql for the db. Basically a wappstack. I just mentioned them if ever they are a concern for this problem.
Anyway, what i'm trying to achieve is I have a submit action:
public function actionSubmitData($id){
//Execute Sql queries here
$this->render('view',array(
//some data
));
}
The problem is that I will be processing a large amount of data; About 50,000 records will be processed in each of 4 tables of roughly 10 columns each table, that is fairly large. The use case is that the user submits, then the sql statements are executed in background, but the view 'view' should already be rendered, not requiring the user to wait for the records to be processed. He/she can check on it later on after the records are processed.
A normal request would execute sql statements first, then render the view. The problem is when the page takes about 3-5 min. The page never loads; and the creation of records is not finished, just a white page appears when the requests are intensive.
In other words, how do I execute sql queries in background? Leaving the user able to browse the application still while the request is being processed? And somewhere in the background, records are being processed? What is the workaround for this problem? Thanks!
I'd suggest push your queries (jobs) to some messaging system as RabbitMQ or Gearman. It will let do your work in parallel. You even be able scale your jobs easily.