The Cost of findAll and How to Handle Large Datasets in Doctrine
Loading 90,355 entities into memory just to iterate through them is a recipe for an out-of-memory crash, regardless of how much PHP you think your server can handle. When you call $repository->findBy() without parameters on a table that size, Doctrine constructs a query that selects every single row. The database engine returns all of them, and the ORM hydrates each result into a full object instance before handing control back to your application loop.
This approach creates two distinct bottlenecks: network latency and memory pressure. Even if your database can serve the request quickly over a wire, transmitting 90,000+ serialized objects consumes significant bandwidth. Once on the server, holding that many objects in RAM while executing logic like $this->getApiFullContact($developer) inside a foreach loop causes garbage collection to struggle. The server "dropdown" or slowdown you are experiencing is almost certainly due to this accumulation of state rather than CPU limitations.
Implementing Server-Side Pagination
To solve this, you must move the pagination logic to the database layer using Doctrine QueryBuilder's setFirstResult() and setMaxResults(). This approach ensures that only the specific slice of data required for the current operation is fetched.
Here is how you can refactor your repository method to handle paginated results efficiently:
public function getUsersByPage($offset = 0, $limit = 100)
{
$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('d')
->from('ArtelProfileBundle:Users', 'd')
->orderBy('d.id', 'DESC') // Ensure consistent ordering across pages
->setFirstResult($offset)
->setMaxResults($limit);
return $qb->getQuery()->getResult();
}
In this implementation, you pass $offset and $limit as arguments. For the first batch of 100 users, you call getUsersByPage(0, 100). The next batch calls getUsersByPage(100, 100), and so on. This guarantees that your application processes exactly 100 rows at a time, keeping memory usage constant regardless of the total table size.
Managing State Across Iterations
The original code snippet iterates blindly over all results:
foreach ($developers as $developer) {
$this->getApiFullContact($developer);
}
When switching to pagination, you need a mechanism to track which page is currently being processed. A common pattern involves passing the current page number or offset into your controller logic and looping until no more results are returned.
However, blindly incrementing an offset by 100 every time can become problematic if you have complex filters or joins that skip rows unpredictably. While simple ORDER BY id is safe for sequential iteration, real-world queries often involve conditions that make strict cursor-based pagination (like WHERE id > last_seen_id) more robust than simple offset/limit arithmetic. For now, sticking to the offset pattern works if your sort order is strictly defined by a single column without gaps.
Trade-Offs and Performance Considerations
Using setFirstResult with large offsets can degrade performance. If you jump from page 0 to page 1,000, the database must scan and discard 99,900 rows before returning the first row of the requested page. This "skipping" cost grows linearly with the offset value.
If your application requires navigating deep into a dataset frequently, consider implementing cursor-based pagination or using Doctrine's built-in Paginator. The Paginator class abstracts this complexity, managing the offset calculation and result slicing automatically:
use Knp\Component\Pager\Paginator;
public function getUsersWithPagination($page = 1, $limit = 100)
{
// Assuming a service container is available or injecting a paginator
$paginator = new Paginator();
$queryBuilder = $this->createQueryBuilder('d')
->orderBy('d.id', 'DESC');
return $paginator->paginate(
$queryBuilder,
$page,
$limit,
['queryName' => 'users']
);
}
This approach separates the query construction from the pagination logic, ensuring that your application remains performant even as the ArtelProfileBundle:Users table grows into the millions. By fetching only what is needed for the current batch, you keep your server stable and responsive.