Sql Abfrage noch optimierungsfähig?

[Frank (no reg)]

Hallo,

ich muss gerade eine Datenbankabfrage entwickeln, die mehrere Bit-Flag
(bzw. boolsche) Spalten prüft und frage mich ob das von mir entwickelte
Sql a) der Anforderung gerecht wird   b) noch optimiert werden könnte.

Ein Objekt hat zwei Bit-Flags, die unabhängig voneinander true (1)
oder false (0) sein können, damit ergeben sich 4 mögliche Zustände
pro Datensatz:
1 1
1 0
0 1
0 0

Die Tabelle sieht entsprechend aus:

Id      Payload       Flag1      Flag2
----------------------------------------------
1       ...           1          0
2       ...           1          1
3       ...           0          0

Für die Ausgabe möchte ich eine Filtermöglichkeit für Werte der Tabelle
schaffen, indem ich den Benutzer Checkboxen anklicken lasse

[ ] Elemente mit Flag 1 gesetzt
[ ] Elemente mit Flag 2 gesetzt
[ ] alle anderen Elemente                 (beide Flags sind 0)

Ich habe die Datenbankabfrage wie folgt abgebildet:

  
DECLARE @varFlag1 bit, @varFlag2 bit, @varOthers bit  
  
SELECT [Id], [Payload]  
FROM [tabelle1]  
WHERE (([Flag1] | [Flag2]) & @varOthers) = @varOthers  
AND ([Flag1] & @varFlag1) = @varFlag1  
AND ([Flag1] & @varFlag2) = @varFlag2  

Das scheint aber irgendwie zu überkandidelt in Hinblick auf die
Verwendung so vieler Bit-Operatoren (| und &).

Tests an Beispieldaten haben zunächst immer das gewünschte Ergebnis
gebracht (was aber nichts heißen muss ;)) Aber könnte man dies auch
evt. eleganter lösen? Wäre sehr erfreut, wenn da jemand mal seinen
Schweif drüberblicken lassen könnte.

Danke und Ciao bis später,
Frank

[Ilja]

yo,

Sql a) der Anforderung gerecht wird   b) noch optimiert werden könnte.

optimierung von datenbanken ist ein großes thema für sich und sql abfragen ist dabei nur eine schraube von vielen, an der gedreht werden kann. deshalb ist es immer schwierig, eine datenbank optimal abzustimmen, zumal die performance auch nur ein qualitätsmerkmal ist. grundsätzllich gilt aber immer alles auszuprobieren. nicht selten kommen ergebnisse heraus, die einen verwundern.

der zweite grundsatz ist, reicht mir das, was ich habe oder besteht handlungsbedarf. wenn es nämlich gar keine performance-probleme gibt und dir die abfrage so "gefällt", dann würde ich es einfach so lassen.

schaffen, indem ich den Benutzer Checkboxen anklicken lasse

ich gehe mal davon aus, dass man immer nur eine dieser drei optionen auswählen kann, also keine kombinationen davon. dann wäre eine möglichkeit, auch drei unabhängige abfragen dafür zu machen, die für sich optimiert sind.

eine verständnisfrage wäre noch, ob durch das auswählen von flag 1, automatisch damit flag 2 auf 0 sein sollte oder ob es auch auf 1 sein kann, hauotsache flag1 hat den wert 1

Ilja

[Frank (no reg)]

Hi Ilja,

man kann alle 3 Checkboxen unabhängig von einander an/abcheckern. Ergo,
auch Kombinationen sind möglich.

Was soll rauskommen (chkFlag1, chkFlag2 chkFlagOthers):

1 0 0    =  nur die, die Flag1 gesetzt haben sollen drin bleiben
1 1 0    =  nur die mit Flag1 und/oder Flag2 auf true haben sollen drin bleiben
0 1 1    =  nur die mit Flag2 oder weder Flag1 noch Flag2 gesetzt sollen drin bleiben

