SQL: UPDATE Verständnisproblem

[Hans-Georg Normann]

Ich schlage mich gerade dmait herum eine Delphiapplikation von BDE auf Firebird umzustellen, was weitestgehend klappt. Jedoch gibt es beim Befüllen und Kalkulieren der Datenbanktabellen erhebliche Performance Probleme.


extreme Ladezeiten, bedingt durch enthaltene Unique Keys. Das habe ich so weit in den Griff bekommen.
den Datenaustausch zwischen Tabellen und Abfragen. An dem Problem habe ich mir ergebnislos die Zähne ausgebissen.


Ich habe folgende Abfrage:
SELECT
PRODUCT_NO,
PAIL_NO,
SUM(QUANTITY) QUANTITY,
SUM(QUANTITY_IN) QUANTITY_IN,
SUM(QUANTITY_OUT) QUANTITY_OUT
FROM LJOUR
WHERE LJOUR_DATE BETWEEN '01.11.2002' AND '31.10.2003'
GROUP BY PRODUCT_NO, PAIL_NO
ORDER BY PRODUCT_NO, PAIL_NO Versteht sich von selbst, dass die beiden Datumsangaben variabel sind. Als Ergebnis erhält man einige tausen Records.

Parallel dazu gibt es eine bereits befüllte Tabelle, welche auch die in der SQL Ausgabe genannten gleichnamigen Felder hat. Soweit zur Einführung.

Kann ich die Summen der Abfrage irgendwie per SQL (UPDATE) an die Tabelle übertragen oder geht das ausschließlich konvetionell, d.h. Record für Record in Delphi?

Bin mal auf Lösungsvorschläge gespannt.

Hans

[Christoph]

Hm, Du scheibst zu versuchen einen View mit schlechten Mitteln zu simulieren. Schau mal in die Doku deiner Datenbank unter CREATE VIEW.

[Hans-Georg Normann]

Ja, wenn das mit nem View ginge, hätte ich ja nicht gefragt. Aber wie bekomme ich die Zeile
LJOUR_DATE BETWEEN '01.11.2002' AND '31.10.2003' so in den View eingebaut, dass der Datumsbereich flexibel ist?

Hans

[Christoph]

Was bedeutet flexibel? Immer vom letzten Ersten ein Jahr zurück? Kein Problem: von heute ganau ein Jahr zurück kannst Du zum Beispiel wie folgt spezifizieren (SQL2 kompatible Version (beachte die kuriose CAST-Syntax und das Fehlen von Klammern nach current_date), getestet mit PostgreSQL):


between
cast((current_date - cast('1 year' as interval) + cast('1 day' as interval)) as date)
and
current_date

Das wird im View dann je nach Datum der Abfrage ausgewertet.

[Hans-Georg Normann]

Hi Cristoph

So flexibel ist das Datum leider auch nicht. Es geht um eine Inveturauswertung, welche zum Stichtag aufgebaut wird. Aber das bringt mich auf die Idee, die Abgrenzungsdaten in eine eigene Tabelle zu schreiben, etwa in der Form:
DATUM1_VON=01.01.1900
DATUM1_BIS=31.10.2002
DATUM2_VON=01.11.2002
DATUM2_BIS=31.10.2003
Darauf basierend müsste ich doch zwei Views aufbauen können, und die Daten dann mit reinem SQL hin und her schieben können. Sobald ich meine Umzugskartons gepackt habe, werde ich mir das nocheinmal ansehen.

Hans

[Christoph]

Statt mehrer Felder je Jahr wäre es wohl naheliegender folgende Struktur zu wählen:


create table inventurzeitraum (
jahr char(4) primary key,
beginn date,
ende date
);
Wenn die View-Abfrage sehr komplex ist, kannst Du sie auch in eine stored procedure packen (da gehen dann bedingte Verzweigungen und Schleuifen).

[Hans-Georg Normann]

