Optimierung von DuckDB-Abfragen

[Terdious]

Aufgrund der Diskussionen über das RAM-Speicher-Sperren durch DuckDB wäre es sinnvoll zu überprüfen, ob wir die schwersten Abfragen optimieren können.

Die Paginierung ist eine der Überlegungen, die angestellt werden müssen.

Hier ein Beispiel für eine meiner schwersten Seiten zur Energieverfolgung, wobei jedes Diagramm 8 Kurven enthält (die möglicherweise noch erhöht werden können) und alles für jede Phase (L1/L2/L3) + den Gesamtwert detailliert ist.

Die zeitlichen Visualisierungen bis zu 3 Monaten sind flüssig. Aber die Anzeige für ein Jahr ist langsamer, etwa 10 Sekunden.

Anfängliche Abfrage bei der Anzeige des Dashboards mit Diagrammen für die letzte Stunde:

Anfang des Ladens beim Wechsel auf das Jahr im ersten Diagramm

Ende des Ladens

Abfragedetails

Zeitliche Details der Abfrage

Nach dem Update auf 4.66.3 bestätige ich, dass ich für die Jahresansicht nur 1 Sekunde länger brauche und keinen Unterschied für den Rest spüre:

@pierre-gilles, zögere nicht, mir Bescheid zu sagen, wenn du weitere Details benötigst

[pierre-gilles]

@Terdious

Die Paginierung ist eine der Überlegungen, die angestellt werden müssen.

Keine Paginierung nötig, wir machen Sampling, wir geben maximal 300 Zeilen pro Grafik zurück

Ich werde mir das mit diesen Informationen ansehen, danke für den detaillierten Post!

[pierre-gilles]

Kannst du Tests an deiner Datenbank durchführen?

Ich möchte sehen, was mit dieser Abfrage passiert:

SELECT
    TIME_BUCKET(INTERVAL ? MINUTES, created_at) AS created_at,
    AVG(value) AS value,
    MAX(value) AS max_value,
    MIN(value) AS min_value,
    SUM(value) AS sum_value,
    COUNT(value) AS count_value
FROM
    t_device_feature_state
WHERE device_feature_id = ?
AND created_at > ?
GROUP BY 1
ORDER BY created_at;

Die ? müssen ersetzt werden:

• Das erste durch 1752
• Das zweite durch die umfassendste device_feature_id, die du hast
• Das dritte durch das Datum von vor einem Jahr, also '2024-12-14T10:04:11.478Z'

Falls du vergleichen möchtest, hier ist die aktuelle Abfrage:

  WITH intervals AS (
        SELECT
            created_at,
            value,
            NTILE(300) OVER (ORDER BY created_at) AS interval
        FROM
            t_device_feature_state
        WHERE device_feature_id = ?
        AND created_at > ?
    )
    SELECT
        MIN(created_at) AS created_at,
        AVG(value) AS value,
        MAX(value) AS max_value,
        MIN(value) AS min_value,
        SUM(value) AS sum_value,
        COUNT(value) AS count_value
    FROM
        intervals
    GROUP BY
        interval
    ORDER BY
        created_at;

[Terdious]

Eine Frage, warum 1752? Das sind Buckets von 29h12??

Ansonsten hier die Ergebnisse:

1. Aggregation nach Zeit-Bucket - Abfrage TimeBucket

SELECT
    TIME_BUCKET(INTERVAL 1752 MINUTES, created_at) AS created_at,
    AVG(value) AS value,
    MAX(value) AS max_value,
    MIN(value) AS min_value,
    SUM(value) AS sum_value,
    COUNT(value) AS count_value
FROM
    t_device_feature_state
WHERE
    device_feature_id = 'e9079170-4654-4fad-b726-23a36c793905'
    AND created_at > TIMESTAMP '2024-12-14 10:04:11.478'
GROUP BY 1
ORDER BY created_at;

Ergebnis

• 301 Zeilen zurückgegeben
• Ausführungszeit ≈ 0,486 s
• Feste Zeit-Buckets (~29h)

2. Aufteilung nach Quantilen - Abfrage NTILE(300)

WITH intervals AS (
    SELECT
        created_at,
        value,
        NTILE(300) OVER (ORDER BY created_at) AS interval
    FROM
        t_device_feature_state
    WHERE
        device_feature_id = 'e9079170-4654-4fad-b726-23a36c793905'
        AND created_at > TIMESTAMP '2024-12-14 10:04:11.478'
)
SELECT
    MIN(created_at) AS created_at,
    AVG(value) AS value,
    MAX(value) AS max_value,
    MIN(value) AS min_value,
    SUM(value) AS sum_value,
    COUNT(value) AS count_value
FROM
    intervals
GROUP BY
    interval
