SELECT via Stored Procedure

[nachti1505]

Im Thread http://www.delphipraxis.net/94737-st...erationen.html empfiehlt alzaimar:

Zitat:

Wir verwenden pro (logischer) Tabelle genau eine Stored Procedure mit einem Parameter 'Action', der angibt, ob man einfügen, löschen und verändern möchte. Nach dem 'Action'-Parameter folgen dann die ganzen Parameter für die Operation.

Das ganze habe ich auch gemacht, so dass für jede logische Tabelle bei mir nun zwei SPs bestehen. Eine zum Lesen der Tabellendaten und eine zum insert/update/delete. Speziell das Lesen macht mir nun aber Sorgen. Eine typische Lese-SP sieht ja in etwa so aus:

Code:

  for select a,
             b,
             ...
             x
  from tabelle
  into :a, 
       :b,
       ...
       :x
  do begin
    /* further action */
    suspend;
  end;

Der Klient besorgt sich seine Daten dann via:

Code:

select a,b,...,x from tabelle where a=5 and c=7;

Dieser Aufruf dauert bei einer Tabelle mit ca. 25000 zu lange. Was wahrscheinlich daran liegt, dass das WHERE in die SP müßte um besser zu arbeiten. So aber wird durch die SP erstmal die komplette Tabelle durchgeackert und dann erst das WHERE angewendet. Wie kann man nun den WHERE-Clause an die SP übergeben?

[mkinzler]

Man könnte a ubd c als Parameter übergeben oder meinst du eine variablere Bedingung?

[nachti1505]

Prinzipiell eine gute Idee... da werde ich wohl nicht drum herum kommen... hab eben mal versucht, die SP durch eine View zu ersetzen und sofort die gewohnte Performance erhalten... was gäbe es denn für ein Vorgehen für eine "variablere Bedingung"?

[mkinzler]

1.) Where-Bedingung als string übergeben und dann Abfrage dynamisch zusammenbauen
2.) Für jedes mögliches Feld einen Parameter, null wenn FEld keine Bedingung haben soll, dann in der SP

SQL-Code:

select ... where ((:<p1> is null) or ( <Feld1> = :<p1>)) and ((:<p2> is null) or ( <Feld2> = :<p2>)) ...

[nachti1505]

Möglichkeit zwei habe ich schon bedacht, Möglichkeit 1 klingt interessant... werde mir das mal anschauen.... Vielen Dank dir :-D

[mkinzler]

Zitat:

Möglichkeit zwei habe ich schon bedacht, Möglichkeit 1 klingt interessant...

Dann macht man aber u.U. die Performance-Vorteile einer SP zunichte.

[tsteinmaurer]

Das ist generell ein sehr wichtiger Unterschied zwischen Selectable Stored Procedure und View, wenn man über Performance spricht, den die Leute gerne bei CRUD Prozeduren mit Firebird vergessen. Für Leseoperationen ohne WHERE Klausel sind SP genau so geeignet, aber hebeln den Optimizer aus, wenn man eine horizontale Filterung mittels einer WHERE Klausel macht.

In der Regel werden CRUD Prozeduren ja dazu verwendet, um den Zugriff auf die eigentliche Tabelle zu "kapseln". Zum Beispiel, dass der verbundene Benutzer nicht direkt auf die Tabellen losgehen kann, sondern halt über diese Abstraktionsschicht. Ob man diese Abstraktionsschicht wirklich braucht bzw. man sich wirklich antut, muss man von Fall zu Fall entscheiden.

Ich verwende gerne VIEWS wenns ums Lesen geht und keine spezielle Logik für die Rückgabe benötigt wird. Für datenmanipulierende Operationen (CUD) dann SP. So kann man sich in der View auch jegliche Daten aus Fremdschlüsselbeziehungen holen und in dieser Datenmenge hat der Benutzer die Möglichkeit auch zu sortieren, zu filtern etc ...

CRUD waren/sind in aller Munde, aber man muss sich für den Einsatz Gedanken machen.

lg,
Thomas

[jobo]

OT
Möglichkeit 3
Ich mag es ja selbst nicht, aber wie wärs mit der Verwendung von Views für den "Select Fall"?
Oder so: Was bietet die Select Procedure, was der View nicht kann?
Wenn SP für Select natürlich vom technisch versierten Unternehmensvorstand ;) als Direktive gesetzt ist, bleibt aber wohl keine Wahl.

Die viel beschworenen Geschwindigkeitsvorteile einer SP
Eine SP, die per Design den Client zwingt, (meistens) alle Datensätze zu laden, kann nur unter sehr speziellen Umständen einen Performancevorteil haben. (Einer wäre meinetwegen, sehr wenig Datensätze, 5 z.B.)

Zitat:

Möglichkeit 1

Eine SP, die dieses Design durch dynamischen Zusammenbau der Where Clause via String Parameter abschafft, kann nur schneller werden (außer z.B. bei den erwähnten 'sehr wenig Datensätzen')
Leider nicht ohne Nebenwirkungen, die Nachteile beim Einsatz von handgeklöppelten Strings statt Parametern sind ja bekannt.

Letztlich muss man wie immer abwägen, elegante Programm/Interfaces/Code, Bequemlichkeit für den Entwickler, Performance, Sicherheit ..

P.S.:

Zitat:

Möglichkeit 2

mit "künstlich" ergänzten Parametern scheint mir nicht unkritisch, sie verlangt einen soliden Optimizer, ein sehr gutes Auge bei der Klammersetzung bzw, hinreichende Testautomatisierung zur Prüfung der Kombis der Wherebedingungen. Das gilt natürlich je stärker, desto breiter die Tabelle ist (mehr Parameter) und je häufiger dieses Verfahren überhaupt eingesetzt wird.

/OT

Edit: Da kann ich Thomas nur zustimmen.

[dataspider]

Hi,

ich halte die Verwendung von SP' s für Selects nicht in jedem Falle für sinnvoll, die Gründe (Optimizer) sind ja schon genannt.

Sind die Bedingungen klar (z.B. Statistik mit where datum between :datum_von and :datum_bis), dann ist das OK.
In diesem Fall übergibt man die Datumswerte als Parameter und der Optimizer kann den Plan ermitteln.
Ist die "where - Bedingung" dynamisch, macht es bei großen Datenmengen kaum noch Sinn.

Natürlich haben SP' s auch Vorteile:
Wenn komplexe Berechnungen nötig sind, ist eine SP oft übersichtlicher als ein View oder ein elendig verschachtelter SELECT.
Bei extrem komplexen Abfragen ist der vom Optimizer ermittelte Plan nicht immer optimal.
In einer SP dagegen wird durch die Zerlegung der komplexen Abfrage in u.U. mehrere Statements das ganze wieder einfacher, da der Optimizer dann pro Statement einen Plan ermittelt.

Frank

[EDIT]Hm... 2 neue Beiträge vor mir - heute mal ohne den Hinweis...[/EDIT]

[nachti1505]

Feedback:

Das dynamische Zusammenbauen des SELECTs in der SP funktioniert, aber ich muss dadurch dem Nutzer Lese-Zugriff auf die unterliegende Tabelle geben. In den anderen Fällen reichte ein GRANT auf die SP.

Im Grund steckt auch nicht viel Logik in dieser SP, so dass ich wohl für die SELECTs auf VIEWs ausweichen werden.


Ich danke alle Vorrednern für ihre Bemerkungen und Ideen.