Dieser View bringt ein richtiges Ergebnis
CREATE VIEW INV_CALC_SLOW_MOVING_C1 (
COMPANY_NO,
STOCK_NO,
GERMAN_PRODUCT_NO,
PAIL_NO,
S_QUANTITY,
S_INCOMING_QUANTITY,
S_OUTGOING_QUANTITY,
S_QUANTITY_UNEXPECT,
QUANTITY,
INCOMING_QUANTITY,
OUTGOING_QUANTITY,
UNEXPECT_MOVING_QUANTITY
)
AS SELECT
L.COMPANY_NO,
L.STOCK_NO,
L.GERMAN_PRODUCT_NO,
L.PAIL_NO,
S.QUANTITY S_QUANTITY,
S.QUANTITY_IN S_INCOMING_QUANTITY,
S.QUANTITY_OUT S_OUTGOING_QUANTITY,
S.QUANTITY_UNEXPECT S_QUANTITY_UNEXPECT,
SUM(L.QUANTITY) QUANTITY,
SUM(L.INCOMING_QUANTITY) INCOMING_QUANTITY,
SUM(L.OUTGOING_QUANTITY) OUTGOING_QUANTITY,
SUM(L.UNEXPECT_MOVING_QUANTITY) UNEXPECT_MOVING_QUANTITY
FROM INV_LJOUR L LEFT OUTER JOIN INVENTORY_STOCK S ON (
L.COMPANY_NO = 1
AND L.STOCK_NO = S.STOCK_NO
AND L.GERMAN_PRODUCT_NO = S.GERMAN_PRODUCT_NO
AND L.PAIL_NO = S.PAIL_NO
), INV_DATE_RANGES R
WHERE L.MOVING_DATE BETWEEN R.INV_DATE_C1_FROM AND R.INV_DATE_C1_TO
GROUP BY L.COMPANY_NO, L.STOCK_NO, L.GERMAN_PRODUCT_NO, L.PAIL_NO,
S.QUANTITY, S.QUANTITY_IN,S.QUANTITY_OUT, QUANTITY_UNEXPECT Versuche ich jedoch mit der Update Funktion einen Wert QUANTITY an S_QUANTITY zu übertragen, dann bekomme ich eine Fehlermeldun (.... not perimittetd... read only view). Offensichtlich bewirkt das SUM(QUANTITY), dass die View read only wird.

So langsam bin ich mit meinem Latein am Ende. Bleibt jetzt nur noch eine Stored Procedure? Habe ich noch nie mit gearbeitet und die Handbücher schaffen da auch (noch) nicht die notwendige Klarheit. Wäre nett, wenn mir da jemand unter die Arme greifen könnte. Mir würde es ja ausreichen, wenn ich ein Beispiel für QUANTITY / S_QUANTITY bekommen könnte. Der Rest sollte dann Fromsache sein.

Hans

[Christoph]

Versuche ich jedoch mit der Update Funktion einen Wert QUANTITY an S_QUANTITY zu übertragen, dann bekomme ich eine Fehlermeldun (.... not perimittetd... read only view). Offensichtlich bewirkt das SUM(QUANTITY), dass die View read only wird.

Jetz bin ich aber auch mit meinem Latein am Ende: warum bitte willst Du denn den View updaten und nicht die Originaltabellen???

Im allgemeinen sind Views nicht updatebar (Ausnahmen siehe http://lionel.kr.hs-niederrhein.de/~dalitz/data/lehre/DBS/Kap3.1-3-4up.pdf Seite 6-7).

[Hans-Georg Normann]

Hi Cristoph, ich will nicht unbedingt die View updaten. Ich will eigentlich nur vernünftige Zahlena aus dem Systen herausbekommen ;)

OK, habe mich jetzt einmal inmitten der Umzugskartons noch fünf Stunden mit Stored Procedures beschäftigt und bin dahintergestiegen wie es geht. Werde Morgen oder Übermorgen mal das Ergebnis posten. Ist eigentlich ganz einfach :D

Dann kann ich meine Views auch wieder löschen. Geht völlig ohne. Parameterübergabe von Delphi macht mir noch Probleme, d. h. ich habe in der Richtung noch nichts probiert. Mit Stored Procedure habe ich die Verarbeitungszeit von über fünf Minuten auf ca. 5 Sek. gedrückt. Der Aufwand steht zwar in keinem Verhältnis, aber ich glaube Stored Procedures könnten bei mir Standard werden.

