Mehrere Spalten mit einem Schlüssel, anhand von gleichnamigen Spalten filtern

F

Flooo

Benutzer
Beiträge
12
Hallöchen,

leider habe ich massive Probleme mit einer MSSQL-Abfrage.
Ich habe beispielsweise diese Werte

1685540646062.png
diese Tabelle heist "#rights_table"


Will nun anhand der variablen (zur Erklärung: ist ein Token welches dann mehrere Variablen mit unterschiedlichen Inhalt haben kann) und deren Inhalt filtern.

1685540694414.png

u = und, o = oder
bisher habe ich soetwas in der art herausgefunden, dies Funktioniert ist aber schwierig Programmiertechnisch umzusetzen auf unterschiedliche Filterkriterien da ich für jede "und verknüpfung" ein weiteres Innerjoin benötige und diese Abfragen sehr Tief werden können.


SELECT distinct a.tokenid FROM (
(
SELECT * FROM #rights_table
WHERE
(
variable = 'Abteilung' AND data = 'GL' OR
variable = 'Abteilung' AND data = 'Lager'
)
) a

inner JOIN
(
SELECT * FROM #rights_table
where
(
(variable = 'Vertraulichkeit' AND data = 'Vertraulich') OR
(variable = 'Vertraulichkeit' AND data = 'offen') OR
(
(variable = 'Schutzgrad' AND data = '1') OR
(variable = 'Schutzgrad' AND data = '2')
)


)
) b
ON a.tokenid =b.tokenid
)


Gibt es eine Möglichkeit diese Zeilen mit "variable" und "data" irgendwie in eine einzige Zeile zu schreiben sodass ich einfach Filtern kann?


Ich brauche unbedingt Hilfe, vielen Dank!

Liebe Grüße
Flo
 
Werbung:
Das Prinzip: Entity–attribute–value model - Wikipedia

Der einfachste und gewöhnliche Weg zum Ziel sind viele Joins (wobei sich das hier bis auf einen Join runter brechen lässt). Das ist zwar realtiv viel Code aber am Ende meist gar nicht mal langsam denn Joins ist was ein DBMS gut kann und die Tabelle ist genau dafür angelegt, dementsprechend auch Indexe etc. Alle anderen Wege die ich mir jetzt mit meinen Buntstiften malen könnte hätte ich selbst nie getestet und wären sehr expirimentell und vermutlich nicht schneller sondern nur nerdiger - also lasse ich das gleich.

Dein Code selbst ist allerdings etwas - gruselig. Ich glaube das lässt sich durchaus eleganter und kürzer ausdrücken:
Code: 
SELECT DISTINCT t1.tokenid
FROM #rights_table t1
INNER JOIN #rights_table t2
ON t1.tokenid = t2.tokenid
AND ( t2.variable = 'Vertraulichkeit'
AND t2.data IN ( 'Vertraulich','offen' )
OR t2.variable = 'Schutzgrad'
AND t2.data IN ( '1','2' ) )
WHERE t1.variable = 'Abteilung'
AND t1.data IN ( 'GL','Lager' )
Anmerkung: Meist gibt es eine Haupttabelle mit den wichtigsten Spalten auf die man joint, wenn aus der Haupttabelle keine Atribute gebraucht werden geht es natürlich auch nur mit #rights_table.
 
PS: Das DISTINCT ändert vermutlich nichts an der Ausgabe, es sei denn es ist z.B. Vertraulichkeit und Schutzgrad gleichzeitig definiert.
 
Hatten wir neulich noch, ein JOIN definiert (meist) die Keys, das WHERE definiert die Filterkriterien.
Die Filterkriterien können nach belieben mit geodert oder geundet werden.

("Notfalls" kann man den Filter in den Join verschieben, muss hier glaub ich nicht sein, oder?)
 
Man kann die Filter-Kriterien auch komplett im WHERE-Teil unterbringen, ist vom Ergebnis her das Selbe:
Code: 
SELECT DISTINCT t1.tokenid
FROM #rights_table t1
INNER JOIN #rights_table t2
ON t1.tokenid = t2.tokenid
WHERE t1.variable = 'Abteilung'
AND t1.data IN ( 'GL','Lager' )
AND ( t2.variable = 'Vertraulichkeit'
AND t2.data IN ( 'Vertraulich','offen' )
OR t2.variable = 'Schutzgrad'
AND t2.data IN ( '1','2' ) )
Aber EAV wird schnell sehr groß und ich sagte ja es kommt manchmal auch zu vielen Joins da ist jedes Bisschen Geschwindigkeit nützlich.
 
ukulele schrieb:
Aber EAV wird schnell sehr groß und ich sagte ja es kommt manchmal auch zu vielen Joins da ist jedes Bisschen Geschwindigkeit nützlich.
Zum Vergrößern anklicken....
Verstehe nicht ganz, was das miteinander zu tun haben soll. Für mich ist das ein reines Optimizer Thema. MSSQL kenne ich nicht, aber der Engine ist es im Idealfall egal, ob ich JOIN schreibe oder WHERE. Natürlich kann ich mit dem einen nicht das gleiche abbilden wie mit dem anderen, aber ein Geschwindigkeitsproblem sehe ich da prinzipiell nicht. Eine Optimierung würde ich nur nach Bedarf vornehmen und Joins / Filter zunächst gemäß eigentlichem Zweck einsetzen.
 
