Exporting table data to CSV is taking forever

I’m generating CSV file from the table data. The table records are in millions. The problem is when i click the EXPORT To CSV button it takes more than 6 minutes to generate the CSV file and my server throws timeout error after 6 minute.

UPDATE: I know i can increase the timeout time but i need to optimize the below script and QUERY!

Function For Exporting The Users to CSV

 // Function for exporting all Quiz Users Leads  to CSV
    public function executeUserExportLeads($request) {
        $this->export = true;
        $this->pager = new myPropelPager('BtqUser', 9999999);
        $this->pager->setCriteria(btqAdminBtqUserPeer::getBtqUsersForExport());
        $this->pager->setPeerMethod('doSelectStmt');
        $this->pager->setAction('newadmin/users');
        $this->pager->setPage($request->getParameter('page', 1));
        $this->pager->init();

        //Generating CSV in server and giving user the CSV file for download
        if($this->pager->getResults() > 0){
            $filename = "userleads".".csv";
            unlink($filename);
            $uploaddir  = sfConfig::get('sf_upload_dir');
            $path = $uploaddir ."/" . $filename;
            fopen($path , 'a');
            $handle = fopen($path, 'w+');

            //set column headers
            $fields = array('Date', 'Name', 'Email', 'Lead From', 'State', 'Phone No',"rn");
            fwrite($handle, implode(',',$fields));

            //output each row of the data, format line as csv and write to file pointer
            foreach($this->pager->getResults() as $row){
                $lineData = array(date('M-d-Y', strtotime($row['schedule_date'])), $row['name'], $row['email'] , $row['lead_from'], $row['state'], $row['telefon'],"rn");
                fwrite($handle, implode(',',$lineData));
            }

            fclose($handle);

            $result_array = array('fileURL' => 'http://this-site.com/uploads/'.$filename);
            return $this->renderText(json_encode($result_array));
        }
        exit;
    }

Query Export ( this fetches all users record for exporting to csv ):

public static function getBtqUsersForExport() {
        $criteria = new Criteria();

        $criteria->clearSelectColumns();
        $criteria->addSelectColumn("btq_user.id as id");
        $criteria->addSelectColumn("btq_user.name as name");
        $criteria->addSelectColumn("btq_user.email as email");
        $criteria->addSelectColumn("btq_user.lead_from as lead_from");
        $criteria->addSelectColumn("btq_user.telefon as telefon");
        $criteria->addSelectColumn("btq_user.datain as datain");


        $criteria->addSelectColumn("state.state as state");

        $criteria->addSelectColumn("lead_schedule.id as schedule_id");
        $criteria->addSelectColumn("lead_schedule.created_at as schedule_date");

        $criteria->addJoin(self::STATE_ID, StatePeer::ID, Criteria::LEFT_JOIN);
        $criteria->addJoin(self::ID, LeadSchedulePeer::LEAD_ID, Criteria::LEFT_JOIN);
        $criteria->addJoin(self::ID, BtqUserTrackBlogVideoPeer::USER_ID, Criteria::LEFT_JOIN);

        $criteria->addGroupByColumn(self::EMAIL);
        $criteria->add(BtqUserPeer::IS_DUMMY_DETAIL, "1", Criteria::NOT_EQUAL);

        $criteria->addDescendingOrderByColumn(self::DATAIN);
        return $criteria;
    }

Ajax for the request:

<script>
    function move() {
        var hidden = document.getElementById("myProgress");
        hidden.classList.remove("hidden");
        var elem = document.getElementById("myBar");
        var width = 1;
        var id = setInterval(frame, 5000);
        function frame() {
            if (width >= 100) {
                clearInterval(id);
                var hidden = document.getElementById("myProgress");
                hidden.classList.add("hidden");
            } else {
                if(width>100){
                }else{
                    width++;
                    elem.style.width = width + '%';
                }
            }
        }
        $('#exportCSV').submit(function(event){
           event.preventDefault();
        });
        $.ajax({
            data: {export: "Export To CSV"},
            type: 'POST',
            url: 'userExportLeads',
            success: function(result){
                console.log(result);
                var data = JSON.parse(result);
                clearInterval(id);
                $('#myBar').css('width','100%');
                $('#myProgress').delay(5000).fadeOut();
                location.href = data.fileURL;
            }
        });
    }
</script>

And the below is form code:

 <form id="exportCSV" action="<?php echo url_for('newadmin/userExportLeads'); ?>" method="POST">
            <input type="submit" onclick="move()" name="export" value="Export To CSV" />
      </form>

    </div>

    <br/>
      <div id="myProgress" class="hidden" align="left">
          <div id="myBar"></div>
      </div>"

If there is anything else required i can share it.

Thanks

Source: Symfony1 Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *