Improve widget performance

[DanielSiepmann]

Widgets can become slow depending on dataset size.

The main issue is retrieval of data from database. One approach is to add an index, but that will mainly improve performance of widget "PageviewsPerPage". The widget "PageviewsPerDay" will still be slow. I've not yet found a proper way to improve performance for that widget. The corresponding PHP File is: https://github.com/DanielSiepmann/tracking/blob/main/Classes/Dashboard/Provider/PageviewsPerDay.php

[DanielSiepmann]

The following adjusts the widget to only issue a single query with subqueries instead of x queries. But that didn't result in better performance:

diff --git a/Classes/Dashboard/Provider/PageviewsPerDay.php b/Classes/Dashboard/Provider/PageviewsPerDay.php
index f83c648..9c8f7af 100644
--- a/Classes/Dashboard/Provider/PageviewsPerDay.php
+++ b/Classes/Dashboard/Provider/PageviewsPerDay.php
@@ -106,29 +106,30 @@ class PageviewsPerDay implements ChartDataProviderInterface
             $endPeriod = (int) strtotime('-' . $daysBefore . ' day 23:59:59');

             $labels[] = date($this->dateFormat, $timeForLabel);
-            $data[] = $this->getPageviewsInPeriod($startPeriod, $endPeriod);
+            $data[] = [
+                'start' => $startPeriod,
+                'end' => $endPeriod,
+            ];
         }

         return [
             $labels,
-            $data,
+            $this->getData($data),
         ];
     }

-    private function getPageviewsInPeriod(int $start, int $end): int
+    private function getData(array $data): array
     {
-        $constraints = [
-            $this->queryBuilder->expr()->gte('crdate', $start),
-            $this->queryBuilder->expr()->lte('crdate', $end),
-        ];
+        $queryBuilder = clone $this->queryBuilder;
+
+        $this->queryBuilder->count('*');
+        $this->queryBuilder->from('tx_tracking_pageview');
+        $constraints = [];

         if (count($this->pagesToExclude)) {
             $constraints[] = $this->queryBuilder->expr()->notIn(
                 'tx_tracking_pageview.pid',
-                $this->queryBuilder->createNamedParameter(
-                    $this->pagesToExclude,
-                    Connection::PARAM_INT_ARRAY
-                )
+                ':pidsToExclude'
             );
         }

@@ -142,11 +143,18 @@ class PageviewsPerDay implements ChartDataProviderInterface
             );
         }

-        return (int)$this->queryBuilder
-            ->count('*')
-            ->from('tx_tracking_pageview')
-            ->where(...$constraints)
-            ->execute()
-            ->fetchColumn();
+        foreach ($data as $key => $conditions) {
+            $subQueryConstraints = array_merge($constraints, [
+                $this->queryBuilder->expr()->gte('crdate', $conditions['start']),
+                $this->queryBuilder->expr()->lte('crdate', $conditions['end']),
+            ]);
+            $subqueryBuilder = clone $this->queryBuilder;
+            $subqueryBuilder->where(... $subQueryConstraints);
+            $queryBuilder->addSelectLiteral('(' . $subqueryBuilder->getSQL() . ') as "' . $key . '"');
+        }
+
+        $queryBuilder->setParameter(':pidsToExclude', $this->pagesToExclude, Connection::PARAM_INT_ARRAY);
+
+        return $queryBuilder->execute()->fetch();
     }
 }

Any suggestion is welcome.