-
-
Notifications
You must be signed in to change notification settings - Fork 2.1k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Performance issue on admin dashboard #15953
Comments
Hi @vasilvestre! Thank you for opening the issue, Thanks in advance! 🍻 |
More than 10000 for this one year so 3-5k per month |
Hello @vasilvestre. We would like to inform you that we have discussed your concern and acknowledge its validity, however, it is not currently among our top priorities. It is possible that we will revisit this issue around the time of the |
I've found a solution, queries and indexes seem suboptimal. I will share the changes ! |
@vasilvestre we would be interested in this also |
Hey @vasilvestre!
|
Sorry I had dome work 😅 it's simply to change LIKE with tons of date to >= and <= which performance are WAY better. Will share code today! |
Hello @TheMilek , here's my solution, maybe I've missed something but the results are the same for all date interval. This is the base queryIt takes around 3 seconds. SELECT SUM(s0_.total) AS sclr_0,
YEAR(s0_.checkout_completed_at) AS sclr_1,
MONTH(s0_.checkout_completed_at) AS sclr_2
FROM sylius_order s0_
WHERE s0_.payment_state = 'paid'
AND s0_.channel_id = 1
GROUP BY sclr_1, sclr_2
HAVING (sclr_1 = '2024' AND sclr_1 = '2025' AND sclr_2 >= '1' AND sclr_2 <= '1')
OR (sclr_1 = '2024' AND sclr_1 <> '2025' AND sclr_2 >= '1')
OR (sclr_1 = '2025' AND sclr_1 <> '2024' AND sclr_2 <= '1')
OR (sclr_1 > '2024' AND sclr_1 < '2025');
; The explain result1,SIMPLE,s0_,,ref,"IDX_6196A1F972F5A1AA,IDX_6196A1F955A0F9B",IDX_6196A1F972F5A1AA,5,const,704642,10,Using index condition; Using where; Using temporary; Using filesort And this is my queryIt takes about 250ms. SELECT SUM(s0_.total) AS sclr_0,
YEAR(s0_.checkout_completed_at) AS sclr_1,
MONTH(s0_.checkout_completed_at) AS sclr_2
FROM sylius_order s0_
WHERE s0_.payment_state = 'paid'
AND s0_.channel_id = 1
AND s0_.checkout_completed_at >= '2024-01-01'
AND s0_.checkout_completed_at < '2026-01-01'
GROUP BY sclr_1, sclr_2; The explain result :1,SIMPLE,s0_,,range,"IDX_6196A1F972F5A1AA,IDX_6196A1F955A0F9B",IDX_6196A1F955A0F9B,6,,73554,5,Using index condition; Using where; Using MRR; Using temporary; Using filesort The controller<?php
declare(strict_types=1);
namespace App\Dashboard;
use App\Doctrine\Repository\Sylius\OrderRepository;
use Sylius\Component\Core\Dashboard\Interval;
use Sylius\Component\Core\Dashboard\SalesDataProviderInterface;
use Sylius\Component\Core\Dashboard\SalesSummary;
use Sylius\Component\Core\Dashboard\SalesSummaryInterface;
use Sylius\Component\Core\Model\ChannelInterface;
use Sylius\Component\Core\OrderPaymentStates;
use Sylius\Component\Resource\Repository\RepositoryInterface;
class SalesDataProvider implements SalesDataProviderInterface
{
/**
* @param OrderRepository $orderRepository
*/
public function __construct(
private readonly RepositoryInterface $orderRepository
) {
}
public function getSalesSummary(
ChannelInterface $channel,
\DateTimeInterface $startDate,
\DateTimeInterface $endDate,
Interval $interval
): SalesSummaryInterface {
$queryBuilder = $this->orderRepository->createQueryBuilder('o')
->select('SUM(o.total) AS total')
->andWhere('o.paymentState = :state')
->andWhere('o.channel = :channel')
->setParameter('state', OrderPaymentStates::STATE_PAID)
->setParameter('channel', $channel)
;
switch ($interval->asString()) {
case 'year':
$queryBuilder
->addSelect('YEAR(o.checkoutCompletedAt) as year')
->groupBy('year')
->andWhere('o.checkoutCompletedAt >= :startYear AND o.checkoutCompletedAt <= :endYear')
->setParameter('startYear', $startDate->format('Y-m-d'))
->setParameter('endYear', $endDate->format('Y-m-d'))
;
$dateFormatter = static fn (\DateTimeInterface $date): string => $date->format('Y');
$resultFormatter = static fn (array $data): string => $data['year'];
break;
case 'month':
$queryBuilder
->addSelect('YEAR(o.checkoutCompletedAt) as year')
->addSelect('MONTH(o.checkoutCompletedAt) as month')
->groupBy('year')
->addGroupBy('month')
->andWhere('o.checkoutCompletedAt >= :startYear AND o.checkoutCompletedAt <= :endYear')
->setParameter('startYear', $startDate->format('Y-m-d'))
->setParameter('endYear', $endDate->format('Y-m-d'))
;
$dateFormatter = static fn (\DateTimeInterface $date): string => $date->format('n.Y');
$resultFormatter = static fn (array $data): string => $data['month'] . '.' . $data['year'];
break;
case 'week':
$queryBuilder
->addSelect('YEAR(o.checkoutCompletedAt) as year')
->addSelect('WEEK(o.checkoutCompletedAt) as week')
->groupBy('year')
->addGroupBy('week')
->andWhere('o.checkoutCompletedAt >= :startYear AND o.checkoutCompletedAt <= :endYear')
->setParameter('startYear', $startDate->format('Y-m-d'))
->setParameter('endYear', $endDate->format('Y-m-d'))
;
$dateFormatter = static fn (\DateTimeInterface $date): string => (ltrim($date->format('W'), '0') ?: '0') . ' ' . $date->format('Y');
$resultFormatter = static fn (array $data): string => $data['week'] . ' ' . $data['year'];
break;
case 'day':
$queryBuilder
->addSelect('YEAR(o.checkoutCompletedAt) as year')
->addSelect('MONTH(o.checkoutCompletedAt) as month')
->addSelect('DAY(o.checkoutCompletedAt) as day')
->groupBy('year')
->addGroupBy('month')
->addGroupBy('day')
->andWhere('o.checkoutCompletedAt >= :startYear AND o.checkoutCompletedAt <= :endYear')
->setParameter('startYear', $startDate->format('Y-m-d'))
->setParameter('endYear', $endDate->format('Y-m-d'))
;
$dateFormatter = static fn (\DateTimeInterface $date): string => $date->format('j.n.Y');
$resultFormatter = static fn (array $data): string => $data['day'] . '.' . $data['month'] . '.' . $data['year'];
break;
default:
throw new \RuntimeException(sprintf('Interval "%s" not supported.', $interval->asString()));
}
$ordersTotals = $queryBuilder->getQuery()->getArrayResult();
$salesData = [];
$period = new \DatePeriod($startDate, \DateInterval::createFromDateString(sprintf('1 %s', $interval->asString())), $endDate);
foreach ($period as $date) {
$salesData[$dateFormatter($date)] = 0;
}
foreach ($ordersTotals as $item) {
$salesData[$resultFormatter($item)] = (int) $item['total'];
}
$salesData = array_map(
static fn (int $total): string => number_format(abs($total / 100), 2, '.', ''),
$salesData
);
return new SalesSummary($salesData);
}
} |
Describe the proposed solution
When you have a lot of data, the dashboard can take up to 5-10 sec more depending of data. It slow the login process when you do not even need to get stats.
What could be done :
Additional context
Here's a Sentry analysis of the time :
You can see the statistics do not contain code overwrite
The text was updated successfully, but these errors were encountered: