Selektierte aufeinander folgende Datensätze - Abfrage - Laufzeitproblem

[daniel.schmid]

Hallo Forumgemeinde!

Es besteht eine Datenbank, welche Start und Endzeiten erfasst welche einem Produkt zugeordnet werden. Nun soll eine mittlere Zeitdifferenz anhand der Startzeiten ermittelt werden, welche sich aus zwei aufeinander folgenden Datensätzen des gleichen Produkts beziehen. Diese sollen dann nach gewissen Zeitformaten und Produkten Gruppiert werden.

Bsp:

Produkt | Startzeit | Endzeit
_________________________________
A |01.01.2010 14:00:01 | 01.01.2010 14:01:01
B |01.01.2010 14:01:03 | 01.01.2010 14:02:01
A |01.01.2010 16:00:01 | 01.01.2010 17:03:01
B |01.01.2010 15:01:03 | 01.01.2010 15:02:01
A |02.01.2010 14:00:01 | 02.01.2010 14:01:01
B |02.01.2010 14:01:03 | 02.01.2010 14:02:01
A |02.01.2010 16:00:01 | 02.01.2010 17:03:01
B |02.01.2010 15:01:03 | 02.01.2010 15:02:01


Erg:
Produkt | avg(Zeitdifferenz) | Zeitformat(zb. Auf Tage bezogen)
__________________________________
A | 00:10:13 | 01.01.2010
B | 00:12:13 | 01.01.2010
A | 00:09:13 | 02.01.2010
B | 00:15:13 | 02.01.2010

(Die Werte sind alle nur symbolisch zu interpretieren.)

Nun ist es so, dass meine Datenbank aus mehr als 500.000 Einträgen besitzt und meine Subquery sehr hohe Laufzeiten hervorruft.

select 
      Produkt,
      avg(timediff(
                (select
                 b2.Startzeit
                 from baugruppeallgemein b2
                where b2.Startzeit> b1.Startzeit 
                 and 
                 b1.Produkt= b2.Produkt
                 and 
               b2.Startzeit<= (select 
                                           max(b3.Startzeit) 
                                           from baugruppeallgemein b3 
                                          where 
                                          b3.Produkt= b1.Produkt)
                           limit 1), 
              b1.StartTime)),
    date_format(Startzeit,'%v%/%y')
from 
baugruppeallgemein b1 
group by date_format(Startzeit,'%v%/%y'), Produkt

Habt ihr eine Idee wie ich das besser lösen kann?

Würde mich sehr auf eure Unterstützung freuen!
Vielen Dank im vorraus!

Grüße Daniel

 

[akretschmer]

Habt ihr eine Idee wie ich das besser lösen kann?

Prinzipiell ginge das sicherlich schneller, schuld sind hier die verschachtelten Selects. Der bessere Weg wäre eine Abfrage unter benutzung von Window-Funktionen. Allerdings kann MySQL, wie so viele andere modernen Abfragetechniken, das auch nicht.

Also, die beste Chance und der einfachste Weg wäre vermutlich ein Umstieg auf z.B. PostgreSQL, was solche Window-Funktionen (und vieles mehr) halt kann.

 

[ukulele]

Kann es sein das dein Query nur, sry, Schwachsinn ermittelt? Dein Subquery kann doch mehrere Werte ermitteln, darum vermutlich auch das LIMIT 1 aber ohne ORDER BY. Was genau soll da herauskommen? Du bekommst einen beliebigen Wert, höher als b1.starttime und kleiner gleich dem maximalen Wert, was sowieso gegeben ist (b3 scheint mir nutzlos).

 

[daniel.schmid]

Danke für die raschen Antworten!

@akretschmer : danke für den Hinweis nur leider ist es nicht möglich die Datenbrank zu wechseln da mehrere Systeme davon abhängen und das zuviel Aufwand wäre für eine Abfrage

@ukulele: du hast Recht b3 scheint auch mir nutzlos zu sein ist aber aus probieren entstanden und anfangs war diese Subquery nicht enthalten. Zum Limit 1: ich konnte durch diese ergänzung (ein wenig) Zeit sparen durch ersetzen von min(b2.Startzeit). Da die Einträge chronologisch eingefügt werden und die MYSQL-Datenbank standartmäßig auf ASC eingestellt ist, ist mit dieser Abfrage der erste Wert gleich dem Minimum Wert. <- So mein Gedanke

Korrigiert mich gerne wenn ich falsch liege.

Danke für eure Anregungen.

 

[ukulele]

Auch wenn die Einträge "chronologisch" in die Tabelle geschrieben wird gibt es keine Garantie das SQL sie auch so ausgibt. Du solltest unbedingt definieren, welcher Datensatz zuerst kommt.

Also um das Grundproblem zu lösen sollte eigentlich

SELECT   b1.produkt,
     b1.startzeit,
     (   SELECT   min(b2.startzeit)
       FROM   baugruppeallgemein b2
       WHERE   b1.produkt = b2.produkt
       AND     b2.startzeit > b1.startzeit ) AS naechste_startzeit
FROM   baugruppeallgemein b1

der gängige Weg sein und auch bei 500k+ Datensätzen funktionieren. Wenn das langsam ist, hast du vieleicht keinen Index auf produkt liegen? Vieleicht hilft dir ein Clustered Index produkt,startzeit weiter.

Wenn das Query schnell ist kann man darauf aufbauen...

 

[akretschmer]

