FB: Prepared Query mit In-Statement ?

[jobo]

"100 mal eine Query"

Klingt etwas nach der berühmten Whileschleife für Queries.

Häufig kann man das IN zu einem JOIN auflösen. Das hilft nicht unbedingt weiter, außer in einem speziellen Fall. Wenn nämliche die vielen IN Werte alle an einem oder wenigen "Parent" Werten einer übergeordneten Tabelle hängen.
Gegenanzeige wäre bspw. das in dem IN disjunkte Werte aus einer Suchmaske stecken.

Wenn also die Zahl 100 darauf hindeutet, das in dieser 100er Schleife eine Dimension oder Menge durchgearbeitet wird, die man auch insgesamt in eine "große" Query auflösen kann, wäre das eine mögliche Lösung, das IN fliegt dann idealer Weise raus.

Also Frage wäre: Gibt es zu den 100 Abfragen eine Master Query?
Und wenn ja, wie sieht die aus?

Handarbeitslösung:
Die IN Werte in eine (temporäre) User spezifische Tabelle eintragen und die jetzige Query dagegen joinen. Oder bei einer bekannten Maximalanzahl von IN Werten, z.B. 5, mit 5 verschiedenen Queries arbeiten und jeweils die Query starten, die dazu dieser Parameter Anzahl entspricht.
Oder den fixen Teil der Query in einen View packen und auf Parametrieung und Prepare der IN Anweisung verzichten (dann ist wenigsten der View fertig prepared).

[IBExpert]

Zitat von hoika:

Geht das irgendwie anders ??

Hängt ein wenig von der Datenmenge ab, geht aber immer auch anders (ich hab mal die params P genannt)

1. bei kleineren Datenmengen so

Code:

Select Feld3 From Tab
Where 
(Feld1=:P1) And
(:P2 containing ';'||Feld2||';' )

dabei musst du dann einfach deinen Parameter P2 als semikolon getrennte Liste mit führendem und abschliessendem
Semikolon als String übergeben, z.B. so

Code:

qry.Params[0].AsInteger := 123;
qry.Params[1].AsString := ';a;b;x;';

Das sollte klappen, ist aber von der Performance nur für kleine Datenmenge sinnvoll, weil es garantiert einen
Full Table Scan macht

2. Je nach Netzwerkspeed und Anzahl an Parametern sollte man die Vorteile von prepare nicht überschätzen
und einfach den kompletten SQL so wie du den brauchst mit konstanten im SQL Text übertragen , d.h.

Code:

Select Feld3 From Tab
Where 
(Feld1=123) And
(Feld2 in ('a','b','x') )

3. Die optimalere Lösungs ggf auch für große Datenmenge
Erstelle eine Global Temporary Table mit on commit delete rows wo nur mögliche Parameter
reinkommen, Index anlegen ggf nicht vergessen
und packe deine Parameter in die Tabelle

Code:

CREATE GLOBAL TEMPORARY TABLE BRPARRAY (
    VAL   VARCHAR(80) NOT NULL PRIMARY KEY
) ON COMMIT DELETE ROWS;
commit;
INSERT INTO BRPARRAY VALUES ('a');
INSERT INTO BRPARRAY VALUES ('b');
INSERT INTO BRPARRAY VALUES ('x');
--achtung hier kein commit, sonst ist wegen on commit delete rows die tabelle wieder leer

Select Feld3 From Tab
join brparray on brparray.val=tab.feld2 
Where 
(Feld1=123)

In der Global Temporary Table GTT siehst du immer nur die Daten deiner eigenen Connection.
Mit on commit delete rows ist die nach dem commit leer
Mit on commit preserve rows ist die auch nach dem commit noch gefüllt, dann ggf einfach
selber entscheiden wann gelöscht werden soll.


Wir nutzen GTT zB dafür, das beim Programmstart die Userrechte und Translations einmalig
ausgelesen werden und immer in den GTTs drin sind, man also bei jeder Abfrage zB PLZ
Werte entsprechend der eigenen Rechte da drin zu haben und jede Abfrage kann immer an
eine oder mehrere passende GTTs gejoint werden.

[hoika]