Ich hatte vorher ein 3faches Union-Select was aber deutlich mehr Logical
Reads hatte und insofern auf die Dauer "teuerer" sein könnte, da diese
Abfrage von sehr vielen Leuten gleichzeitig und wiederholt gebraucht wird.

Mir ging es hinsichtlich Optimierung eher um Verkürzung der WHERE Klausel, ich bin mit dieser UND ODER ABER NICHT -Mathematik nicht
wirklich der Spezi.

Grüße, Frank

[Ilja]

yo,

man kann alle 3 Checkboxen unabhängig von einander an/abcheckern. Ergo,
auch Kombinationen sind möglich.

ok, trotzdem gilt in aller regel, die checkboxen auswerten und entsprechend die where klausel aufbauen. also mehrere sql abfragen erstellen zu können ist besser, als eine abfrage zu haben, die alles abdeckt.

Beispiel: nehmen wir mal an, es wird nur flag 1 ausgewählt, dann wäre die WHERE klausel sehr einfach.

Ilja

[Frank (no reg)]

Hi,

ja, wären sehr einfach die Abfragen, und die für Checkbox 3 bräuchte
ich unter Umständen gar nicht ausführen. So gesehen auch die 2. nicht...
je nach Auswahl.

Aber dann hätte ich max. 3 Sql Abfragen = max. 3x Interprozesskommunikation
(Marshaling und so weiter) und genauso viele Logical Reads wie
mit 'nem SELECT ... UNION SELECT ... UNION SELECT ...

Aber danke dir dennoch für deine Unterstützung, sie hat mich zu dem
Punkt gebracht auch mal in andere Richtungen (mehrere Sql-Statements)
zu denken und die momentan Lösung als geeignet (fast ideal) zu erkennen.

Yo, man liest sich
Frank

[Auge]

Hallo

ja, wären sehr einfach die Abfragen, und die für Checkbox 3 bräuchte
ich unter Umständen gar nicht ausführen. So gesehen auch die 2. nicht...
je nach Auswahl.

Aber dann hätte ich max. 3 Sql Abfragen

Ich vermute mal, das Ilja eher darauf hinaus wollte, dass du, je nach den übergebenen Werten der Checkboxen, entweder die Abfrage onthefly anders zusammenbaust oder eine auf den entsprechenden Fall spezialisierte Abfrage aufrufst.

Tschö, Auge

[Ilja]

yo,

Ich vermute mal, das Ilja eher darauf hinaus wollte, dass du, je nach den übergebenen Werten der Checkboxen, entweder die Abfrage onthefly anders zusammenbaust oder eine auf den entsprechenden Fall spezialisierte Abfrage aufrufst.

genauso ist es, habe mich mal wieder umständlich ausgedrückt. das waären dann insgesamt 8 verschiedene abfragen, die jede für sich optimiert ist.

Ilja

[Frank (no reg)]

Hi Ilja und auch Auge,

Hilfe ;) 8 Abfragen .... 8x Interprozesskommunikation ... 8x Marshaling

Wie kommt ihr, bzw du auf 8 optimierte Abfragen?

  
ArrayList l = new ArrayList();  
  
// reagiere nur when die erste Checkbox gecheckt ist  
if(checkbox1.checked)  
{  
  // flag 1 soll geprüft werden und nur elemente mit flag 1 sollen ausgelesen werden  
  [code lang=sql]  
  SELECT [Id], [Payload] FROM [tabelle] WHERE Flag1 = 1  
  

// ich lese die selektierten Ids in die ArrayList
}

// reagiere nur when die zweite Checkbox gecheckt ist
if(checkbox2.checked)
{
  // flag 2 soll geprüft werden und nur elemente mit flag 2 sollen ausgelesen werden
  ~~~sql

SELECT [Id], [Payload] FROM [tabelle] WHERE Flag2 = 1

  // die ArrayList von oben wird weiter gefüllt mit Ids aus der Abfrage, wenn diese nicht sowieso schon drin sind (von erster Abfrage)  
}  
  
