Warum machen die mehrere WHERE-Bedingungen langsame Abfragen anstatt zu beschleunigen?
Das problem ist, dass die Abfrage läuft sehr langsam, wenn im Vergleich zu der Abfrage laufen mit ein oder zwei, anstatt alle drei Bedingungen.
Nun die Abfrage.
Select Count(*)
From
SearchTable
Where
[Date] >= '8/1/2009'
AND
[Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150))
AND
FreeText([Description], 'keyword list here')
Die erste Bedingung ist selbsterklärend. Die zweite benutzt eine UDF, um eine Liste der Postleitzahlen, die innerhalb von 150 Meilen von 30348. Die Dritte verwendet einen Volltext-index, um die Suche für die zur Verfügung gestellten Worte.
Nur mit dieser Bedingung
[Date] >= '8/1/2009'
Die Abfrage 43884 (Tabelle Größe ist knapp 500k Zeilen) in 3 Sekunden.
Nur mithilfe dieser Zustand
[Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150))
Bekomme ich 27920, auch wieder in 3 Sekunden.
Und mit nur der vollständige text ist Teil
FreeText([Description], 'keyword list here')
68404 ist wieder in 8 Sekunden.
Wenn ich nur die Postleitzahl und der Volltext-Bedingungen, die ich bekommen 4919 in 4 Sekunden.
Nur das Datum und den vollständigen text der Bedingungen ruft, die mich 9481 in nur schüchtern von 14 Sekunden.
Mit dem Datum und der Postleitzahl Bedingungen nur gibt mir 3238 in 14 Sekunden.
Mit allen drei Bedingungen, die von der Abfrage zurückgegeben 723 in 2 Minuten, 53 Sekunden. (wtfbbq)
3 Sekunden ist eine LANGE Zeit für nur 500k Zeilen. Indizes sind definitiv fehlen
Wünschte, ich könnte wurden ausgezeichnet: die Antwort auf mehr als eine person, denn viele waren hilfreich. Showplan ergab, dass die UDF war auf jeden Fall das Problem. wenn ich dump die Ergebnisse in eine temp-Tabelle und die join, bekomme ich mein Ergebnis in 0 Sekunden... SIEG!!... Dank Stack-Überlauf homies..
Clark: ich hatte das gleiche problem... und durch die übergabe mit einer temp-Tabelle funktioniert es wie ein Charme. Thx
InformationsquelleAutor Clark | 2010-01-29
Du musst angemeldet sein, um einen Kommentar abzugeben.
Der einzige Weg zu wissen, warum, ist zu prüfen, die Durchführung zu planen.
Versuchen SET SHOWPLAN_TEXT ON.
InformationsquelleAutor
Holen Sie sich einen Ausführungsplan
Müssen Sie sich bei der Ausführung plan um jede Hoffnung verstehen, der eigentliche Grund für die variation in den Reaktionszeiten. Insbesondere in diesem Fall gibt es mehrere Faktoren zu berücksichtigen:
Freetext
bedeutet, dass die Volltext-Suchmaschine verwendet wird, die möglicherweise verursacht werden, die SQL server zusätzliche Probleme bei der Vorhersage der Anzahl der zurückgegebenen Zeilen.Wirklich, bekommen Sie einen Ausführungsplan.
Update:
Mögliche Ursachen
In der Abwesenheit des einen Ausführungsplan ich denke, dass die wahrscheinlichste Ursache für die langsame Ausführung ist schlecht Schätzungen für die Bedingungen auf
ZipCode
undDescription
:ZipCode
Zustand als ein Ergebnis hängt von einer gespeicherten Prozedur.FreeText
Zustand, wie Ihr auf Ergebnisse aus der full-text-Abfrage-engine.Was ich glaube, was geschieht, ist, dass SQL server unter-Schätzung der Anzahl von Zeilen, die bleiben nach der Filterung, und die Anwendung der Abfragen in der falschen Reihenfolge. Das Ergebnis ist, dass es endet, das zu tun, Dutzende (vielleicht Hunderte) von tausenden von lookups, und das ist weitaus langsamer als nur tun, eine Tabelle Scannen.
Für eine besonders komplizierte Abfrage, die ich gesehen habe, SQL-server ausführen ~3,000,000 lookups Versuch, die eine einzelne Zeile zurückgeben - die Tabelle gar nicht haben 3,000,000 Zeilen!
Dinge zu versuchen - Setzen ZipCodeForRadius in eine temp-Tabelle.
Wenn ich Recht habe, dann helfen Sie mit dem ersten könnte man versuchen, die Ergebnisse der
ZipCodesForRadius
gespeicherte Prozedur in eine temporäre Tabelle, ich muss zugeben, dass ich nicht über eine gute Erklärung, warum dies wird helfen, aber ich habe ein paar Theorien darüber, warum es könnte Hilfe:SELECT
- Anweisung neu kompiliert werden, jedes mal, wenn Sie die Abfrage ausführen (es sei denn, der Bereich der Postleitzahlen ist sehr klein) in der proc dauert ein paar Sekunden, wie auch immer, das wird eine gute Sache sein, wenn es eine große variation in der entsprechenden Postleitzahl. Wenn nicht, dann gibt es Möglichkeiten, zu vermeiden, dass die Neukompilierung.Es sicherlich sollte nicht tun zu viel Schaden in jedem Fall.
InformationsquelleAutor
Da mehr Bedingungen zu prüfen, ist mehr Arbeit für die Datenbank-engine. Logisch scheint mir.
Wenn Sie eine Bedingung, die über einen gruppierten index-Feld, das insbesondere zu prüfen wäre nicht verlangsamen den Vorgang, der viel. Haben Sie sich überlegt Neuordnung Indizes an, die Ihrer Abfrage entsprechen?
Auch wenn das DBMS nahm der naive Ansatz, der die Anwendung der Bedingungen, die man in einer Zeit, warum sollte es logisch erscheinen, für die gesamte Zeit zu springen, 173 Sekunden?
Wenn man es in 10 Sekunden mit der temp-Tabelle Ansatz, ich schlage vor, Sie verwenden es.
NEIN! - SQL-Abfragen sind einfach so viel komplizierter als entweder "Mehr Filter = mehr Arbeit"!!!
"Da mehr Bedingungen zu prüfen, ist mehr Arbeit für die Datenbank-engine" - das ist überhaupt nicht wahr. In der Tat, was passiert (simplisticly) ist, dass die Abfrage-Optimierer untersucht die Bedingungen und die verfügbaren Indizes und versucht, den schnellsten Zugang zu planen. Es gibt keine direkte Beziehung zwischen dem, wie viele, wo die Bedingungen und die Geschwindigkeit.
InformationsquelleAutor
Wenn die Bestellung der WHERE-Klausel nicht die Dinge beschleunigen, nachdem Sie eine ausgewählt werden, um Ihre wählen Sie können den trick tun (Es beschleunigt die Dinge bei einigen Gelegenheiten mit DB2/400, nicht sicher darüber, wie SQL Server optimiert):
InformationsquelleAutor
Versuchen, fügen Sie einige Indizes der Tabelle. Speziell diejenigen, die Abdeckung der Bedingungen in Ihrer where-Klausel. Wahrscheinlich ist es jetzt eine Tabelle Scannen, ziehen Sie die Daten zurück, die können sehr, sehr langsam.
Auch Sie möchten möglicherweise verwenden Sie den Tatsächlichen Ausführungsplan Einschließen in management studio, um zu zeigen, wie es geht, zu bestimmen, welche Datensätze Sie erhalten.
UPDATE
Einem Ihrer Kommentare es klingt wie diese Abfrage ist das ziehen aus einer temp Tabelle. In diesem Fall, nach dem erstellen der Tabelle gelten Indizes. Hinzufügen von Indizes, dann laufen die Abfragen schneller sein als eine Tabelle Scannen auf 500k Zeile temp-Tabelle.
InformationsquelleAutor
Wenn Sie eine Bedingung, count (), dann kann die Abfrage Scannen der kleinste index, umfasst die Zählung. Auch wenn ein full-scan, die Anzahl der Seiten zu Lesen ist viel kleiner als das eines der "clustered index scan", das ist wohl viel breiter. Wenn Sie mehrere Bedingungen, die die Kandidaten Reihen haben, die verbunden werden, und die Abfrage-plan möglicherweise verlassen die non-clustered-index scans (oder range-scans) und für einen full table scan.
In Sie Fall, was wahrscheinlich passiert ist:
[Date] >= '8/1/2009'
zufrieden ist, indem ein index mit Datum, wahrscheinlich durch einen index AUF dem Datum, so dass Ihr eine schnelle-range-scan[Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150))
gleichen Datum. Auch wenn Sie keinen index auf dem Punkt, haben Sie wahrscheinlich eins mit Reißverschluss.FreeText([Description], 'keyword list here')
Volltext-Suche für die Zählung, die durch interne FT-Indizes, schnell.Alle drei Bedingungen. Jetzt wird es chaotisch. Wenn Sie genug RAM haben kann die Abfrage machen Sie einen plan für die FT erstmal suchen, dann HASH-JOIN werden dann Zip-scan HASH-JOIN-Datum. Wäre dies schnell, in der Größenordnung von 3+3+8 Sekunden + ändern (für die hash-operation). Aber wenn Sie nicht genügend RAM haben, oder wenn der Optimierer nicht, wie zu tun, ein hash-join, es wird zu tun haben eine fehlertolerante Suche, dann nested-loop-Suche von Zip dann nested-loop-Suche von Code, und es treffen index tipping point in seinen Entscheidungen. Also wahrscheinlich bekommen Sie eine Tabelle Scannen. Das ist natürlich Spekulation meinerseits, aber nachdem alles, was Sie geschrieben nur in der T-SQL-text und keine Informationen über die Struktur von gruppierten und nicht-gruppierten Indizes.
Am Ende haben Sie sich daran zu erinnern, dass SQL ist eine nicht dem C-wie prozedurale Sprache. Wenn man über die Leistung in SQL ist nie über Vergleiche und Boolesche Logik. Es geht immer darum, Zugriff auf die Daten und die Menge der Seiten, die zu Lesen. Also auch wenn jede einzelne Bedingung kann erfüllt werden, indem eine kleine, schnelle, index-range-scan von einem schmalen nicht-gruppierten index oder FT-index, der die Kombination nicht (oder in seinem Fall, wird der Abfrageoptimierer nicht herausfinden, einen Weg zu).
InformationsquelleAutor
Daten-Transport-wise, sind Sie richtig in Ihrem denken: weniger Daten, schnellere Fertigstellung Zeit. Allerdings, in der Regel, die Zeit ist minimal, und die meisten der Zeit, die auf die aktuelle Abfrage-Verarbeitung.
Sieh es mal so: Wenn Sie in einem Wagen waren viel, wäre es einfacher, herausgreifen, alle Autos, die waren rot, oder alle Autos, die waren rot, Modelljahr 2006, schwarzer Innenraum, und hatte die Gummi-Fußmatten?
InformationsquelleAutor
Ich vermute, dass Sie das Date-Feld nicht indiziert ist, und ohne einen index zu verlassen sich auf Sie zum filtern der Ergebnismenge vor der Anwendung der where-Klausel auf der nicht-sargable-Spalten, es gibt Sie alle das gleiche Gewicht und führt nicht die quick-Filter den ersten, bevor die anderen teurer Klauseln.
Wenn ich nicht in der Lage zu optimieren der Datenbank mit Indizes, etc., Oft finde ich, dass das erneute schreiben der Abfrage wie dieser ist genug, um direkt den compiler zu einer effizienteren Abfrage:
InformationsquelleAutor