Efficient and fastest way to fetch huge 50,000+ rows in Zf2

Question

I need to fetch huge data in my code. I am using zf2, currently I am using Doctrine2 to deal with my database. But I just found that ORM queries are taking much time than plain mysql queries. So could you please suggest me the best way to fetch huge data from database in zf2.

Answer 1

There are a few things to consider here:

1. By default, Doctrine will hydrate the database records into php objects (entities): it fills the entities with the data from your query. This is done by "smart guessing" so to speak, which is relatively slow. Hydrate to arrays, and you are getting a much faster response. Read more about hydration in the manual.
2. By default, Doctrine does not use any caching to parse your mappings or when transforming the DQL into SQL. You can use caching to speed things up. Also, Doctrine is faster dealing with read-only entities than making them read/write. Read more about Doctrine performance issues in the manual.
3. Rendering 50,000 rows in a html table (or whatever) is a pain for your render engine (Zend\View / php) and your DOM (in the browser). The query might be optimized and load fairly quickly, rendering all these results into a view and displaying them into a browser will be really slow as well.
4. Using pagination will decrease the size of your dataset, speeding up the query and the rendering at the same time.
5. Using an ORM to help you with mapping, database abstraction and so on is really nice, but comes with the trade-off: performance. Using a library like Doctrine inherently increases execution time: you will never reach the performance of the php mysql functions in Doctrine.

So:

1. Try to decrease the number of results from one query, to speed up the database query, hydration and rendering
2. Try tuning performance with Doctrine by using array hydration, caching and read-only entities.
3. If you need to fastest way possible, don't use Doctrine. Try out Zend\Db or write all things in sql yourself