// reagiere nur wenn dritte Checkbox (alle anderen) gecheckt ist  
// wenn nicht gecheckt, dann interessiert mich dieser Fall nicht  
if(checkbox3.checked)  
{  
  // alles was Flag1 und Flag2 auf 0 hat soll ausgelesen werden  
  ~~~sql
  
  SELECT [Id], [Payload] FROM [tabelle] WHERE Flag1 = 0 AND Flag2 = 0  
  

// die ArrayList von oben wird weiter gefüllt mit Ids aus der Abfrage, wenn diese nicht sowieso schon drin sind (von 1./2. Abfrage)
}
[/code]

Das Sql Statement On-the-fly zusammenbauen hat den nachteil, dass
(fast) jedes Mal der Ausführungsplan neu berechnet werden müsste.
Was auf die Dauer (trotz Connection Pooling etc) auch zu einer
Performance Reduzierung führen kann.

Grüße, bis später,
Frank

[Auge]

Hallo

Hilfe ;) 8 Abfragen .... 8x Interprozesskommunikation ... 8x Marshaling

Du verstehst es immer noch nicht. :-)

Du legst die notwendige Anzahl an möglichen Abfragen in deinem Programm fest in die du eventuell nur noch weitere formularelemente einfügen musst. Je nach den übergebenen Werten der Checkboxen wählst du eine andere Abfrage aus, die du an den  DB-Server übergibst. es wird also immer nur eine der Abfragen ausgeführt.

Tschö, Auge

[Frank (no reg)]

Hi,

also doch nicht 8 Abfragen (mich interessiert aber dennoch wie ich
zur Abbildung von 4 Checkbox-Zuständen auf 8 "optimierte" Abfragen
kommen soll) sondern nur eine, dafür komplexere (weil evendudel
dynamisch zusammengebaut) Abfrage? Okay, aber genau diese eine
Abfrage hab ich doch jetzt auch schon und so komplex ist diese jene
welche nun auch nicht. Und sie deckt (dank ausreichend viel Testerei)
die Anforderung a) funktionell und b) recht performant ab.

Aber es hätte ja sein können, dass die WHERE-Klausel hätte einfacher
gestaltet werden können.

Aber ein großes Danke dennoch an alle Antworter, da sie mich über
mögliche Alternativen haben nachdenken lassen, bzw. dazu angestoßen.

Ciao, Frank

[Struppi]

Ein Objekt hat zwei Bit-Flags, die unabhängig voneinander true (1)
oder false (0) sein können, damit ergeben sich 4 mögliche Zustände
pro Datensatz:
1 1
1 0
0 1
0 0

Die Tabelle sieht entsprechend aus:

Id      Payload       Flag1      Flag2

1       ...           1          0
2       ...           1          1
3       ...           0          0

Ich hab nicht alles verstanden, aber warum verwendest du hier zwei Bitflags statt einer Zahl wo du die entpsrechenden Bits setzt?

Dann ließe sich die Abfrage auch einfach mit einer Bedigung aufbauen (übrigens dürften Bitoperation nicht zeitkritisch sein, da diese zusammen mit incr, decr die maschinennahsten Operationen sind und zumindest die ausführungszeit extrem schnell ist)

Also in etwa so:
Id      Payload       Flag
----------------------------------------------
1       ...           1
2       ...           3
3       ...           0

und dann:

[ ] Elemente mit Flag 1 gesetzt

WHERE Flag & 1

[ ] Elemente mit Flag 2 gesetzt

WHERE Flag & 2

[ ] alle anderen Elemente                 (beide Flags sind 0)

WHERE Flag & 0

und auch möglich:

[ ] alle Elemente mit Flag1 und Flag2

WHERE Flag & 3

Struppi.

[Frank (no reg)]

Hi,

vielen Dank für diesen Hinweis, ich werde mal schauen, ob sich der
Aufwand bezüglich Implementierung der anderen Tabellen-Struktur (es
hängt noch etwas mehr als nur meine Abfrage an dieser Struktur) in
Bezug auf Performanceverbesserung.

Viele Grüße,
Frank