Nur um mal zu zeigen, welche Vorteile man durch Window-Funktionen hier hätte:

die Tabelle

test=*# select * from baugruppeallgemein ;
 produkt |  startzeit  |  endzeit
---------+---------------------+---------------------
 a  | 2010-01-01 14:00:01 | 2010-01-01 14:01:01
 b  | 2010-01-01 14:01:03 | 2010-01-01 14:02:01
 a  | 2010-01-01 16:06:01 | 2010-01-01 17:03:01
 b  | 2010-01-01 15:01:03 | 2010-01-01 15:02:01
 a  | 2010-01-02 14:00:01 | 2010-01-02 14:01:01
 b  | 2010-01-02 14:01:03 | 2010-01-02 14:02:01
 a  | 2010-01-02 16:00:01 | 2010-01-02 17:03:01
 b  | 2010-01-02 15:01:03 | 2010-01-02 15:02:01
(8 rows)

Lösung @ukulele Resultat und explain analyse:

test=*# select b1.produkt, b1.startzeit, (select min(b2.startzeit) from baugruppeallgemein b2 where b1.produkt=b2.produkt and b2.startzeit > b1.startzeit) as naechste_startzeit from baugruppeallgemein b1 order by produkt, startzeit;
 produkt |  startzeit  | naechste_startzeit
---------+---------------------+---------------------
 a  | 2010-01-01 14:00:01 | 2010-01-01 16:06:01
 a  | 2010-01-01 16:06:01 | 2010-01-02 14:00:01
 a  | 2010-01-02 14:00:01 | 2010-01-02 16:00:01
 a  | 2010-01-02 16:00:01 |
 b  | 2010-01-01 14:01:03 | 2010-01-01 15:01:03
 b  | 2010-01-01 15:01:03 | 2010-01-02 14:01:03
 b  | 2010-01-02 14:01:03 | 2010-01-02 15:01:03
 b  | 2010-01-02 15:01:03 |
(8 rows)

test=*# explain analyse select b1.produkt, b1.startzeit, (select min(b2.startzeit) from baugruppeallgemein b2 where b1.produkt=b2.produkt and b2.startzeit > b1.startzeit) as naechste_startzeit from baugruppeallgemein b1 order by produkt, startzeit;
  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=10.26..10.28 rows=8 width=10) (actual time=0.103..0.105 rows=8 loops=1)
  Sort Key: b1.produkt, b1.startzeit
  Sort Method: quicksort  Memory: 17kB
  ->  Seq Scan on baugruppeallgemein b1  (cost=0.00..10.14 rows=8 width=10) (actual time=0.028..0.078 rows=8 loops=1)
  SubPlan 1
  ->  Aggregate  (cost=1.12..1.13 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=8)
  ->  Seq Scan on baugruppeallgemein b2  (cost=0.00..1.12 rows=1 width=8) (actual time=0.003..0.004 rows=2 loops=8)
  Filter: ((startzeit > b1.startzeit) AND (b1.produkt = produkt))
  Rows Removed by Filter: 6
 Planning time: 0.147 ms
 Execution time: 0.143 ms
(11 rows)

Lösung mit Window-Funktion:

test=*# select produkt, startzeit, lead(startzeit) over (partition by produkt order by startzeit) from baugruppeallgemein ;
 produkt |  startzeit  |  lead
---------+---------------------+---------------------
 a  | 2010-01-01 14:00:01 | 2010-01-01 16:06:01
 a  | 2010-01-01 16:06:01 | 2010-01-02 14:00:01
 a  | 2010-01-02 14:00:01 | 2010-01-02 16:00:01
 a  | 2010-01-02 16:00:01 |
 b  | 2010-01-01 14:01:03 | 2010-01-01 15:01:03
 b  | 2010-01-01 15:01:03 | 2010-01-02 14:01:03
 b  | 2010-01-02 14:01:03 | 2010-01-02 15:01:03
 b  | 2010-01-02 15:01:03 |
(8 rows)

test=*# explain analyse select produkt, startzeit, lead(startzeit) over (partition by produkt order by startzeit) from baugruppeallgemein ;
  QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=1.20..1.36 rows=8 width=10) (actual time=0.040..0.053 rows=8 loops=1)
  ->  Sort  (cost=1.20..1.22 rows=8 width=10) (actual time=0.032..0.034 rows=8 loops=1)
  Sort Key: produkt, startzeit
  Sort Method: quicksort  Memory: 17kB
  ->  Seq Scan on baugruppeallgemein  (cost=0.00..1.08 rows=8 width=10) (actual time=0.007..0.009 rows=8 loops=1)
 Planning time: 0.058 ms
 Execution time: 0.085 ms
(7 rows)

Bei den geringen Datenmengen sind die Zeiten hier natürlich fast gleich, wichtiger ist der Plan. In der ersten Lösung sehe ich da ein "loops=8", dieser Schritt ist also bei 8 Datensätzen 8 mal auszuführen. Entsprechend bei 500K Datensätzen 500K mal. Auch wenn man da einen Index setzt bleiben es 500K Indexscans. Was beide Pläne gemeinsam haben ist ein Full-Table-Scan, der ist auch nicht vermeidbar. Es bleibt also bei Window-Funktion 1 Full-Table-Scan und bei der MySQL-Lösung 1 Full-Table-Scan und 500.000 Indexscans. Ich denke, das ist ein recht eindeutiger Unterschied.