Langsame Abfrage mit unerwarteter Indexsuche

Question

Ich habe folgende Frage:

SELECT *
FROM sample
   INNER JOIN test ON sample.sample_number = test.sample_number
   INNER JOIN result ON test.test_number = result.test_number
   WHERE sampled_date BETWEEN '2010-03-17 09:00' AND '2010-03-17 12:00'

die größte Tabelle hier ist RESULT, sie enthält 11,1M Datensätze. Die linken 2 Tabellen etwa 1M.

Diese Abfrage arbeitet langsam (mehr als 10 Minuten) und liefert etwa 800 Datensätze. Der Ausführungsplan zeigt geclusterter Index-Scan (über seinen PRIMARY KEY (result.result_number, der eigentlich nicht an der Abfrage beteiligt ist)) über alle 11M Datensätze. RESULT.TEST_NUMBER ist ein geclusterter Primärschlüssel.

Wenn ich 2010-03-17 09:00 auf 2010-03-17 10:00 ändere, erhalte ich etwa 40 Datensätze. Es wird für 300ms ausgeführt. und Plan zeigt Indexsuche (über result.test_number index)

wenn ich * in SELECT-Klausel zu result.test_number (mit Index abgedeckt) ersetzen - dann werden alle schnell im ersten Fall zu. dies deutet auf hdd IO-Probleme, aber nicht klärt ändern Plan.

Also, irgendwelche Ideen?

UPDATE: sampled_date befindet sich in der Tabelle sample und wird durch den Index abgedeckt. andere Felder dieser Abfrage: test.sample_number ist durch den Index abgedeckt und result.test_number ebenfalls.

UPDATE 2: offensichtlich als Sql-Server in allen Gründen nicht wollen, um Index zu verwenden.

Ich habe ein kleines Experiment gemacht: Ich habe INNER JOIN mit result entfernt, alle test.test_number ausgewählt und danach

SELECT * FROM RESULT WHERE TEST_NUMBER IN (...)

dies, natürlich, funktioniert schnell. aber ich kann nicht bekommen, was ist der Unterschied und warum Abfrage-Optimierer wählen Sie solche unangemessenen Weg, um Daten im ersten Fall auswählen.

UPDATE 3: nach dem Sichern der Datenbank und dem Wiederherstellen der Datenbank mit dem neuen Namen - beide Anfragen funktionieren schnell wie erwartet, auch bei viel mehr Bereichen...

Gibt es irgendwelche speziellen Befehle zum Säubern oder Optimieren oder was auch immer, die in diesem Zusammenhang relevant sein können? :-(

Answer 1

Ein paar Dinge zum Ausprobieren:

• Statistiken aktualisieren
• Fügen Sie der Abfrage Hinweise darauf hinzu, welcher Index verwendet werden soll (in SQL Server könnten Sie sagen WITH (INDEX(myindex)) nach Angabe einer Tabelle)

EDIT: Sie haben festgestellt, dass es durch das Kopieren der Datenbank funktioniert hat, was mir sagt, dass die Indexstatistiken veraltet sind. Sie können sie aktualisieren mit etwas wie UPDATE STATISTICS mytable auf regelmäßiger Basis.

Verwenden Sie EXEC sp_updatestats um die gesamte Datenbank zu aktualisieren.

Answer 2

Als Erstes würde ich die gewünschten Spalten genau angeben und sehen, ob das Problem weiterhin besteht. Ich bezweifle, dass Sie alle Spalten aus allen drei Tabellen benötigen.

Es hört sich so an, als ob es Probleme damit hat, alle Zeilen aus der Ergebnistabelle zu bekommen. Wie groß ist eine Zeile? Schauen Sie sich an, wie groß alle Daten in der Tabelle sind und teilen Sie sie durch die Anzahl der Zeilen. Klicken Sie mit der rechten Maustaste auf die Tabelle -> Eigenschaften..., Registerkarte Speicher.

Versuchen Sie, die Where-Klausel in eine Unterabfrage einzufügen, um sie zu zwingen, das zuerst zu tun?

SELECT *
FROM 
    (SELECT * FROM sample 
     WHERE sampled_date 
     BETWEEN '2010-03-17 09:00' AND '2010-03-17 12:00') s
     INNER JOIN test ON s.sample_number = test.sample_number
     INNER JOIN result ON test.test_number = result.test_number

ODER dies könnte besser funktionieren, wenn Sie eine kleine Anzahl von Proben erwarten

SELECT *
FROM sample
   INNER JOIN test ON sample.sample_number = test.sample_number
   INNER JOIN result ON test.test_number = result.test_number
WHERE sample.sample_ID in (
    SELECT sample_ID
    FROM sample
    WHERE sampled_date BETWEEN '2010-03-17 09:00' AND '2010-03-17 12:00'
)

Answer 3

Wenn Sie eine SELECT * wollen Sie alle Daten aus der Tabelle. Die Daten der Tabelle befinden sich im geclusterten Index - die Blattknoten des geclusterten Index sind die Datenseiten.

Wenn Sie also sowieso alle diese Datenseiten haben wollen, und da Sie 1 Mio. Zeilen mit 11 Mio. Zeilen verbinden (1 von 11 ist für SQL Server nicht sehr selektiv), könnte die Verwendung eines Indexes, um die Zeilen zu finden und dann Lesezeichen-Lookups in den tatsächlichen Datenseiten für jede dieser gefundenen Zeilen durchzuführen, einfach nicht sehr effizient sein, und daher verwendet SQL Server stattdessen den geclusterten Index-Scan.

Um es kurz zu machen: Wählen Sie nur die Zeilen aus, die Sie wirklich benötigen! Damit geben Sie SQL Server die Möglichkeit, einen Index zu verwenden, dort zu suchen und die benötigten Daten zu finden.

Wenn Sie nur drei oder vier Spalten auswählen, ist die Wahrscheinlichkeit, dass SQL Server einen Index findet und verwendet, der diese Spalten enthält, sehr viel größer, als wenn Sie alle Daten aus allen beteiligten Tabellen abfragen.

Eine andere Möglichkeit wäre, einen Weg zu finden, eine Unterabfrage zu formulieren, z. B. mit einer Common Table Expression, die Daten aus den beiden kleineren Tabellen abruft, die Anzahl der Zeilen noch weiter reduziert und das hoffentlich recht kleine Ergebnis mit der Haupttabelle verbindet. Wenn Sie eine kleine Ergebnismenge von nur 40 oder 800 Ergebnissen haben (und nicht zwei Tabellen mit jeweils 1 Mio. Zeilen), dann ist SQL Server möglicherweise eher geneigt, einen Clustered Index Seek zu verwenden und Lesezeichenabfragen für 40 oder 800 Zeilen durchzuführen, als einen vollständigen Clustered Index Scan.