如何提高列出嵌套主义实体的性能 [英] How to increase performance for listing nested Doctrine entities

问题描述

在此项目中，我正在使用 Zend Framework 3 和 Doctrine2 .

I am using Zend Framework 3 and Doctrine2 in this project.

我有一个嵌套的实体，它的构建像树的层次结构.

I have a nested entity which is build like a tree hierarchy.

[Project] 1 - n [WorkOrders] 1 - n [WorkOrderItems]

每个 WorkOrderItem 都有一个状态-为了简化示例，状态可以为1(未完成)或2(已完成).我现在想通过遍历所有 WorkOrderItems 并显示百分比来显示项目进度，如下所示:

Every WorkOrderItem has a status - to simplify for the example that status can be 1 for not finished or 2 for finished. I now want to display the progress of the project by iterating over all WorkOrderItems and calculate the percent like so:

$finishedWorkOrderItems = 0;
$totalWorkOrderItems = 0;
foreach ($this->getWorkOrders() as $workOrder) {
    foreach ($workOrder->getWorkOrderItems() as $workOrderItem) {
        if ($workOrderItem->getStatus() == WorkOrderItem::STATUS_FINISHED) {
            $finishedWorkOrderItems++;
        }
        $totalWorkOrderItems++;
    }
}
return 100 / $totalWorkOrderItems * $finishedWorkOrderItems;

当显示50个项目的列表时，直到完全渲染视图为止的加载时间非常长-7到9秒之间.如果不进行进度渲染列表，则渲染时间会大大减少-在0.5到2秒之间.

When displaying a list of 50 Projects the loadtime until the View is completely rendered is very high - between 7 and 9 seconds. When rendering the list without the progress the render time is significantly lower - between 0.5 and 2s.

我尝试为项目使用自定义的 DQL ，并将所需的数据加入select语句中，如下所示:

I tried to use a custom DQL for the project and join the needed data in the select statement like so :

$queryBuilder = $entityManager->createQueryBuilder();

    $queryBuilder
        ->select(['p', 'o', 'oi'])
        ->from(Project::class, 'p')
        ->join('p.orders', 'o')
        ->join('o.orderItems', 'oi')
        ->groupBy('p');

这没有给我带来可衡量的性能提升.

Which gave me no measurable performance boost.

推荐答案

为什么不直接从DQL而不是嵌套循环中获取总计数

Why not get your total count directly from DQL instead of a nested loop

SELECT p,
COUNT(DISTINCT  o.id) AS totalOrders,
COUNT(oi.id) AS totalOrdersItems,
SUM(CASE WHEN oi.status = 'some status' THEN 1 ELSE 0 END) AS totalWorkedOrdersItems
FROM Bundle\Entity\Project p
LEFT JOIN p.orders o
LEFT JOIN o.orderItems oi
GROUP BY p.id

这篇关于如何提高列出嵌套主义实体的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！