Hallo,
die 100 habe ich mal so hingeschrieben.
Es können auch mehr sein.
Die betreffende Query hat noch keinen Outer Join,
der macht das Umbauen in eine einzige Query noch viel langsamer.

Das Containing klingt doch schon mal gut.
Aber das kann doch nicht stimmen,
ich habe z.B. 12 und 12B in der Datenbank,
suche ich jetzt über Containg ;12;, wird auch das 12B gefunden, richtig?

Das könnte ich aber über eine nachträgliches If beim Durchlaufen der Query lösen.

Ein Full Table Scan ist es nicht, weil Field1 indiziert ist.

PS:
Der Code ist alt. Ich versuche ihn zu Optimieren,
ohne alles neu schreiben zu müssen.

[p80286]

Zitat von hoika:

Der Code ist alt. Ich versuche ihn zu Optimieren,
ohne alles neu schreiben zu müssen.

Der Versuch ist nicht strafbar, aber trotzdem hätte ich ein schlechtes Gefühl dabei. Auch mich macht der mehrfache Aufruf erst einmal stutzig. Du solltest vielleicht eine Ebene höher ansetzen und Deine Abfragen hinterfragen, statt an ihrer Syntax zu basteln.

Gruß
K-H

[hoika]

Hallo,
das Problem ist nicht die Abfrage, sondern der dahinterliegende Code.
Da habe ich einfach keine Lust, den zu ändern.

Der Mehrfachaufruf kommt halt von der prozeduralen Rangehensweise:
- ermittle Anzahl der Gruppenmitglieder
- ermittle pro Gruppenmitglied diverse Daten (for-Schleife)
je nach Art des Mitgliedes sind es andere Daten

[Delphi.Narium]

Verzichte auf das Prepare und baue das SQL jeweils zusammen.

Die Verwendung von IN im Zusammenhang mit Parametern ist eher grenzwertig, insbesondere dann, wenn die Menge der Werte innerhalb des IN von Statement zu Statement variiert.

[hoika]

Hallo,
genauso läuft es ja jetzt.
Ich habe etwa 500 variierende Mitglieder in einer Personengruppe.
Jedes Mitglied hat 1 bis x Aktivitäten, die angezeigt werden sollen.
Von den 500 Mitgliedern sind immer so 100-200 aktiv.

[jobo]

Zitat von hoika:

das Problem ist nicht die Abfrage, sondern der dahinterliegende Code.
Da habe ich einfach keine Lust, den zu ändern.

Der Mehrfachaufruf kommt halt von der prozeduralen Rangehensweise:
- ermittle Anzahl der Gruppenmitglieder
- ermittle pro Gruppenmitglied diverse Daten (for-Schleife)
je nach Art des Mitgliedes sind es andere Daten

Ich will nicht nerven, aber mein Geschwafel weiter vorne dreht sich genau darum. Die Idee ist die Iteration im Code zu entfernen bzw. zurückzubauen, Ermittlung der Anzahl und Aktivitäten der Mitglieder in einem "großen" Statement.
Die DB würde sich freuen, vielleicht sogar der Codeleser und Versteher.

SQL ist genau dafür geschaffen, Daten zu verknüpfen und zu transformieren. Für eine prozedurale Lösung- zur Datenzusammenstellung wohlgemerkt- gibt es nur selten Argumente.
Eine Fall basierte Business Logik, die anhand der Daten dann unterschiedliche Aktivitäten(! nicht Selektionen) startet, ist im Client gut aufgehoben. (Geht auch anders, ist aber hier nicht das Thema)
Guten Rutsch!

[IBExpert]

Zitat von hoika:

ich habe z.B. 12 und 12B in der Datenbank,
suche ich jetzt über Containg ;12;, wird auch das 12B gefunden, richtig?

nein, wenn sich das auf meine Lösung bezieht, weil um den wert aus der Tabelle, mit dem
im parameter gesucht wird, ja immer ein Semikolon davor und dahinter dynamisch ergänzt ist

';12B;13;13A;' containing ';12;' ist false

du musst deinen parameter sauber mit Trenner zusammenbauen

[hoika]

Hallo,
Jobs
ja, will ich aber nicht

Holger
dann verstehe ich das Containing nicht
Ah, auch das Select anpassen!