Hallo an Alle,
ich bin neu in Benutzung von PostgreSQL und muss jetzt ein Fehler in einer fertigen Funktion suchen, den ich nicht verstehe.
An eine Funktion werden mehrere Parameter übergeben, die in der SQL-Abfrage benutzt wird, um eine Datenauswertung für einen Zeitraum als Tabelle zurüclzugeben.
Wenn ich die Funktion ausführe und den Zeitraum von 6 Monaten angebe, dauert die Abfrage ca. 4 Sekunden. Sobald ich an einer Stelle (fett markiert im Code) die Variablen durch feste Werte (z.B.
'20100404': :D ATE und '20101004': :D ATE
) ersetze, geht es nur in ca. 200 ms.
Was könnte das sein und wieso funktioniert es mit Variablen langsamer als mit den festen Werten, wo ich an der Stelle eigentlich kein Unterschied sehe?
-- Function: trainingsergebnis_geraet_view_function(nmandant_id integer, nkunden_id integer, nclass_id integer, ntype integer, nmachine_id integer, cerstellt_am_datumvon bpchar, cerstellt_am_datumbis bpchar)
-- DROP FUNCTION trainingsergebnis_geraet_view_function(nmandant_id integer, nkunden_id integer, nclass_id integer, ntype integer, nmachine_id integer, cerstellt_am_datumvon bpchar, cerstellt_am_datumbis bpchar);
CREATE OR REPLACE FUNCTION trainingsergebnis_geraet_view_function(nmandant_id integer, nkunden_id integer, nclass_id integer, ntype integer, nmachine_id integer, cerstellt_am_datumvon bpchar, cerstellt_am_datumbis bpchar)
RETURNS SETOF record AS
$BODY$
DECLARE
oReturn record;
nMd_Id integer;
nKd_id integer;
nTp integer;
nMn_id integer;
dErstDatVon DATE;
dErstDatBis DATE;
BEGIN
nMd_Id := $1;
nKd_id := $2;
nTp := $4;
nMn_id := $5;
dErstDatVon := $6: :D ATE;
dErstDatBis := $7: :D ATE;
FOR oReturn IN
Select erstellt_am_datum,
machine_id,
class_id,
mandant_id,
kunden_id,
bereichs_id,
bereichs_name,
class_name,
geraete_name,
nutzung,
"template",
template_name,
CASE
when typ = 1 then
CASE
-- // (kJ * 1000) = J / (min * 60) = sek. ... (J / sek.) = Watt
when class_id = 0 or class_id = 1 or class_id = 2 or class_id = 5 or class_id = 6 or class_id = 8 then summe
-- // (m / 1000) = km / (min / 60) = h ... (km / h) = km/h
when class_id = 3 then summe
END
when typ = 2 then summe
END AS summe,
typ,
"template2",
template_name2,
CASE
when typ = 1 then
CASE
when class_id = 0 or class_id = 1 or class_id = 2 or class_id = 5 or class_id = 6 or class_id = 8 then summe2::integer
when class_id = 3 then summe2::integer
END
when typ = 2 then summe2
END AS summe2,
CASE
when typ = 1 then
CASE
when class_id = 0 or class_id = 1 or class_id = 2 or class_id = 5 or class_id = 6 or class_id = 8 then (((summe2/5)*1000) / (summe*60))::numeric(8,2)
when class_id = 3 then ((summe3/1000) / (summe/60))::numeric(8,2)
END
when typ = 2 then (summe2 / summe)::numeric(8,2)
END AS durchschnitt,
"template3",
template_name3,
summe3 AS summe3
From (
SELECT tpe.erstellt_am_datum,
tpe.machine_id,
tpe.class_id,
tpe.mandant_id,
tpe.kunden_id,
tpe.bereichs_id,
name_bereich(tpe.bereichs_id) AS bereichs_name,
name_class(tpe.class_id) AS class_name,
name_geraet(tpe.machine_id) AS geraete_name,
Max(tpe.nutzung) AS nutzung,
tpe.template_name AS "template",
name_template(tpe.template_name) AS template_name,
sum(get_geraete_zaehler(tpe.class_id, tpe.machine_id, tpe.template_name, tpe.summe))::numeric AS summe,
mc."type" AS typ
FROM trainingsergebnis tpe
LEFT JOIN machineclasses mc ON mc.class_id = tpe.class_id
where
tpe.mandant_id = nMd_Id and --1 and
tpe.kunden_id = nKd_id and --5 and
((mc.type = 1 and tpe.template_name = 'ZZ') or (mc.type = 2 and tpe.template_name = 'HZ'))
and (nMn_id = 0 and type = nTp and tpe.class_id <> 3 or tpe.machine_id = nMn_id)
and erstellt_am_datum between [B][size=134]dErstDatVon[/size][/B] and [B][size=134]dErstDatBis[/size][/B]
GROUP BY tpe.erstellt_am_datum,
tpe.machine_id,
tpe.template_name,
tpe.class_id,
tpe.mandant_id,
tpe.kunden_id,
tpe.bereichs_id,
mc."type"
) AS foo
Join
(SELECT tpe2.erstellt_am_datum AS erstellt_am_datum2,
tpe2.template_name AS "template2",
tpe2.machine_id AS machine_id2,
tpe2.class_id AS class_id2,
name_template(tpe2.template_name) AS template_name2,
sum(get_geraete_zaehler(tpe2.class_id, tpe2.machine_id, tpe2.template_name, tpe2.summe))::numeric AS summe2
FROM trainingsergebnis tpe2
LEFT JOIN machineclasses mc ON mc.class_id = tpe2.class_id
where
tpe2.mandant_id = nMd_Id and --1 and
tpe2.kunden_id = nKd_id and --5 and
((mc.type = 1 and tpe2.template_name = 'AZ') or (mc.type = 2 and tpe2.template_name = 'GZ'))
and (nMn_id = 0 and type = nTp and tpe2.class_id <> 3 or tpe2.machine_id = nMn_id) and erstellt_am_datum between dErstDatVon and dErstDatBis
GROUP BY tpe2.erstellt_am_datum,
tpe2.machine_id,
tpe2.template_name,
tpe2.class_id,
mc."type"
) AS foo2
on (foo.erstellt_am_datum = foo2.erstellt_am_datum2 and foo.machine_id = foo2.machine_id2 and foo.class_id = foo2.class_id2)
left Join
(SELECT tpe3.erstellt_am_datum AS erstellt_am_datum3,
tpe3.template_name AS "template3",
tpe3.machine_id AS machine_id3,
tpe3.class_id AS class_id3,
name_template(tpe3.template_name) AS template_name3,
sum(get_geraete_zaehler(tpe3.class_id, tpe3.machine_id, tpe3.template_name, tpe3.summe))::numeric AS summe3
FROM trainingsergebnis tpe3
LEFT JOIN machineclasses mc ON mc.class_id = tpe3.class_id
where
tpe3.mandant_id = nMd_Id and
tpe3.kunden_id = nKd_id and
((mc.type = 1 and tpe3.template_name = 'SZ') or (mc.type = 2 and tpe3.template_name = 'WZ'))
and (nMn_id = 0 and type = nTp and tpe3.class_id <> 3 or tpe3.machine_id = nMn_id) and erstellt_am_datum between dErstDatVon and dErstDatBis
GROUP BY tpe3.erstellt_am_datum,
tpe3.machine_id,
tpe3.template_name,
tpe3.class_id,
tpe3.mandant_id,
tpe3.kunden_id,
mc."type"
) AS foo3
on (foo.erstellt_am_datum = foo3.erstellt_am_datum3 and foo.machine_id = foo3.machine_id3 and foo.class_id = foo3.class_id3)
order by machine_id
LOOP
RETURN NEXT oReturn;
END LOOP;
RETURN ;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION trainingsergebnis_geraet_view_function(nmandant_id integer, nkunden_id integer, nclass_id integer, ntype integer, nmachine_id integer, cerstellt_am_datumvon bpchar, cerstellt_am_datumbis bpchar) OWNER TO "xxxxx";
Im Voraus vielen Dank,
Andreas