Guten Morgen, vielen dank für die schnellen und vorallem ausführlichen Antworten!

@dabadepdu leider habe ich feststellen müssen, das eses ohne joins nicht funktioniert

das kommt daher wenn ich diese Tabelle habe, und ein where bauen möchte mit einer "und" verknüpfung, funktioniert dies nicht.
das einfache "where" bezieht sich immer nur auf eine Zeile des Ergebnisses, erst durch joins können mehrere Zeilen mit unterschiedlichen feldern ("variable") gefiltert werden


@ukulele okay, ich werde mal probieren einen geeignetes Programm zu schreiben. Bei bedarf poste ich auch gerne den Quelltext (C#). Leider muss ich für jede UND verknüpfung einen eigenen join bauen, da komme ich aber nicht drum rum.
Aber diese ergänzung finde ich sehr hilfreich, damit lassen sich ODERs sehr einfach programmieren "AND t1.data IN ( 'GL','Lager' )"


Vielen Lieben Dank
Für Verbesserungen und andere Vorschläge bin ich natürlich noch offen und freue mich über jede Antwort
 
Okay kurz nachgeschaut @dabadepdu hat recht, der Optimizer überführt die Filter-Kriterien aus dem WHERE-Teil in die Join Condition. Die Performance ist gleich.

In meinem Code mache ich das einfach tatsächlich immer so das ich die Join-Condition erweitere um das Ergebnis des Joins möglichst "schlank" zu halten (so mein Gedanke), damit nehme ich die Verlagerung durch den Optimizer quasi voraus. Das habe ich mir einfach so angewöhnt, häufig habe ich dann gar keinen WHERE-Teil mehr, es ist aber reine Geschmackssache.

Fun-Fact: Ich habe neulich noch interessante Artikel zu einem gar nicht mal so unähnlichen Thema gelesen: LEFT JOIN + WHERE left_table IS NULL vs. WHERE NOT EXISTS + Subquery

Why Not Exists Makes More Sense Than Left Joins For Performance – Darling Data

erikdarlingdata.com erikdarlingdata.com

@Flooo :
Verstehe ich jetzt noch nicht ganz, die entwickelst das Programm selber oder du hast Vorgaben durch ein bestehendes Programm? Warum musst du Joinen und kannst nicht AND verwenden?
 
ukulele schrieb:
der Optimizer überführt die Filter-Kriterien aus dem WHERE-Teil in die Join Condition. Die Performance ist gleich.
Zum Vergrößern anklicken....
Ich denke, das ist ein Idealzustand. In der Realität spielen viele Aspekte bis hin zu existierenden (und aktuellen) Statistiken usw. mit rein.

Der Aufbau von SQL Statements sollte Logik und Lesbarkeit und ein paar anderen Grundprinzipien folgen. Dazu gehört auch die vorgesehene Anwendung von Join und Where. Ein "ordentliches" SQL Statement zeigt im Join die Join Kriterien, also normalerweise die Schlüsselfelder.
Das Where filtert diese theoretisch große Menge auf die gewünschten Datensätze.
Optimalerweise sind das keine großen Mengen (hier unterscheidet sich ein DWH Abfrage o.ä. natürlich von einem OLTP), im Fall eines Berechtigungstokens (eines, das bei einer Session abgefragt wird), kommen sehr überschaubar wenig Datensätze raus, jenachdem vielleicht 2 bis 3 stellig. Wenn das richtig (auf dem Token) indiziert ist, sollte das blitzschnell gehen, auch wenn es tausende User und hunderte, aktive Sessions gibt.
(Jetzt wo ich das schreibe, frag ich mich gerade, ob man das überhaupt auf Tokenebene macht- zumindest wenn man Token mit einer relativ kurzen Lebensdauer hat, anderes Thema, kommt drauf an, was genau hier ein Token ist)

Erst wenn ein "ordentliches" Statement lahmt, sollten Optimierungen vorgenommen werden. Ggf bis hin zu einem Umbau, wie @kretschmer vorgeschlagen hat. Der Erfahrungswert ist, dass ein Optimizer nicht immer alles gut/richtig, "optimal" macht, dazu zähle ich auch mal die identische Behandlung von Join und Where (ganz zu schweigen von den Unterschieden bei den Herstellern und Versionen). Aber erst dann optimieren, wenn es hängt. Sowas spricht aber auch nicht unbedingt gegen ein regelmäßiges (erfahrungsbasiertes) Formulieren von SQL. Im Zweifel reiben sich die Kollegen die Augen, wenn sie eigenwillige SQL Statements in die Finger bekommen.
 
Ich stimme dir nicht zu. Allgemein finde ich es Geschmackssache, grade in diesem speziellen Fall mit EAV ist der Name, Typ oder wie hier der Variablenname logisch betrachtet sehr wohl ein Join-Kriterium, er ersetzt quasi den Spaltennamen. Ich joine doch nicht erst eine fantastiliarden Variablen auf meine Basistabelle um dann zu Filtern was ich wirklich brauche. Zudem gibt es hier nicht mal eine Basistabelle, eigentlich Joine ich zwei Attribute mit einander. Ich joine doch nicht erst alle Attribute mit einander um dann 99,9% wieder zu filtern. Rein logisch betrachtet will ich nicht mehr joinen als nötig, auch nicht in der Anzahl der Datensätze.
 
ukulele schrieb:
Ich joine doch nicht erst eine fantastiliarden Variablen auf meine Basistabelle um dann zu Filtern was ich wirklich brauche
Zum Vergrößern anklicken....
Doch, ja, nein. Vielleicht reden wir aneinander vorbei.
Wie gesagt, vollkommen egal ob gejoint oder gefiltert. Der Optimizer schaut nach hilfreichen Indizes.
Ich empfehle die Standardverwendung von Joins und Filtern in der Formulierung eines SQL. Erst wenn dabei Blödsinn passiert (Optimizer Fails), kann/sollte man selbst "optimieren" durch Änderung des Statements. Was ich logisch betrachtet joine (oder filtere) ist dem Optimizer egal. Ist es auch kein Schlüsselfeld, hat aber trotzdem einen Index, kann ziemlich gezielt aus Millionen DS per RangeScan usw. eine kleine Restmenge gefiltert werden. Darauf dann noch 2 DinA4 Seiten EAV Bedingungen durchzusieben tut nicht mehr weh.
Natürlich kann EAV schmerzhaft werden. Es gibt ja nicht ohne Grund andere Verfahren, Dokument-basierte Store Engines sind ein gutes Beispiel.
 
ukulele schrieb:
Okay kurz nachgeschaut @dabadepdu hat recht, der Optimizer überführt die Filter-Kriterien aus dem WHERE-Teil in die Join Condition. Die Performance ist gleich.

In meinem Code mache ich das einfach tatsächlich immer so das ich die Join-Condition erweitere um das Ergebnis des Joins möglichst "schlank" zu halten (so mein Gedanke), damit nehme ich die Verlagerung durch den Optimizer quasi voraus. Das habe ich mir einfach so angewöhnt, häufig habe ich dann gar keinen WHERE-Teil mehr, es ist aber reine Geschmackssache.

Fun-Fact: Ich habe neulich noch interessante Artikel zu einem gar nicht mal so unähnlichen Thema gelesen: LEFT JOIN + WHERE left_table IS NULL vs. WHERE NOT EXISTS + Subquery

Why Not Exists Makes More Sense Than Left Joins For Performance – Darling Data

erikdarlingdata.com erikdarlingdata.com

@Flooo :
Verstehe ich jetzt noch nicht ganz, die entwickelst das Programm selber oder du hast Vorgaben durch ein bestehendes Programm? Warum musst du Joinen und kannst nicht AND verwenden?
Zum Vergrößern anklicken....
@ukulele Ich Programmiere das Programm selbst, könnte sogar die Datenstruktur nochmal ändern, habe aber keine andere Möglichkeit als so ein EAV gefunden, gibt es eine andere (vielleicht bessere) Möglichkeit so etwas (Datenhaltung/Struktur) zu machen, können halt immer unterschiedliche und andere Variablen sein

Ich schaue mir das ganze auch mal mit dem erwähnten JSON an, wäre auch eine Möglichkeit, aber ich habe die böse Vermutung das dies nicht sehr performant sein wird, oder? Vor allem weil JSON Parsing ja heavy Duty ist im Gegensatz zu einem einfach join, oder liege ich das falsch?

Die Datenmenge könnte schnell über 100000 "tokens" mit insgesamt 1-2 millionen variablen werden


VIELEN VIELEN Dank an euch alle fürs helfen
 
Werbung:
ukulele schrieb:
Okay kurz nachgeschaut @dabadepdu hat recht, der Optimizer überführt die Filter-Kriterien aus dem WHERE-Teil in die Join Condition. Die Performance ist gleich.

In meinem Code mache ich das einfach tatsächlich immer so das ich die Join-Condition erweitere um das Ergebnis des Joins möglichst "schlank" zu halten (so mein Gedanke), damit nehme ich die Verlagerung durch den Optimizer quasi voraus. Das habe ich mir einfach so angewöhnt, häufig habe ich dann gar keinen WHERE-Teil mehr, es ist aber reine Geschmackssache.

Fun-Fact: Ich habe neulich noch interessante Artikel zu einem gar nicht mal so unähnlichen Thema gelesen: LEFT JOIN + WHERE left_table IS NULL vs. WHERE NOT EXISTS + Subquery

Why Not Exists Makes More Sense Than Left Joins For Performance – Darling Data

erikdarlingdata.com erikdarlingdata.com

@Flooo :
Verstehe ich jetzt noch nicht ganz, die entwickelst das Programm selber oder du hast Vorgaben durch ein bestehendes Programm? Warum musst du Joinen und kannst nicht AND verwenden?
Zum Vergrößern anklicken....
Als kleiner Nachtrag: wie könnte ich das ganze mit einfachen AND´s machen?
 
Zurück
Oben