CODEWITHSUNDEEP
phpmysqlzend-framework2
user49438
user49438

Reputation: 909

Speed up 6,000 row query with Zend Framework 2

I have a query that returns roughly 6,000 results. Although this query executes in under a second in MySQL, once it is run through Zend Framework 2, it experiences a significant slowdown.

For this reason, I tried to do it a more "raw" way with PDO: 

class ThingTable implements ServiceLocatorAwareInterface
{
        // ...

        public function goFast()
        {


            $db_config = $this->getServiceLocator()->get('Config')['db'];
            $pdo = new PDO($db_config['dsn'], $db_config['username'], $db_config['password']);

            $statement = $pdo->prepare('SELECT objectNumber, thingID, thingmaker, hidden, title FROM Things ', array(PDO::MYSQL_ATTR_COMPRESS, PDO::CURSOR_FWDONLY));
            $statement->execute();

            return $statement->fetchAll(PDO::FETCH_ASSOC);


        }
}

This doesn't seem to have much of a speedup, though.

I think the problem might be that Zend is still trying to create a new Thing object for each record, even though it is only a partial list of columns. I'd really be okay not populating any objects. I really just need a few columns with those attributes to iterate over.

As suggested by user MonkeyZeus, the following was used for bench-marking:

$start = microtime(true);
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
echo (microtime(true) - $start).' seconds';

And in response:

In a VM, that returns 0.0050520896911621. This is in line with what it is when I just run the command straight in MySQL. I believe the overhead is in Zend, but not sure how to quite go about that. Again if I had to guess, I'd say it is because Zend is adding overhead while trying to be nice with the results, but I'm not quite sure how to proceed after that.

[I'm] not so worried about the query. It is a single select statement. goFast() gets called by the Zend indexAction() --similar to other actions used across the project--this one is just way slower at returning the page. One problem I found was that Zend's $this->url() was slowing things down a bit. So I removed those, but the performance still isn't great.

How can I speed this up?

Upvotes: 3

Views: 456

Answers (1)

ogres
ogres

Reputation: 3690

When you say , that query runs under a second in MySQL , what do you mean ? did you try to run this query and print ALL 6000 rows ? or you just queried this and command line printed first/last few of them ?

The problem might be that , you are fetching them all , going through cursor , you are copying all the data ( 6000 rows ) from MySQL to PHP and then returning it , are you sure you want to do this ?

Maybe you could return a statement/cursor to the Query and then iterate through rows when you really need it ?

Your problem is not the SQL itself , but fetching them into PHP array all at once.

You can test it by logging the time it needs to actually execute SQL and fetching it into PHP array.

Do not use fetchall , return the statement itself and in the function/code where you have to run "foreach" this array , use statement to fetch each row one by one.

Upvotes: 1

Related Questions