>>> [!note] Migrated issue <!-- Drupal.org comment --> <!-- Migrated from issue #3605210. --> Reported by: [codeitwisely](https://www.drupal.org/user/1210320) >>> <h3>Problem/Motivation</h3> <p><code>QuotaManager::getDashboardData()</code> fetches every row for the month and aggregates per-user totals in PHP &mdash; response time grows linearly with row count. The loop also builds <code>$by_user[$uid]['rows']</code> (all raw rows per user) which the dashboard template never reads, wasting memory proportional to table size.</p> <h3>Steps to reproduce</h3> <ol> <li>Seed 10,000+ rows for the current month.</li> <li>Run <code>drush cr</code>, then load <code>/admin/reports/ai-metering</code> with query logging enabled.</li> <li>Observe: one query fetches all columns for all rows in the month; PHP iterates every row.</li> </ol> <h3>Proposed resolution</h3> <p><strong>Query 1 &mdash; numeric aggregates via GROUP BY:</strong></p> <pre><pre>SELECT<br>&nbsp; uid,<br>&nbsp; COUNT(*)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AS calls,<br>&nbsp; SUM(input_tokens)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AS total_input,<br>&nbsp; SUM(output_tokens)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AS total_output,<br>&nbsp; SUM(cached_tokens)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AS total_cached,<br>&nbsp; SUM(estimated_cost_usd)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AS total_cost,<br>&nbsp; SUM(CASE WHEN provider_type = 'local' THEN 1 ELSE 0 END)&nbsp; AS local_calls,<br>&nbsp; SUM(CASE WHEN provider_type != 'local' THEN 1 ELSE 0 END) AS cloud_calls<br>FROM {ai_metering_usage}<br>WHERE timestamp BETWEEN :start AND :end<br>GROUP BY uid</pre></pre><p><strong>Query 2 &mdash; dominant provider per user</strong> (current logic: provider with the highest single-call cost; <code>MIN()</code> for deterministic tie-breaking):</p> <pre><pre>SELECT u.uid, MIN(u.provider_id) AS provider_id<br>FROM {ai_metering_usage} u<br>INNER JOIN (<br>&nbsp; SELECT uid, MAX(estimated_cost_usd) AS max_cost<br>&nbsp; FROM {ai_metering_usage}<br>&nbsp; WHERE timestamp BETWEEN :start AND :end<br>&nbsp; GROUP BY uid<br>) m ON u.uid = m.uid AND u.estimated_cost_usd = m.max_cost<br>WHERE u.timestamp BETWEEN :start AND :end<br>GROUP BY u.uid</pre></pre><p>Scan efficiency requires the standalone <code>(timestamp)</code> index (see related issue). For higher-volume sites, a monthly rollup table &mdash; same pattern as <code>ai_metering_quota</code> &mdash; would eliminate aggregation at read time entirely.</p> <h3>Remaining tasks</h3> <ul> <li>Rewrite <code>QuotaManager::getDashboardData()</code> with the two-query approach</li> <li>Remove the unused <code>$by_user[$uid]['rows']</code> accumulation</li> <li>Revisit cache invalidation &mdash; a short TTL (5 min) may be preferable to tag-based invalidation on every write for busy sites</li> </ul> <h3>UI / API / Data model changes</h3> <p>No UI or schema changes for the short-term fix. A rollup table would require a schema addition and a backfill update hook.</p> <h3>AI assistance</h3> <p>Analysis and proposed resolution drafted with AI assistance and reviewed by the module maintainer.</p>