ORDER BY
    created_at;

Ergebnis

• 300 Zeilen zurückgegeben
• Ausführungszeit ≈ 0,370 s
• Ausgewogene Intervalle in Bezug auf die Anzahl der Punkte

[pierre-gilles]

@Terdious

Eine Frage, warum 1752? Das sind Buckets von 29h12??

Genau! Das ist 525600/300

Aber die Performance ist nicht besser, und beide sind eher gut in Sachen Performance…

Dann, in deinem Diagramm hast du 10 Funktionen, also musst du * 10 machen, was deine Performance erklärt.

Die Lösung liegt vielleicht nicht dort, aber vielleicht in einer gemeinsamen Abfrage für 10 Funktionen.

[Terdious]

Bon, ich habe gerade einige Tests durchgeführt… Und ich würde sagen, dass das Problem sicherlich nicht die Abfragen an DuckDB sind (ich glaube, wir haben diese Art von Tests bereits direkt in der Entwicklung in Gladys durchgeführt ^^). Es muss einfach die Darstellung oder eher der Aufbau der Serien sein…

1. Aggregation nach Zeitintervall - TimeBucket-Abfrage

SELECT
    device_feature_id,
    TIME_BUCKET(INTERVAL 1752 MINUTES, created_at) AS created_at,
    AVG(value) AS value,
    MAX(value) AS max_value,
    MIN(value) AS min_value,
    SUM(value) AS sum_value,
    COUNT(value) AS count_value
FROM t_device_feature_state
WHERE device_feature_id IN (
	'eaa982b7-a3c8-4b4d-b2d0-5a5cf5245938',
    'e9079170-4654-4fad-b726-23a36c793905',
    'c59fca37-54f3-418b-b27c-857e08e2a0fb',
    'da79df66-dcff-438e-859c-39b641fd15e9',
    '5eba10ac-4c3a-479c-aba7-a1e74a00d914',
    '4b5e90f0-a86b-405b-8ca1-953705ef79a3',
    'c92f8fa3-d841-4692-bd3a-e5addb760a73',
    'b5445f10-7aa9-41bf-8cae-d2f30498652f',
    '08e90478-4bb2-4a08-986f-3d6f212c95f1',
    '85311f8c-334f-4b7e-8294-2b841c0b8fe8'
)
AND created_at > TIMESTAMP '2024-12-14 10:04:11.478'
GROUP BY
    device_feature_id,
    created_at
ORDER BY
    device_feature_id,
    created_at;

2. Aufteilung nach Quantilen - NTILE(300)-Abfrage

WITH intervals AS (
    SELECT
        device_feature_id,
        created_at,
        value,
        NTILE(300) OVER (
            PARTITION BY device_feature_id
            ORDER BY created_at
        ) AS interval
    FROM t_device_feature_state
    WHERE device_feature_id IN (
		'eaa982b7-a3c8-4b4d-b2d0-5a5cf5245938',
	    'e9079170-4654-4fad-b726-23a36c793905',
	    'c59fca37-54f3-418b-b27c-857e08e2a0fb',
	    'da79df66-dcff-438e-859c-39b641fd15e9',
	    '5eba10ac-4c3a-479c-aba7-a1e74a00d914',
	    '4b5e90f0-a86b-405b-8ca1-953705ef79a3',
	    'c92f8fa3-d841-4692-bd3a-e5addb760a73',
	    'b5445f10-7aa9-41bf-8cae-d2f30498652f',
	    '08e90478-4bb2-4a08-986f-3d6f212c95f1',
	    '85311f8c-334f-4b7e-8294-2b841c0b8fe8'
    )
    AND created_at > TIMESTAMP '2024-12-14 10:04:11.478'
)
SELECT
    device_feature_id,
    MIN(created_at) AS created_at,
    AVG(value) AS value,
    MAX(value) AS max_value,
    MIN(value) AS min_value,
    SUM(value) AS sum_value,
    COUNT(value) AS count_value
FROM intervals
GROUP BY
    device_feature_id,
    interval
ORDER BY
    device_feature_id,
    created_at;

Ich habe also einen Performance-Test auf dem Dashboard durchgeführt:

ChatGPT-Studie ^^ :

Direkte Lesung deiner Performance-Aufzeichnung

Gesamtzeit

• Gesamt ≈ 13.190 ms (13,2 s)

Verteilung (linkes Panel)

• Anzeige (Rendering) : 2.330 ms
• Darstellung (Paint / Layout) : 791 ms
• Skript (JS) : 563 ms
• Netzwerk-Ladezeit : 27 ms
• System / nicht zugeordnet : ~5.200 ms
• Gesamt „nützlich“ sichtbar : ~4 s
• Große Lücke „nicht zugeordnet / idle / Wartezeit“ : ~9 s

