Hallo zusammen,
ich hab mir gerade ein Performance-Problem gebaut. meine Funktion soll eine Tabelle mit Zeitpunkten und dazugehörigen Werten zurück liefern. Die Funktion bekommt als einen Parameter die gewünschte zeitliche Auflösung übergeben, was bis zur Stundenebene kein Problem ist, da diese Werte in einer zusätzliche Tabelle abgelegt werden. Problematisch wird es bei der online Aggregierung zu Tagen, Wochen oder Monaten.
Meine “Lösung” bis jetzt ist eine Funktion, die mir jeweils die Wechsel zwischen den Zeiträumen (z.B. Tagen) liefert, und dann mit for timestamp aus dieser Funktion mein SQL-Statement als storded-function mit Start- und endzeitpunkt aufzurufen.
etwas zusammengekürzt sieht meine Funktion so aus:
CREATE OR REPLACE FUNCTION valueitem_result(itemid integer, unit integer, select_type integer, starttime timestamp without time zone, endtime timestamp without time zone, windows_timezone text)
RETURNS table ("Mittelwert" double precision, "TimeStamp" timestamp without time zone) AS
$BODY$
declare
sql text;
result record;
select_string text;
replace_text text;
timespan text;
selected_timestamp timestamp without time zone;
first_timestamp timestamp without time zone;
last_timestamp timestamp without time zone;
function_name text;
begin
if unit > 4 then
sql := 'SELECT kennzahlsql from get_valueitem_sqlstring('||itemid||',4,'||select_type||');';
execute sql into result;
sql := result.kennzahlsql;
sql := replace(sql, ':from', '$1');
sql := replace(sql, ':to', '$2');
function_name := 'body_'||itemid;
sql:='prepare '||function_name||' (timestamp without time zone,timestamp without time zone) as '||sql;
execute sql;
sql := 'SELECT * FROM create_day_series('||unit||','||quote_literal(starttime)||','||quote_literal(endtime)||','||quote_literal(windows_timezone)||');';
for selected_timestamp in execute sql LOOP
last_timestamp := selected_timestamp;
if first_timestamp is null Or first_timestamp = last_timestamp then
--erster oder letzter Wert der Serien
else
sql:='execute '||function_name||'('||quote_literal(first_timestamp)||','||quote_literal(last_timestamp)||')';
return query execute sql;
end if;
first_timestamp := selected_timestamp;
end loop;
sql:='deallocate '||function_name||';';
Execute sql;
return ;
else
sql := 'SELECT * from get_valueitem_sqlstring('||itemid||','||unit||','||select_type||');';
execute sql into result;
select_string := result.kennzahlsql;
replace_text := quote_literal(starttime);
select_string := replace(select_string, ':from', replace_text);
replace_text := quote_literal(endtime);
select_string := replace(select_string, ':to', replace_text);
return query execute select_string;
end if;
end;
$BODY$
LANGUAGE plpgsql VOLATILE;
Das Ergebnis von create_day_series ist eine Liste von timestamp without timzone
(… ich weiß, keine gute Idee, aber da kann ich jetzt nichts dran ändern, da alle Zeitstempel in der Datenbank ohne Zeitzone gespeichert werden, aber sie werden explizit als UTC gespeichert)
Das Ergebnis von get_valueitem_sqlstring ist ein select, das zum Rückgabetyp der Funktion passt, hier mal zwei Beispiele:
SELECT sum ( "Item"."Mittelwert" ) AS "Mittelwert", LAST("Item"."TimeStamp") AS "TimeStamp" FROM ( SELECT Distinct cast ( "AGG_Hour_SID_400073"."Mittelwert"-lag("AGG_Hour_SID_400073"."Mittelwert") OVER ( ORDER BY "AGG_Hour_SID_400073"."TimeStamp") AS DOUBLE PRECISION) AS "Mittelwert", ("AGG_Hour_SID_400073"."TimeStamp")::TIMESTAMP WITHOUT TIME ZONE AS "TimeStamp" FROM "AGG_Hour_SID_400073" WHERE ("AGG_Hour_SID_400073"."TimeStamp" >= :from AND "AGG_Hour_SID_400073"."TimeStamp" <= :to) ORDER BY "TimeStamp" ) AS "Item";
SELECT sum ( "Item"."Mittelwert" ) AS "Mittelwert", LAST("Item"."TimeStamp") AS "TimeStamp" FROM ( SELECT Distinct cast ( ("AGG_Hour_SID_10067"."Mittelwert" - "AGG_Hour_SID_10066"."Mittelwert") AS DOUBLE PRECISION) AS "Mittelwert", ("AGG_Hour_SID_10067"."TimeStamp")::TIMESTAMP WITHOUT TIME ZONE AS "TimeStamp" FROM "AGG_Hour_SID_10067", "AGG_Hour_SID_10066" WHERE ("AGG_Hour_SID_10067"."TimeStamp" > :from AND "AGG_Hour_SID_10067"."TimeStamp" <= :to AND "AGG_Hour_SID_10067"."TimeStamp" = "AGG_Hour_SID_10066"."TimeStamp") ORDER BY "TimeStamp" ) AS "Item";
jetzt zu der Frage: kann man den Loop mit “for selected_timestamp in execute sql LOOP” und seinem "return query execute sql;
" irgendwie geschickter bauen, so dass das nicht so extrem lange dauert?
vielen Dank schon mal für die mühen
Marco