SQL: Funktion in Funktion: Performance grausig

Lupus-SLE

Moin

habt ihr auch Ahnung von Datenbanken? Ich hab eine MariaDB, drei Funktionen und eine View - wobei die dritte Funktion die komplette Performance in den Keller zieht, wenn p1 = days > 7 wird...

Funktion 1 (CalcNewCMC):

DETERMINISTIC
BEGIN
  DECLARE datum_1 DATETIME;
  DECLARE current_cmc, first_usd, first_cmc DECIMAL(12,2);
  
  SET datum_1 = DATE_SUB(current_timestamp(), INTERVAL p_DAYs DAY);
  
  SELECT cmc_100 INTO current_cmc FROM book1 WHERE id = p_id LIMIT 1;
  
  SELECT usd INTO first_usd FROM book1 WHERE typ = 'b'
  AND NOT is_old
  AND datum >= datum_1
  ORDER BY datum ASC
  LIMIT 1;
  
  SELECT cmc_100 INTO first_cmc FROM book1 WHERE typ = 'b'
  AND NOT is_old
  AND datum >= datum_1
  ORDER BY datum ASC
  LIMIT 1;
  
  RETURN (
    (current_cmc * first_usd) / first_cmc
  );
END

Aufruf z. B.: CalcNewCMC(id, 7)

Funktion 2 (Calc_EMA_2):

DETERMINISTIC
BEGIN
  DECLARE ema DECIMAL(12,2) DEFAULT NULL;
  DECLARE done INT DEFAULT FALSE;
  DECLARE inval DECIMAL(12,2);
  DECLARE datum_1 DATETIME;
  DECLARE datum_2 DATETIME;
  
  DECLARE cursor_list CURSOR FOR 
      SELECT CalcNewCMC(id, p_days)
      FROM book1
      WHERE typ = 'b' AND NOT is_old
        AND datum >= datum_1
        AND datum <= datum_2
      ORDER BY datum ASC;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  SET datum_1 = DATE_SUB(current_timestamp(), INTERVAL p_days DAY);
  SELECT datum INTO datum_2 FROM book1 WHERE id = p_id LIMIT 1;
  
  OPEN cursor_list;
  loop_list: LOOP
      FETCH cursor_list INTO inval;
      IF done THEN
         LEAVE loop_list;
      END IF;
      
      IF ema IS NULL THEN
        SET ema = inval;
      ELSE
        SET ema = (alpha * inval) + ((1.0 - alpha) * ema);
      END IF;
  END LOOP loop_list;
  CLOSE cursor_list;
  
  RETURN ema;
END

Aufruf z. B.: Calc_EMA_2(id, 7, 0.15)

Funktion 3 (p1):

NO SQL
    DETERMINISTIC
return @p1

View 1 (book1_days):

SELECT *, CalcNewCMC(id, p1()) AS new_cmc, Calc_EMA_2(id, p1(), 0.05) AS cmc_ema
FROM book1
WHERE typ = 'b' AND is_old = 0
AND datum >= current_timestamp() - INTERVAL p1() DAY

Aufruf z. B.: SELECT * FROM book1_days WHERE @p1:=7;

Ich glaube, das Problem sollte ersichtlich sein... CalcNewCMC stellt für n Anfragen n*n*2 neue Anfragen, und Calc_EMA_2 ruft für eine Anfrage CalcNewCMC n-mal auf... also wären wir schon bei O(n^3) für eine Anfrage. Lässt sich das vielleicht umgehen?

Oder sollte eine DB gar nicht den gleitenden Durchschnitt über einen Durschnitt berechnen?

DocShoe

Unter Postgres gibt es so genannte Window-Funktionen, mit denen man sowas bequem berechnen kann, vllt. gibt's sowas auch für MariaDB. Wird aber wohl kein SQL-Standard sein, falls das eine Rolle spielt.

Edit:
Beispiel

Lupus-SLE

@DocShoe Ty!

Ehrlich gesagt, weiß ich gar nicht, weshalb ich für mein Haushaltsbuch MySQL / MariaDB gewählt hatte... Ich glaube, weil alle immer von MySQL schwärmen.

Würde es Sinn machen, jetzt die DB-Software zu wechseln, also Postgres zu verwenden und einen Dump einzuspielen?

Aber wäre der rekursive Aufruf nicht auch schon durch:

DETERMINISTIC
BEGIN
  DECLARE ema DECIMAL(12,2) DEFAULT NULL;
  DECLARE done INT DEFAULT FALSE;
  DECLARE inval, first_usd, first_cmc DECIMAL(12,2);
  DECLARE datum_1, datum_2 DATETIME;
  
  DECLARE cursor_list CURSOR FOR 
      SELECT cmc_100
      FROM book1
      WHERE typ = 'b' AND NOT is_old
        AND datum >= datum_1
        AND datum <= datum_2
      ORDER BY datum ASC;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  SET datum_1 = DATE_SUB(current_timestamp(), INTERVAL p_days DAY);
  SELECT datum INTO datum_2 FROM book1 WHERE id = p_id LIMIT 1;
  
  SELECT usd INTO first_usd FROM book1 WHERE typ = 'b'
  AND NOT is_old
  AND datum >= datum_1
  ORDER BY datum ASC
  LIMIT 1;
  
  SELECT cmc_100 INTO first_cmc FROM book1 WHERE typ = 'b'
  AND NOT is_old
  AND datum >= datum_1
  ORDER BY datum ASC
  LIMIT 1;
  
  OPEN cursor_list;
  loop_list: LOOP
      FETCH cursor_list INTO inval;
      IF done THEN
         LEAVE loop_list;
      END IF;
      
      inval = (inval * first_usd) / first_cmc; # New cmc ...
	  
      IF ema IS NULL THEN
        SET ema = inval;
      ELSE
        SET ema = (alpha * inval) + ((1.0 - alpha) * ema);
      END IF;
  END LOOP loop_list;
  CLOSE cursor_list;
  
  RETURN ema;
END

beseitigt?

Muss mal gerad testen, ob das auch syntaktisch geht.

Lupus-SLE

Ta ! It works... (In Zeile 39 lediglich ein SET vergessen.)

DocShoe

@Lupus-SLE
Wie ist die Laufzeit jetzt?

Lupus-SLE

@DocShoe sagte in SQL: Funktion in Funktion: Performance grausig:

@Lupus-SLE
Wie ist die Laufzeit jetzt?

Linear. Edit: Bzw., für d Tage mit insgesamt n Einträgen: n*n.