Langsame Abfrage mit unerwarteten index-scan

Ich habe diese Abfrage:

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'

den größten Tisch hier ist das ERGEBNIS, Sie enthält 11,1 M records. Die linken 2 Tabellen über 1M.

diese Abfrage funktioniert, langsam (mehr als 10 Minuten) und zurück über 800 Einträgen. Ausführung von plan zeigt, "clustered index scan" (über dem Primärschlüssel (Resultat.result_number, die eigentlich gar nicht in der Abfrage)) über alle 11M records.
ERGEBNIS.TEST_NUMBER ist, einen gruppierten Primärschlüssel.

wenn ich 2010-03-17 09:00-2010-03-17 10:00 - ich bekomme etwa 40 Datensätze. es führt für 300ms. und der plan zeigt index seek (über das Ergebnis.test_number index)

wenn ich ersetzen Sie * in der SELECT-Klausel zur Folge.test_number (abgedeckt mit index) - dann werden alle zu schnell im ersten Fall auch. diese Punkte zu hdd-IO-Probleme, aber nicht klärt wechselnden plan.

so, irgendwelche Ideen?

UPDATE:
sampled_date ist in Tabelle Probe und fallenden index.
weitere Felder aus dieser Abfrage: testen.sample_number bedeckt ist, die durch den index und Ergebnis.test_number zu.

UPDATE 2:
offensichtlich als sql-server in einer beliebigen Gründen nicht verwenden möchten index.

habe ich ein kleines experiment: ich entfernen Sie die INNERE VERKNÜPFUNG mit dem Ergebnis, wählen Sie alle testen.test_number und danach tun

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

diese, natürlich, arbeitet schnell. aber ich kann nicht bekommen, was ist der Unterschied und warum query optimizer wählen Sie solche unangebrachten Art und Weise, um Daten auszuwählen, die im 1. Fall.

UPDATE 3:
nach dem sichern der Datenbank und der Wiederherstellung der Datenbank mit dem neuen Namen - beide Anträge schnell arbeiten wie erwartet auch auf viel mehr Bereiche...

so gibt es spezielle Befehle zu bereinigen oder zu optimieren, was auch immer, werden kann dies relevant? 🙁

  • Gibt es einen index nur auf Sampled_date? Welchen SQL-server verwendest du? Was für ein Tisch ist sampled_date in?
  • sampled_date IN der Probe natürlich. und es ist bedeckt mit index, natürlich auch (index-Suche, die für diesen index im plan immer). sql 2000. "Gibt es einen index nur auf Sampled_date?" ich versteh das nicht 🙁
  • Gibt es einen index, der nur Spalte ist Sampled_date?
  • yep. es ist ein index besteht nur aus der PROBE.SAMPLED_DATE (und nicht mehr Indizes enthält dieses Feld mit der linken-den meisten Teil)
  • versuchen Sie, wählen Sie 'x' anstelle von select *. wie schnell ist es? vielleicht ist es die Menge der Daten, die Sie zurückgeben. wenn es die Menge der Daten (800 Zeilen) ist das problem, versuchen Sie, um den Seitenumbruch der Ergebnisse
  • die Abfrage langsam, wenn ich versuche, wählen Sie ein Feld aus anderen ERGEBNIS zu FÜHREN.TEST_NUMBER
  • ich denke, das ist unmöglich, jedes Feld sollte immer noch schneller sein. die joins und wheres tun die Projektion von Daten, und wählen Sie werden einfach nur wählen Sie aus den projizierten Daten. so auch eine einfache select 'x' oder wählen Sie 1, sollte so schnell wie wählen Sie Ergebnis.test_number. nur meine Vermutung
  • yep. wählen Sie 1, und wählen Sie Ergebnis.test_number sind schnell. weil 1 ist eine statische und Ergebnis.test_number entnommen index während der index-Suche. ABER wenn wir WÄHLEN Sie ERGEBNIS.NAMEN, die nicht behandelt mit index sql-server zu tun haben Lesevorgänge von der Festplatte, um Daten abzurufen. und wie ich schon sagte, diese Tatsache nicht klärt, warum diese änderungen query plan 🙁
  • versuchen composite-index auf test_number und der name der Ergebnis Tabelle
  • Buen: dies wird helfen, natürlich, aber es gibt ~50 Felder in der Ergebnistabelle und ~20 erforderlich ist. bauen-index von über 20 Felder, die nur für eine Abfrage ist es nicht Wert imo. ich bevorzuge zu schauen, für andere Lösungen, die helfen, die server zu wissen, dass suchen besser dann Scannen Sie in diesem Fall.

