Ok guys... thanks for your patience with me... Don't know if you guys had a chance to check out the code i pasted but if you didn't i am already doing most of the things stated in the replays... and i really think that this is the a php issue... not symfony/propel issue..
Having said that... i have started working on a solution where the file upload is done in a separate action... which then forwards the user to another action that processes the data in the uploaded file x amount of records at a time... refreshing the page and passing the pager's current status as a request parameter, so processing a file with 10000 1000 records at time would mean 10 page refreshes... Anyway if anyone gets a chance let me know what you think about this... Regards Richard Btw... to anyone who i offended earlier... my deepest apologies... sometimes frustration gets the better of me... On Mon, Apr 12, 2010 at 10:01 PM, Tony Piper <tpi...@tpiper.com> wrote: > Just to chip in with another possible cause: > > I see you're doing all of this in a controller (and others have talked > about whether that's a good idea). Have you got the web debug toolbar > enabled? If so, it could be keeping a log of all DB queries executed > in your action. I had something similar with a batch job in 1.3/1.4. > > There 's a trac ticket which gives some workarounds: > http://trac.symfony-project.org/ticket/3039 > > Hope this helps. > > cheers, > > Tony. > > On Apr 12, 8:53 am, Richard U <richard....@gmail.com> wrote: >> Dennis thats the function if you want to have a look at it... >> >> public function executeIndex(sfWebRequest $request) { >> >> ini_set('memory_limit','512M'); >> set_time_limit(60*5); >> sfConfig::set('sf_logging_enabled', false); >> sfConfig::set('sf_web_debug', false); >> >> $timer = new Timer; >> $timer->start_time(); >> >> $this->submited_bulk_stats = >> $request->getParameterHolder()->get('bulk_stats'); >> >> $this->form = new BulkAddStatsForm(); >> >> $this->count_updated_records = 0; >> $this->count_inserted_records = 0; >> >> if ($request->isMethod('post')) >> { >> >> $this->form->bind($request->getParameter('bulk_stats'), >> $request->getFiles('bulk_stats')); >> if ($this->form->isValid()) >> { >> $values = $this->form->getValues(); >> >> $file = $this->form->getValue('file'); >> $file_name = >> str_replace($file->getOriginalExtension(), '', >> $file->getOriginalName()); >> $save_to = >> sfConfig::get('sf_upload_dir').DIRECTORY_SEPARATOR.'uploaded_'.$file_name.' >> _'.date("dmYHis").$file->getOriginalExtension(); >> $file->save($save_to); >> >> echo "<br>File saved: $save_to"; >> echo "<br>File size: ".filesize($save_to); >> >> // get accounts >> $accounts = AccountsPeer::doSelect(new >> Criteria()); >> $re_indexed_accounts = >> $re_indexed_accounts_by_id = array(); >> foreach ($accounts as $tmp_account) { >> >> $re_indexed_accounts[$tmp_account->getAffName().$tmp_account->getAffUser()] >> = $tmp_account; >> >> $re_indexed_accounts_by_id[$tmp_account->getId()] = $tmp_account; >> } >> >> // get parking programs >> $parking_programs = >> ParkingProgramPeer::doSelect(new Criteria()); >> $re_indexed_parking_programs = array(); >> foreach ($parking_programs as $pp) { >> >> $re_indexed_parking_programs[$pp->getId()] = $pp; >> } >> >> // Process the uploaded file >> $objReader = new PHPExcel_Reader_Excel2007; >> $objPHPExcel = $objReader->load($save_to); >> >> // Define allowed columns >> $allowed_columns = >> array('domain','views','clicks','revenue','currency','date','park','account >> '); >> $re_indexed_data = array(); >> $data = >> $objPHPExcel->getActiveSheet()->toArray(); >> >> // Get db connections since we are using two >> different db's here >> $connection_cdb = Propel::getConnection(); >> $connection_stats = >> Propel::getConnection('cdb_stats'); >> >> // get domains >> $domains_re_indexed = array(); >> $query = 'SELECT * FROM domains'; >> $statement = $connection_cdb->prepare($query); >> $statement->execute(); >> while ($dObj = $statement->fetch(PDO::FETCH_OBJ)) >> { >> $domains_re_indexed[$dObj->dn] = >> array('domain_id'=>$dObj->domain_ID, 'deal_id'=>$dObj->deal_id); >> } >> >> try { >> $validated_data = array(); >> $sfLogger = >> sfContext::getInstance()->getLogger(); >> >> foreach($data as $row_number => >> $row) { >> $batch_options = array(); >> >> // Check its not an empty row >> if (!strlen(implode("", >> $row))) { >> continue; >> } >> >> foreach($row as $col_number >> => $col) { >> echo "<br>MEMORY: >> ".memory_get_usage(); >> if ($row_number == >> 1) { >> $col = >> strtolower($col); >> $key = >> array_search($col, $allowed_columns); >> if ($key !== >> false) { >> >> $data_indexes[$col_number] = $allowed_columns[$key]; >> } >> } else { >> if >> (isset($data_indexes[$col_number]) && >> strlen($data_indexes[$col_number]) && strlen($col)) { >> if >> ($data_indexes[$col_number] == "date") { >> >> $col = date("Y-m-d", strtotime(str_replace("/", "-", $col))); >> } >> >> $batch_options[$data_indexes[$col_number]] = $col; >> } >> } >> } >> >> if ($row_number > 1) { >> >> // Park info now >> comes from the form >> if >> (!isset($values['parking_program']) || >> !strlen($values['parking_program'])) { >> throw new >> Exception('Missing or invalid parking program'); >> } else { >> >> $batch_options['aff_name'] = >> strtolower($re_indexed_parking_programs[$values['parking_program']]->getNam >> e()); >> } >> >> // Account info now >> comes from the form >> if >> (!isset($values['accounts']) || !strlen($values['accounts'])) { >> throw new >> Exception('Missing or invalid parking account'); >> } else { >> >> $batch_options['aff_user'] = >> strtolower($re_indexed_accounts_by_id[$values['accounts']]->getAffUser()); >> } >> >> // Specify for batch >> results to be returned as a serialized array >> $batch_results = >> array(); >> >> $batch_options['return_results'] = true; >> >> $batch_options['row'] = $row_number; >> >> $batch_options['skip_merge'] = 1; >> >> $output = array(); >> >> foreach($batch_options as $k=>$v) { >> $output[] = >> "$k:$v"; >> } >> echo >> "<br>".implode(", ", $output); >> flush(); >> >> // Validate and set >> data >> $c = new Criteria(); >> >> >> $c->add(DailyStatsTempPeer::DN, $batch_options['domain']); >> >> $c->add(DailyStatsTempPeer::AFF_NAME, $batch_options['aff_name']); >> >> $c->add(DailyStatsTempPeer::AFF_USER, $batch_options['aff_user']); >> >> $c->add(DailyStatsTempPeer::VIEWS, $batch_options['views']); >> >> $c->add(DailyStatsTempPeer::CLICKS, $batch_options['clicks']); >> >> $c->add(DailyStatsTempPeer::REVENUE, $batch_options['revenue']); >> >> $c->add(DailyStatsTempPeer::STAT_DATE, $batch_options['date']); >> >> $c->add(DailyStatsTempPeer::STAT_CURRENCY, $batch_options['currency']); >> >> // Set other data >> that needs to be added with the stats >> // ~ Set click rate >> >> $c->add(DailyStatsTempPeer::CLICK_RATE, >> ($c->getValue(DailyStatsTempPeer::VIEWS) ? >> $c->getValue(DailyStatsTempPeer::CLICKS)/$c->getValue(DailyStatsTempPeer::V >> IEWS) >> : 0)); >> // ~ Set revenue per >> click >> >> $c->add(DailyStatsTempPeer::REVENUE_PER_CLICK, >> ($c->getValue(DailyStatsTempPeer::CLICKS) ? >> $c->getValue(DailyStatsTempPeer::REVENUE)/$c->getValue(DailyStatsTempPeer:: >> CLICKS) >> : 0)); >> // ~ Set data from >> accounts table >> >> if >> (isset($re_indexed_accounts[$c->getValue(DailyStatsTempPeer::AFF_NAME).$c-> >> getValue(DailyStatsTempPeer::AFF_USER)])) >> { >> $account = >> $re_indexed_accounts[$c->getValue(DailyStatsTempPeer::AFF_NAME).$c->getValu >> e(DailyStatsTempPeer::AFF_USER)]; >> >> $c->add(DailyStatsTempPeer::BONUS, $account->getBonus()); >> >> $c->add(DailyStatsTempPeer::REV_SPLIT, $account->getRevSplit()); >> >> $c->add(DailyStatsTempPeer::COMPANY, $account->getCompany()); >> >> $c->add(DailyStatsTempPeer::ACCOUNT_ID, $account->getId()); >> } else { >> >> $sfLogger->err("{".basename(__file__)."} Account not found >> for Park: {$c->getValue(DailyStatsTempPeer::AFF_NAME)} >> >> Account:{$c->getValue(DailyStatsTempPeer::AFF_USER)} on row >> $row_number"); >> throw new >> Exception('Account not found for >> Park:'.$c->getValue(DailyStatsTempPeer::AFF_NAME).', >> >> Account:'.$c->getValue(DailyStatsTempPeer::AFF_USER).' on row >> '.$row_number); >> } >> // ~ Set additional >> colums data recently added >> if >> (isset($domains_re_indexed[$batch_options['domain']])) { >> $domain = >> $domains_re_indexed[$batch_options['domain']]; >> >> $c->add(DailyStatsTempPeer::DOMAIN_ID, $domain['domain_id']); >> >> $c->add(DailyStatsTempPeer::DEAL_ID, $domain['deal_id']); >> } >> >> if >> ($batch_options['date'] == '1970-01-01' || >> $batch_options['date'] == '1969-12-31') { >> >> $sfLogger->err("{".basename(__file__)."} Invalid date format: >> {$batch_options['date']} on row $row_number"); >> throw new >> Exception('Please change date format in excel file >> to dd/mm/yyyy'); >> } >> >> >> $checkIfRecordExistsCriteria = new Criteria(); >> >> $checkIfRecordExistsCriteria->add($c->getCriterion(DailyStatsTempPeer::STAT >> _DATE)); >> >> $checkIfRecordExistsCriteria->add($c->getCriterion(DailyStatsTempPeer::DN)) ; >> >> $checkIfRecordExistsCriteria->add($c->getCriterion(DailyStatsTempPeer::AFF_ >> NAME)); >> >> $checkIfRecordExistsCriteria->add($c->getCriterion(DailyStatsTempPeer::AFF_ >> USER)); >> >> // Set the daily >> stat entree information to be used for logging >> >> $daily_stat_record_info = >> DailyStatsTempPeer::DN.":".$c->getValue(DailyStatsTempPeer::DN).", ". >> >> DailyStatsTempPeer::AFF_NAME.":".$c->getValue(DailyStatsTempPeer::AFF_NAME) >> .", >> ". >> >> DailyStatsTempPeer::AFF_USER.":".$c->getValue(DailyStatsTempPeer::AFF_USER) >> .", >> ". >> >> DailyStatsTempPeer::STAT_DATE.":".$c->getValue(DailyStatsTempPeer::STAT_DAT >> E)." >> Row: $row_number"; >> >> $affected_rows = >> DailyStatsTempPeer::doDelete($checkIfRecordExistsCriteria, >> $connection_stats); >> >> DailyStatsTempPeer::doInsert($c, $connection_stats); >> >> if ($affected_rows) { >> >> $this->count_updated_records++; >> } else { >> >> $this->count_inserted_records++; >> } >> >> >> DealsPeer::clearInstancePool(); >> >> DailyStatsTempPeer::clearInstancePool(); >> >> AccountsPeer::clearInstancePool(); >> >> DomainsPeer::clearInstancePool(); >> >> ParkingProgramPeer::clearInstancePool(); >> } >> } >> >> sfBatchHelper::executeBatch('mergeDailyStatsTempToDailyStats', array()); >> >> } catch (Exception $e) { >> $this->getUser()->setFlash('error', >> var_export($e, true), false); >> } >> } >> } >> // Remove the uploaded file >> if (isset($save_to) && strlen($save_to) && >> file_exists($save_to)) { >> unlink($save_to); >> } >> $this->submited_bulk_stats = $submited_bulk_stats; >> } >> >> Works fine with a couple of thousand rows... but its clear to see the >> memory going up >> Don't know what this will tell you that i haven't already >> >> On Mon, Apr 12, 2010 at 5:28 PM, Dennis Benkert >> >> >> >> <spinecras...@googlemail.com> wrote: >> > So, how big are these CSV files? How many lines are you processing and >> > can you tell us how many entries you're inserting to your DB using >> > Propel? >> >> > It would be best if you could show us the code that implements what >> > you described in your last mail. Investing maybe 30 minutes to write >> > an email that desribes the problem you have, what part of your code >> > suffers from it and the data you're using will most likely keep you >> >> ... >> >> read more » > > -- > If you want to report a vulnerability issue on symfony, please send it to > security at symfony-project.com > > You received this message because you are subscribed to the Google > Groups "symfony users" group. > To post to this group, send email to symfony-users@googlegroups.com > To unsubscribe from this group, send email to > symfony-users+unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/symfony-users?hl=en > > To unsubscribe, reply using "remove me" as the subject. > -- If you want to report a vulnerability issue on symfony, please send it to security at symfony-project.com You received this message because you are subscribed to the Google Groups "symfony users" group. To post to this group, send email to symfony-users@googlegroups.com To unsubscribe from this group, send email to symfony-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/symfony-users?hl=en
