Zend Fetch on large table 10,000 + rows, ~100 columns

Question

I have an sql view which returns over 10,000 records with about 120 columns (ridiculous, I know).

The view data is gathered from the database in PHP using a Zend_Db_Table_Abstract object. We have a report controller which does a fetchAll (which obviously creates a massive array) and stores the array in a view object. Next we have a view script which iterates over the array and echoes a json formatted array.

This is slowwww. Like over 1 minute to render this report, what can I do to speed this up?

Bare in mind I am very new to this project and have no knowledge of Zend and little of PHP. Also I can't give away to much due to data protection etc.

If you could ask what you need to help you gather the picture that would be great. But basically this is what the script does:

Report controller:

Setup some basic json report data (for data tables)
$this->view->report_data = fetchALL($oSelect);

View script:

Output the basic json stuff, then:
"report_data": [
<?php 
    for($i = 0; $i < count($this->report_data); $i++){
        echo "[";
        foreach($this->columns as $k=>$col){
            echo '"'. $this->escape($this->report_data[$i][$col]) .'"';
            if($k < count($this->columns) -1 ){
                echo ',';
            }
        }
        echo "]";
        if($i < count($this->report_data) -1){
            echo ",";
        }
        echo"\n";
    }
?> ]

So after doing some research I gathered that I could use fetch() instead of fetchAll() so to get 1 row at a time, to avoid the massive array. However I tried to use the fetch() and got this error:

Call to undefined method Zend_Db_Table_Abstract::fetch()

So now I'm bummed.

My idea was to get each row and output each row Json formatted, however I don't really know how I would do this in the report controller/ view scenario. Would I have to just get rid of the view script and just use the controller to output the data?

Anyway why does fetch() not work, I can't actually see it in the docs either, well there is some array function _fetch()

Any help would be appreciated, any if you need more info just ask. Thanks

In your view, are you showing all these 10 000 records on one page? I'm not familiar with Zend, but usually when single requests get too heavy, you can use paging. So you're not fetching & showing all the data at once, but in decent sized pieces. Maybe Zend Paginator could help? — hade
First make sure php is the bottlenect. Run your query via phpmyadmin or mysql console to see if it even runs fast there. — Maerlyn
View takes about 2 seconds to load via psql command line. @hade The view output the whole lot of records in json format. Then datatables is used to process this and display/paginate it. — Aydin Hassan
Why are you writing your own encode function when you could simply do: foreach($this->report_data as $d) echo json_encode($d);. If you need the key 'report_data', simply add the sql result to an array with the key — Ashley
@Ashley I didn't write that encode function and yes you are right, that is what I want to do, however it seems using the json_encode function it doesn't replace NULLS. using the encode function that I posted, it returns empty strings instead of NULLS. I'm not sure how but it seems NULLS are breaking the datatables javascript. — Aydin Hassan

Boby Boby · Accepted Answer · 2012-05-29T09:43:55

See the docs for fetch().

The following is not very elegant, but could help you:

Model:

...
function getResults()
{
    return $db->query('SELECT * FROM bugs');
}
...

Controller:

...
$this->view->report_data = $model->getResults();
...

View:

...
while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
    echo Zend_Json::encode($row);
}
...

I haven't tested the code, but you should get the idea.

Zend Fetch on large table 10,000 + rows, ~100 columns

3 Answers