Hans

[Christoph]

Die Lösung würde mich interessieren. Welche Sprache verwendet Firebird denn für Stored Procedures? Auch PL/SQL wie Orcale und Postgres (die Postgres Implementierung ist ziemlich genial: die Sprache ist austauschbar!) oder was eigenes?

[Hans-Georg Normann]

Hi Cristoph, hier meine erste Procedure. Diese arbeitet rein intern, d. h . es erolgt keine Bildschirmausgabe.
CREATE PROCEDURE SP_INVENTORY_CALC_SLOW_STOCK
AS
DECLARE VARIABLE STOCK_NO INTEGER;
DECLARE VARIABLE GERMAN_PRODUCT_NO VARCHAR(13) CHARACTER SET ISO8859_1;
DECLARE VARIABLE PAIL_NO VARCHAR(3) CHARACTER SET ISO8859_1;
DECLARE VARIABLE QUANTITY FLOAT;
DECLARE VARIABLE INCOMING_QUANTITY FLOAT;
DECLARE VARIABLE OUTGOING_QUANTITY FLOAT;
DECLARE VARIABLE UNEXPECT_MOVING_QUANTITY FLOAT;
DECLARE VARIABLE QUANITY_STOCK FLOAT;
begin
/* clear all entries */
UPDATE INVENTORY_STOCK
SET
QUANTITY = 0.0,
QUANTITY_IN = 0,
QUANTITY_OUT = 0,
QUANTITY_UNEXPECT = 0,
QUANTITY_IN1 = 0,
QUANTITY_OUT1 = 0,
QUANTITY_UNEXPECT1 = 0;
/* slow moving stock class1
* (older than 12 month)
* calculate incoming, outgoing and unexpected quantities
* from stock journal
* incoming purchased, production
* outgoing sales
* unexpected others
*/
FOR SELECT
L.STOCK_NO,
L.GERMAN_PRODUCT_NO,
L.PAIL_NO,
SUM(L.QUANTITY) QUANTITY,
SUM(L.INCOMING_QUANTITY) INCOMING_QUANTITY,
SUM(L.OUTGOING_QUANTITY) OUTGOING_QUANTITY,
SUM(L.UNEXPECT_MOVING_QUANTITY) UNEXPECT_MOVING_QUANTITY
FROM INV_LJOUR L, INV_DATE_RANGES R
WHERE L.MOVING_DATE BETWEEN R.INV_DATE_C1_FROM AND R.INV_DATE_C1_TO
AND L.COMPANY_NO = 1
GROUP BY L.STOCK_NO, L.GERMAN_PRODUCT_NO, L.PAIL_NO
INTO
:STOCK_NO,
:GERMAN_PRODUCT_NO,
:PAIL_NO,
:QUANTITY,
:INCOMING_QUANTITY,
:OUTGOING_QUANTITY,
:UNEXPECT_MOVING_QUANTITY
DO BEGIN
UPDATE INVENTORY_STOCK INV
SET
INV.QUANTITY = :QUANTITY,
INV.QUANTITY_IN = :INCOMING_QUANTITY,
INV.QUANTITY_OUT = :OUTGOING_QUANTITY,
INV.QUANTITY_UNEXPECT = :UNEXPECT_MOVING_QUANTITY
WHERE
INV.STOCK_NO = :STOCK_NO
AND INV.GERMAN_PRODUCT_NO = :GERMAN_PRODUCT_NO
AND INV.PAIL_NO = :PAIL_NO;
END

suspend;
end
Der Knackpunkt war die Variablendekalration am Anfang und der Part INTO. Damit gibst du an, welche Spalte in welcher Variable übergeben werden soll. Und das Ganze ist im Vergleich zur "tarditonellen" von einer externen Applikation wo Record für Record upgedatet wird sauschnell. :cool:

Ich hoffe, du kommst damit klar.

Hans

[Jasper]

unter oracle mach ich sowas mit merge. merge ist ansi sql99 standard, könnte also auch in anderen rdbms implementiert sein.


-j

[Hans-Georg Normann]

Danke für den Hinweis, aber ich glaube das steht unter Firebird 1.5 nicht zur verfügung.

Hans