Sofortige Schlussfolgerung :
Es ist weder das Netzwerk,
noch DuckDB,
noch reines JavaScript,
es ist überwiegend Wartezeit, bevor die nützliche Darstellung beginnt.

---

Fokus auf den Netzwerkteil (wichtig für Gladys)

In deiner rechten Tabelle:

• Abfrage: aggregated_states (10.5.0.227)
• Übertragene Größe : 213 ko
• Ausführungszeit : ~260 ms

Das bestätigt, was wir vermutet haben:

• Die Backend-Abfrage ist schnell
• Die Payload ist angemessen
• < 300 ms, also kein Problem

Jetzt können wir ohne Zweifel sagen:

Die 10–13 Sekunden kommen nicht von den DuckDB-Abfragen.

---

Wo gehen dann die ~9 Sekunden verloren?

Laut Flame Chart und Timeline:

„Warte-/nicht zugeordnete“ Phase (~9 s)

Das ist typischerweise:

• Wartezeit auf einen kompletten React-State
• Wartezeit, bis alle Serien bereit sind
• Synchronisation vor dem Rendern des Diagramms
• oder Logik wie:
  • „Warten auf alle Promises“
  • „Warten, bis X berechnet ist, bevor gerendert wird“

Klassisches Anti-Pattern bei komplexen Dashboards.

---

„Rendering + Layout“-Phase (~3 s)

Hier sind wir eindeutig bei:

• vielen Punkten
• vielen Serien
• wahrscheinlich:
  • Neuberechnung von Skalen
  • Neuberechnung von Legenden
  • Tooltips
  • min/max
  • Animationen

Das ist kohärent mit einem jährlichen Multi-Kurven-Diagramm.

Schlussfolgerung (die du bereits formulieren kannst)

Du kannst bereits mit Beweisen sagen:

Die Chrome Performance-Messungen zeigen, dass:

• Die Backend-Abfrage ist < 300 ms
• Die Payload ist ~213 ko
• Das reine JS < 600 ms

Die wahrgenommenen ~13 s sind hauptsächlich auf eine Wartephase + grafische Darstellung auf der Frontend-Seite zurückzuführen.
Die Optimierung muss daher hauptsächlich im React-/Diagramm-Rendering-Pipeline erfolgen, nicht bei DuckDB.

Nächste konkrete Ansätze (wenn du weitergehen möchtest)

Ohne den Code zu sehen, sind die offensichtlichen Hebel:

• Das Diagramm progressiv rendern (Serien eine nach der anderen)
• Die Anzahl der Punkte vor dem Rendern begrenzen (Hard Cap)
• Animationen/Übergänge bei langen Ansichten deaktivieren
• Virtualisierung / reines Canvas, falls noch nicht geschehen

[pierre-gilles]

Ich bin nicht einverstanden mit ChatGPT, man sieht deutlich in deinen Screenshots, dass die Backend-Antwort 11 Sekunden zum Antworten braucht:

@Terdious

Was nicht überraschend ist, du hast mir gezeigt, dass eine Anfrage 400-500 ms dauert, also 500 ms * 10 = 5 Sekunden im besten Fall

@Terdious

Aufteilung nach Quantilen - Anfrage NTILE(300)

Genau, du beweist meinen Punkt: Im Moment führen wir keine Anfrage mit 10 IDs aus, wir führen 10 separate Anfragen durch!

[Terdious]

Ok, ich verstehe tatsächlich.
Hier ist das Ergebnis für die 10 separaten Anfragen:

Ich bin auf denselben Features wie die angezeigte Kurve.

[pierre-gilles]

@Terdious

Hier sind die Ergebnisse für die 10 separaten Anfragen:

Das beweist eher den Punkt, denn man muss bedenken, dass wenn du dein Dashboard anzeigst, du nicht nur dieses Widget hast, sondern potenziell 5 weitere Widgets, also sind wir eher bei 50 Anfragen

[Terdious]

Oui oui, je suis en accord avec ce que tu dis !!

@pierre-gilles

qu’il faut prendre en compte que quand tu affiches ton tableau de bord, tu n’as pas juste ce widget, mais potentiellement 5 autres widgets, donc on est plus sur 50 requêtes

Mmmmh ça pas d’accord, puisque l’affichage à l’année est envoyer après le 1er chargement (affichage à l’heure au refresh de la page). Lorsque je sélectionne le graph à l’année, je n’active que celui-ci. On voit d’ailleurs bien ensuite les autres requetes ‹ live › s’ajouter et se mettre en attente à la suite.

Mais je suis ok pour dire qu’actuellement on est plus de l’ordre de 3 à 5s d’attente de requetes DuckDB.