InformationsquelleAutor zerkms | 2010-03-17
  1. 7

    Ein paar Dinge zu versuchen:

    • Update statistics
    • Fügen Sie Hinweise für die Abfrage über das, was index zu verwenden (im SQL Server könnte man sagen WITH (INDEX(myindex)) nach Angabe einer Tabelle)

    EDIT: Du festgestellt, dass das kopieren der Datenbank machte es Arbeit, die mir sagt, dass die index-Statistiken veraltet seien. Sie können aktualisieren Sie Sie mit so etwas wie UPDATE STATISTICS mytable auf einer regulären basis.

    Verwenden EXEC sp_updatestats update der gesamten Datenbank.

    • yep, nach UPD3 auf die Frage: irgendein Befehl, den ich brauche, um dieses Problem zu lösen 😉
    • yep, ich werde versuchen, dies morgen, wenn Sie zur Arbeit kommen. danke. und kann jeden gleichen Methoden zur Aktualisierung von Statistiken für die gesamte Datenbank?
    InformationsquelleAutor Gabe
  2. 0

    Das erste, was ich tun würde, ist exakt festlegen, welche Spalten ich will, und sehen, ob die Probleme weiterhin bestehen. Ich bezweifle, müssten Sie alle Spalten aus allen drei Tabellen.

    Es klingt wie es hat Mühe, sich alle Zeilen der Ergebnistabelle. Wie groß ist eine Reihe? Schauen Sie, wie groß alle Daten in der Tabelle und dividieren es durch die Anzahl der Zeilen. Der rechten Maustaste auf die Tabelle -> Eigenschaften..., Register Speicher.

    Versuchen Sie, where-Klausel in einer Unterabfrage, um ihn zu zwingen, das zu tun, erste? 

    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 das könnte besser funktionieren, wenn Sie erwarten, dass eine kleine Anzahl von Proben

    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'
)
    • 1. es ist bereits bedeckt mit index. Probleme in der ERGEBNIS-Tabelle, nicht in STICHPROBE 2. 800 vs 40 (1. und 2. Abfrage entsprechend). wenn ich geben Sie jedem Feld anders zu FÜHREN.TEST_NUMBER (WÄHLEN SIE ERGEBNIS.NAMEN AUS ...) zum Beispiel - ich bekomme eine langsame Abfragen und planen Sie mit clustered index scan
    • diese Abfrage erhalten die gleichen Ergebnisse (langsam auch). aber das ist langsam auch für 10-12 Stunden Intervall.
    • Überprüfen Sie die Größe der Zeile, siehe oben. Auch schauen, um zu sehen, wenn es große Mengen von Daten, die in der 9 Uhr-10 Uhr Zeitrahmen.
    • die Letzte Abfrage wird nicht helfen, nur weil, wie ich sagte, das Problem im ERGEBNIS, nicht in einer PROBE
    InformationsquelleAutor Robert Wagner
  3. 0

    Wenn Sie eine SELECT * Sie möchten, dass alle Daten aus der Tabelle. Die Daten für die Tabelle ist im clustered-index - leaf-nodes des clustered index sind die Daten Seiten.

    Also, wenn Sie wollen, dass alle diejenigen, die Daten-Seiten sowieso, und da sind Sie Beitritt 1 mio. Zeilen 11 mio. Zeilen (1 von 11 ist nicht sehr selektiv für SQL-Server), wird ein index verwendet, um Zeilen zu suchen, und führen Sie dann bookmark lookups in die eigentlichen Datenseiten für jede dieser Zeilen gefunden, vielleicht einfach nicht sehr effizient, und damit die von SQL Server verwendet die " clustered index scan statt.

    Also um eine lange Geschichte kurz zu machen: wählen Sie nur die Zeilen, die Sie wirklich brauchen! Sie geben damit SQL Server die chance zu nutzen, einen index, ein suchen und finden der notwendigen Daten.

    Wenn Sie nur drei, vier Spalten, dann sind die Chancen, dass SQL Server finden und einen index verwenden, der enthält die Spalten sind einfach so viel höher als wenn Sie Fragen, für die Daten aus allen beteiligten Tabellen.

    Andere Möglichkeit wäre, zu versuchen und einen Weg finden, um Ausdrücken eine Unterabfrage, beispielsweise mit einer Common Table Expression, würde greifen Daten ab, die beiden kleineren Tische, und verringern die Anzahl der Zeilen noch mehr, und nehmen Sie die hoffentlich Recht klein Ergebnis gegen die Haupt-Tabelle. Wenn Sie eine kleine Ergebnis-nur 40, - oder 800-Ergebnisse (anstatt zwei Tabellen mit 1 mio. Zeilen jeweils), dann SQL Server möglicherweise eher geneigt sein, ein Clustered Index Seek und tun bookmark lookups auf 40 oder 800 Zeilen, anstatt einen vollständigen Scan des Gruppierten Indexes ausführen.

    • "Wenn Sie So wollen, alle diese Daten Seiten sowieso, und da sind Sie Beitritt 1 mio. Zeilen 11 mio. die Zeilen" ich bin bei etwa 20 Zeilen (Datumsbereich-Grenzen dieser) der 11M Zeilen mit index. wie auch immer, das nicht erklärt, warum die wechselnden Angebot ein wenig verpasst plan. warum sql kann der index für die 20 Zeilen und nicht auf 800?
    • "Also um eine lange Geschichte kurz zu machen: wählen Sie nur die Zeilen, die Sie wirklich brauchen! Sie geben damit SQL Server die chance zu nutzen, einen index, ein suchen und finden der notwendigen Daten." Also, wenn ich aufzählen, alle 20 Felder, die ich brauche, aus der ERGEBNIS-Tabelle, welche änderungen sollten gemacht werden mit dem schema?
    • ja, der Unterschied zwischen der Auswahl von 20 oder 800 Zeilen können eine große Unterschied, wie der Abfrageoptimierer seine plan! Vielleicht, bis zu 150 Zeilen, dabei wird die bookmark-lookups wird schneller sein als die "clustered index scan", und dann die Waage-Tipps. Auch mit up-to-date Statistiken können einen großen Unterschied machen - versuchen, zu aktualisieren Sie Ihre Statistiken für die Tabellen und Indizes und sich als erste Maßnahme
    • yep, scan auf 11M ist natürlich schneller als die index-Suche auf 800 Zeilen... (ironisch gemeint)... wie gesagt bei UPD3 ja - ich brauche ein paar Statistiken (oder etwas, das service-Befehl anderes) aktualisieren. kann aber nicht google es 🙁
    InformationsquelleAutor marc_s
Schreibe einen Kommentar