Index auf Boolean-Spalte

Question

Hallo

Bei einer Artikeltabelle gibt es die Spalte inactive welche als smallint umgesetzt ist und nur die Werte 0 und -1 enthalten kann. Gemäss div. Beiträgen solle man auf solche "Boolean"-Spalten keinen Index legen. SQL Server schlägt mir jetzt aber vor, bei der Abfrage SELECT COUNT(*) FROM tdProducts WHERE InActive = 0  einen Index zu erzeugen. Gemäss Abfrageplan würde das eine Verbesserung von 91% bewirken.
Macht der Index bei dieser Spalte Sinn, weil für die Anzahl Datensätze nur der Index herangezogen werden muss?

Wenn bei der Abfrage ein SELECT * FROM.... gemacht wird, bringt er mir den Vorschlag schon gar nicht.

Wenn ohne diesen Index die beiden Abfragen ausgeführt werden, ist es ein Verhältnis von 30%(Count)/70%(*)

Habe jetzt mal den Index gemacht und dann ist das Verhältnis 4/96 und beim Count wird dieser neue Index genutzt, bei * weiterhin nicht.

Von diesen Tests her würde ich sagen, dass der Index doch Sinn macht und der SQL Server selber merkt, wann es besser ist, ein Table Scan(wird im 2.Fall gemacht) zu machen.

Spricht da etwas dagegen?

Bei unserer Anwendung wird bei den Listendarstellung jeweils ein Count für die Paging-Berechnung gemacht und ein Select auf die Daten für die Listendaten.

Gruss Christoph

Answer 1

Hallo Christoph,

sehr vereinfacht gesagt wird ein Index nur dann verwendet, wenn die Daten auf 25% oder weniger gefiltert werden können und das ist bei einem Bool Wert schwierig; genauer hat es Kimberly Tripp mit dem Tipping Point beschrieben. Und aus diesem Grunde wird im Falle von SELECT * der Index nicht verwendet, es müssen eh alle Daten aus der Tabelle ermittelt werden, weswegen in dem Fall ein Full Table Scan effizienter sein kann. Aus diesem Grunde sollte man auch besser nie SELECT * verwenden, sondern wirklich nur die Spalten selektieren, die man benötigt.

 

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Hallo Olaf

Das SELECT * war nur ein Beispiel. Da sind schon die einzelnen Spalten aufgeführt, wenn da auch teilweise viele Spalten drinn sein können.

Dass es bei dieser Abfrage keinen Vorteil gibt, ist mir auch noch egal. Ich bin eher unsicher, ob der Index nicht auch einen Nachteil bringen kann. Beim Count ist der Vorteil ja sehr stark. Die Tabelle ist jetzt auch nicht so, dass es viele Updates/Inserts gibt, welche diesen inactive-Index sehr stark aktuallisieren müssten.

Den Beitrag werde ich mir noch durchlesen.

 

Gruss Christoph

Answer 3

Bei Indizes gilt immer:

- Sie brauchen Speicherplatz.

- Sie brauchen Resourcen bei Datenänderungen (DELETE, INSERT, UPDATE).

- Sie müssen gewartet werden.

Diese Größen musst du in Relation zu der Häufigkeit deines COUNT()-Falles setzen.  Vergleiche auch die Ausführungszaufzeit mit und ohne Index. Daher kann es in deinem Fall schon sinnvoll sein.

Wie sieht dein angsprochenes Paging konkret aus, wie kommt da ein COUNT() ins Spiel? Welche SQL Server Version setzt du ein?

Paging mit SQL Server 2000 geht mit @@ROWCOUNT. Ab SQL Server 2005 mit ROW_NUMBER().

Answer 4

Hallo Stefan

Die Anwendung mit das Paging noch mit Count(*)-Abfrage für die Anzahl Zeilen(Ausgabe an User) und dann ein SELECT Top 25 .... für die erste Seite etc.

Die Anwendung läuft bei div. Kunden und daher kann auch noch ein 2000er-SQL Server dabei sein.

Das wird in nächster Zeit aber umgestellt.
Es geht mir aber hauptsächlich darum, ob Indizes auf solchen "boolean"-Spalten grundsätzlich schlecht sind oder wenn der Optimierer da eine Verbesserung von 91% erwartet, das eben doch in Frage kommen kann.

Gruss Christoph

Answer 5

Hallo Christoph,

wenn der Optimierer der Meinung ist, dass eine Verbesserung von 91% erwartet werden kann, gilt das aber auch nur für genau diese Abfrage mit genau diesem Aufbau (insbesondere Where-Clause).

Dass es dennoch negative Auswirkungen an anderen Stellen geben kann, hat Stefan ja schon geschrieben. Daher würde ich es an deiner Stelle einfach mal in einer Testumgebung ausprobieren.

 

---

Gruß, Stefan
Microsoft MVP - Visual Developer ASP/ASP.NET
http://www.asp-solutions.de/ - Consulting, Development
http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

Answer 6

Hallo Christoph,

sieh Dir mal den Ausführungsplan von SELECT COUNT(*) an, ich schätze mal, das ist ein "Index Scan" und kein "Index Seek". Der Index wird also nicht als solcher verwendet, es ist nur halt effektiver, weil weit weniger Pages gelesen werden müssen, als wenn ein Table Scan ausgeführt wird.

Die Verbesserung ist ja nur eine relative Angabe und inwieweit Aufwand und Nutzen gegenüberstehen, kannst nur Du abschätzen. Verbessert es sich nur von 100ms auf 19ms oder geht es von Minuten- in den Sekundenbereich herunter, wie oft wird die Abfrage ausgeführt etc.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 7

Hallo Olaf

Es ist ein Index Seek und gemäss Analyse ist Pan usage 77 (letzter Neustart war am Donnerstag).
Diese Abfrage wird zumindest bei jedem Aufruf vom Artikelstamm sicher einmal aufgerufen(weil nur aktive Artikel angezeigt werden sollen). Danach gibt es z.B. eine Suche nach Artikelnummer (ebenfalls nur aktive) und eher in seltenen Fällen sollen inaktive angzeigt werden.

Ich habe mal einen Test mit rund 800'000 Artikel gemacht. Mit Index dauert die Abfrage 31ms, ohne 312ms. Ist also nicht im Minutenbereich. Bei den Lesevorgängen ist es von 4 zu 81000(ohne index). Das ist jetzt eine grössere Datenbank und da wird die usage-Zahl auch grösser sein.

Da es ein Artikelstamm ist und mehr gelesen als geschrieben wird, würde ich sagen, dass es ok ist. Bei einer Umsatztabelle kann es sicher wieder anders sein,weil da pro Belege Daten geschrieben werden, die Umsatzzahlen aber weniger gelesen werden.

Ich sehe, es gibt auch bei "Boolean"-Spalten nicht einfach ein ja/nein sondern muss pro Fall geprüft werden.

 

Gruss Christoph