Speed up a 6000 line query with Zend Framework 2
I have a query that returns approximately 6000 results. Although this query is executed within a second in MySQL, once it is run through Zend Framework 2 it suffers a significant slowdown.
For this reason, I tried to do it in 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);
}
}
However, it doesn't seem to have sped up that much.
I think the problem might be that Zend is still trying to create a new object Thing
for each record, although this is just an incomplete list of columns. In fact, I wouldn't fill any objects. I really need multiple columns with these attributes to iterate over.
As suggested by user MonkeyZeus , the following has been used for tabletop 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 corresponds to what it is when I just run the command directly in MySQL. I believe the Overhead is in Zend, but not sure how. Again, if I had to guess, I would say that this is because Zend is adding overhead trying to be good with the results, but I'm not really sure how to proceed after that.
[I am] not so concerned about the request. This is one select statement.
goFast()
ZendindexAction()
is called - similar to other actions used throughout the project - this is slower in returning the page. One of the problems I found was that Zend$this->url()
slowed things down a bit. So I removed them, but the performance is still not great.
How can I speed this up?
source to share
When you say that a query works under the second in MySQL, what do you mean? have you tried running this query and printing ALL 6000 lines? or did you just ask about this and the first / last few of them were printed on the command line?
The problem might be that you are fetching all of them, going through the 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 the statement / cursor to the Query and then iterate over the lines when you really need it?
Your problem is not the SQL itself, but the fact that you are loading them into a PHP array immediately.
You can test it by running the time it takes to actually execute the SQL and fetch it into a PHP array.
Do not use fetchall, return the statement itself and in the function / code where you must "foreach" this array, use the statement for each line in turn.
source to share