Abfrage arbeitet um ein Vielfaches langsamer, wenn BIT-Spalte mit 0 statt 1 verglichen wird

Question

Ich verwende eine Ansicht, die auf einer komplexen Abfrage mit 17 Joins (sowohl innere als auch linke/rechte äußere) und Unterabfragen basiert. Alle Zeilen der Ansicht werden in etwa 5 Sekunden angezeigt.

SELECT * FROM a_view;

Eine der Spalten der Ansicht hat den Typ BIT. Und wenn ich Ansichtszeilen filtere und sie mit 1 vergleiche, funktioniert eine Abfrage wieder etwa 5 Sekunden.

SELECT * FROM a_view WHERE c = 1;

Aber wenn ich diese BIT-Spalte mit 0 vergleiche, dauert eine Abfrage etwa 50 Sekunden (10 mal langsamer).

SELECT * FROM a_view WHERE c = 0;

Diese Abfrage, die dieselben Ergebniszeilen liefert, funktioniert wie erwartet etwa 10 Sekunden:

SELECT * FROM a_view 
EXCEPT
SELECT * FROM a_view WHERE c = 1;

Ich frage mich also, warum der Vergleich mit 0 oder 'FALSE' so viel Zeit in Anspruch nimmt? Irgendwelche Ideen, bitte.

Die Sortierung nach diesem BIT-Feld ist schnell. Auch das Filtern nach anderen Spalten ist schnell.

Answer 1

Ich weiß, dass dies eine sehr alte Frage ist, aber falls jemand immer noch nach einer Lösung sucht, kann er dies versuchen. Ich bin vor kurzem auf das gleiche Problem gestoßen, und zwar bei einer relativ einfachen Abfrage, nur 3 Tabellen in einer Verknüpfung, die größte davon mit etwas über 1000 Zeilen. Leider hatte ich keine Berechtigung, den Ausführungsplan einzusehen, also musste ich improvisieren.

select * from subquery_x;

sehr schnell, im Grunde sofort fertig (nur etwa 500 Zeilen zurückgegeben), wie es sein sollte

select * from subquery_x where column_x = 1  

dtto, sehr schnell, Spalte_x ist eine Nicht-Null-Bit-Spalte

select * from subquery_x where column_x = 0
select * from subquery_x where column_x != 1

sollte etwa 300 Zeilen zurückgeben, beides ist sehr, sehr langsam, es hat tatsächlich mehrere Minuten gedauert!!

einfache (aber seltsame) Lösung - Konvertierung der Spalte in der Where-Klausel in tinyint

select * from subquery_x where convert(tinyint,column_x) = 0

Ich weiß, dies könnte einige Performance-Nebeneffekte haben, aber es funktionierte wie ein Charme, Vergleich konvertiert Spaltenwert zu 1 war auch sehr schnell, so dass ich es so lassen (es wurde in einem Bericht mit dem verglichenen Wert als Parameter zur Verfügung gestellt verwendet)

Wenn jemand weiß, warum das passiert, lasst es uns wissen, ich vermute, dass es ein Fehler ist, aber wer weiß, es könnte genauso gut ein böses Feature sein :-)

Answer 2

Die SQL-Server-SQL-Engine platziert die gesamte SQL-Abfrage des Views in die SQL-Anweisung, die Sie für den View geschrieben haben, und versucht dann, sie zu optimieren.

Dies könnte dazu führen, dass bei c=0 die Statistiken der verwendeten Tabellen zeigen, dass es viel mehr Zeilen gibt, die diesem Prädikat entsprechen, als bei c=1. Bei c=1 könnte eine Tabelle, die das Feld c enthält, das im Mittelpunkt der Verknüpfungen steht, beispielsweise nur 5 übereinstimmende Zeilen liefern, was zu einem ganz anderen Ausführungsplan führt, als wenn die Tabelle 1 Million Zeilen liefert (was zum Beispiel bei c=0 der Fall ist).

Prüfen Sie also die Ausführungspläne für beide. Untersuchen Sie auch die Server-Profiler-Ergebnisse für beide, da bei c=0 möglicherweise viel mehr Lesevorgänge als bei c=1 stattfinden und viel mehr Ergebnisse als bei c=1 zurückgegeben werden. Die Rückgabe aller Zeilen kann eine Weile dauern, so dass auch dies der Grund sein könnte, warum die Abfrage langsamer ist.

Answer 3

Normalerweise gibt es mehr als eine Möglichkeit, eine Abfrage mit Joins auszuführen. Alle modernen RDBMS durchsuchen verschiedene Join-Pläne auf der Suche nach dem besten Plan, indem sie die Kosten (CPU- und Plattenzugriffszeiten) für jeden Plan abschätzen.

Das Problem ist, dass jede zusätzliche Verknüpfung in einer Abfrage die Anzahl der möglichen Pläne um eine steigende Zahl vervielfacht, was zu Doppelfaktorielle (schlimmer als exponentielle) Zunahme der Zahl der Pläne zu berücksichtigen, wenn die Anzahl der Verbindungen zunimmt. Aus diesem Grund muss die DB die Suche irgendwo einschränken, was bedeutet, dass für Abfragen mit vielen Verknüpfungen zwangsläufig suboptimale Pläne gewählt werden.

Als Referenz: PostgreSQL hört standardmäßig nach 12 Joins auf, alle möglichen Pläne zu evaluieren. SQL Server hat sicher eine ähnliche Grenze. Bei 17 Joins würde die Auswertung (2 * 13) * (2 * 14) * (2 * 15) * (2 * 16) * (2 * 17) mal so lange dauern - das ist mehr als genug, um jedes RDBMS zu überfordern, das je existiert hat, oder jemals wird .

Es ist auch zu bedenken, dass die DBs die Kosten auf der Grundlage grober Statistiken schätzen, z. B. der Anzahl der unterschiedlichen Werte in einer Spalte und/oder einer Liste der 10 häufigsten Werte in einer Spalte. All dies führt dazu, dass die Wahrscheinlichkeit, die beste (oder auch nur eine vernünftige) Join-Strategie zu wählen, mit zunehmender Anzahl von Joins sinkt Abstieg .

Warum müssen Sie 17 Tabellen verbinden? Gibt es keine Möglichkeit, Ihr DB-Schema zu vereinfachen?

Answer 4

Ähnlich wie horcic.roman Lösung... Ich habe den Wert oder die Spalte zu BIT für eine große Erhöhung der Geschwindigkeit.

myColumn = CAST(1 AS BIT)

CAST(myColumn AS BIT) = 1

Offensichtlich ziemlich seltsam, wenn man bedenkt, dass die Spalte BIT NOT NULL ist.

Answer 5

Langsam :

select * from bigTable where column = 1

langsam

select * from bigTable where column = cast(1 as bit)

schnell:

@b as bit = 1 deklarieren

select * from bigTable where column = @b