Exporting table data to CSV is taking forever

Question

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',"\r\n");
            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'],"\r\n");
                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

Answer 1

I agree with the recommendation in the comments that switching to raw SQL is probably the best bet.

Even if you don't do that, figure out what SQL Propel is running, and there may be some optimizations you can do. In doctrine you'd do that with echo $query->getSqlQuery(); I'm not familiar with how to do it in Propel. You may also be able to connect to MySQL while this slow query is running and SHOW FULL PROCESSLIST should show you the query that's running.

Prefix that query with EXPLAIN and you can see how MySQL's query plan; EXPLAIN SELECT isn't very intuitive to understand if you haven't used it before, but there are good guides online.

Answer 2

I'm not sure whether this is major problem and effects on export execution time, but it's better to refactor it

    if ($this->pager->getResults() > 0) {
        foreach ($this->pager->getResults() as $row) {
        }
    }

to this

    $result = $this->pager->getResults();
    if ($result.count() > 0) { //I'm not sure how myPropelPager works and what is the type of $result. Let's hope it is Countable 
        foreach ($result as $row) {
        }
    }

I also don't know what $this->pager->getResults() does. In worst case calling it two times you're executing DB query two times. Also I'm in hope that $result is a Cursor, not array containing all results.

My optimization may help to reduce time and memory usage, but I'm not sure. Anyways after all optimizations, if you're exporting millions of rows, you'll met this problem again. The best practice is to detach this procedure from web-server context and do in background with something like Gearman in background.