wie die Struktur eines index für group by in Sql Server

Die folgende einfache Abfrage dauert sehr lange (mehrere Minuten) zu führen.

Habe ich einen index:

create index IX on [fctWMAUA] (SourceSystemKey, AsAtDateKey)

SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
FROM [fctWMAUA] (NOLOCK) AS [t0]
WHERE SourceSystemKey in (1,2,3,4,5,6,7,8,9)
GROUP BY [t0].[SourceSystemKey]

Die Statistiken sind wie folgt:

logische Lesevorgänge 1827978
physische Lesevorgänge 1113
read-aheads 1806459

Nehmen, dass genau die gleiche Abfrage und Formatierung wie folgt gibt mir diese Statistik:

logische Lesevorgänge 36
physische Lesevorgänge 0
read-aheads 0

Es dauert 31ms zu führen.

SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
 FROM [fctWMAUA] (NOLOCK) AS [t0]
 WHERE SourceSystemKey = 1
 GROUP BY [t0].[SourceSystemKey]
UNION
 SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
 FROM [fctWMAUA] (NOLOCK) AS [t0]
 WHERE SourceSystemKey = 2
 GROUP BY [t0].[SourceSystemKey]
UNION
 SELECT MAX([t0].[AsAtDateKey]) AS [Date], [t0].[SourceSystemKey] AS [SourceSystem]
 FROM [fctWMAUA] (NOLOCK) AS [t0]
 WHERE SourceSystemKey = 3
 GROUP BY [t0].[SourceSystemKey]
/* AND SO ON TO 9 */

Wie mache ich einen index hat, der die Gruppe durch schnell?

Hast du einen index auf SourceSystemKey ? Wenn nicht, ich denke, Sie werden möglicherweise induziert einen full table scan.
Was bedeutet die showplan-show? und welche Werte können SourceSystemKey nehmen?

InformationsquelleAutor Craig | 2009-11-04

1

Versuchen, zu erklären, SQL Server verwenden den index:
```
...
FROM [fctWMAUA] (NOLOCK, INDEX(IX)) AS [t0]
...
```
Stellen Sie sicher, dass die Statistiken für die Tabelle werden bis zu Datum:
```
UPDATE STATISTICS [fctWMAUA]
```
Für bessere Antworten, schalten Sie die showplan für beide Abfragen:
```
SET SHOWPLAN_TEXT ON
```
und fügen Sie die Ergebnisse zu Ihrer Frage.

Können Sie auch schreiben Sie die Abfrage ohne GROUP BY. Zum Beispiel können Sie eine exklusive LEFT JOIN ausschließen von Zeilen mit älteren Termin:
```
select cur.SourceSystemKey, cur.date
from fctWMAUA cur
left join fctWMAUA next
    on next.SourceSystemKey = next.SourceSystemKey
    and next.date > cur.date
where next.SourceSystemKey is null
and cur.SourceSystemKey in (1,2,3,4,5,6,7,8,9)
```
Dieser kann überraschend schnell, aber ich glaube nicht, dass es gegen die UNION.
- Versucht, alle Ihre Vorschläge. Immer noch sehr langsam. Union ist immer noch schnell. <pre> |--Stream-Aggregat(GRUPPE DURCH:([t0].[SourceSystemKey]) DEFINIEREN:([Expr1003]=MAX([partialagg1004]))) |--Parallelität(Gather Streams -, ORDER BY:([t0].[SourceSystemKey] ASC)) |--Stream-Aggregat(GRUPPE DURCH:([t0].[SourceSystemKey]) DEFINIEREN:([partialagg1004]=MAX([DRACHEN].[dbo].[fctWMAUA].[AsAtDateKey] as [t0].[AsAtDateKey]))) |--Index Seek(OBJECT:([DRACHEN].[dbo].[fctWMAUA].[IX_AsAtDateSourceSystem] AS [t0]), SEEK:([t0].[SourceSystemKey] >= (1) UND [t0].[SourceSystemKey] <= (9)) ORDERED FORWARD) </pre>
- Ich habe auch die Reihenfolge der Felder im index, und es ändert sich nicht.
- Blick auf den plan, die es irgendwie Sinn macht. Das ursprüngliche suchen, ist gehen, um herauszufinden, alle Datensätze. Gibt es nur noch neun-source-Systeme, und es sucht die Menge.
- Tut hinzufügen " - OPTION (HASH GROUP)" oder "OPTION" (ORDNUNG der GRUPPE)" am Ende der Abfrage einen Unterschied machen?
- Hi Andomar, Guter Vorschlag. Die hash-Gruppe gebracht hat es, bis auf etwa fünfzehn Sekunden, das ist akzeptabel, wenn ich cache die Ergebnisse. Es ist immer noch seltsam, dass ich bekommen kann 32ms von der union version und nichts in der Nähe auf der group by-version. Die union version ist ein suchen und ein top(1) für jede der Abfragen, die ist super schnell. Der index kann nicht scheinen, um das Duplikat.
- Eine Letzte trick ist eine exklusive join (Hinzugefügt, um die Antwort)
- Ich strukturierte die Abfrage, um die Abfrage der Quell-Systeme und führen Sie dann eine innere Abfrage, die zu jedem der Termine max. Diese verwendet den index perfekt, und dauert etwa 7ms. Viel schneller als Gruppe durch, und auch schneller als die union. wählen Sie SourceSystems.SourceSystemKey , (select max(AsAtDateKey) von fctWMAUA wo fctWMAUA.SourceSystemKey = SourceSystems.SourceSystemKey Gruppe von fctWMAUA.SourceSystemKey) MaxData aus SourceSystems
- es funktioniert aber warum
InformationsquelleAutor Andomar

Habe ich festgestellt, dass die beste Lösung ist die folgende.
Es ahmt die union version der Abfrage, und läuft sehr schnell.

40 logische Lesevorgänge und eine Ausführungszeit von 3ms.

SELECT [t3].[value]
FROM [dimSourceSystem] AS [t0]
OUTER APPLY (
    SELECT MAX([t2].[value]) AS [value]
    FROM (
        SELECT [t1].[AsAtDateKey] AS [value], [t1].[SourceSystemKey]
        FROM [fctWMAUA] AS [t1]
        ) AS [t2]
    WHERE [t2].[SourceSystemKey] = ([t0].[SourceSystemKey])
    ) AS [t3]

InformationsquelleAutor Craig

2

Ist es schwer zu sagen, ohne Blick auf den Ausführungsplan, jedoch möchten Sie vielleicht versuchen, die folgenden:
```
SELECT * FROM
(
    SELECT MAX(t0.AsAtDateKey) AS [Date], t0.SourceSystemKey AS SourceSystem
    FROM fctWMAUA (NOLOCK) AS t0
    GROUP BY t0.SourceSystemKey
)
WHERE SourceSystem in (1,2,3,4,5,6,7,8,9)
```
Ist es schwer zu sagen, ohne Blick auf den Ausführungsplan, aber ich denke, dass was passiert ist, dass der SQL-server ist nicht klug genug, um zu begreifen, dass die WHERE-Klausel angegeben, ist die Filterung aus den Gruppen und haben keinen Einfluss auf die Datensätze enthalten für jede Gruppe. Sobald SQL server erkennt, dass seine frei, um etwas mehr inteligente index-lookups, um die maximalen Werte (das ist, was passiert in Ihrem zweiten Abfrage)

Nur eine Theorie, aber es könnte einen Versuch Wert sein.

InformationsquelleAutor Justin
1

Nutzen, die statt, WO, so dass die Filterung geschieht NACH der Gruppierung aufgetreten:
```
SELECT MAX(AsAtDateKey) AS [Date], SourceSystemKey AS SourceSystem
FROM fctWMAUA (NOLOCK)
GROUP BY SourceSystemKey
HAVING SourceSystemKey in (1,2,3,4,5,6,7,8,9)
```
Ich auch nicht besonders Pflege für die IN-Klausel, vor allem, wenn es ersetzt werden könnte durch "<10" oder "Zwischen 1 und 9", die verwendet werden, besser sortiert Indizes.

InformationsquelleAutor BradC
0
```
 WHERE SourceSystemKey = 3
 GROUP BY [t0].[SourceSystemKey]
```
Brauchen Sie nicht zu Gruppe, um einen festen Bereich.

Möglichkeit, die ich lieber den ersten Satz. Kann ich ersetzen die
```
 WHERE SourceSystemKey in (1,2,3,4,5,6,7,8,9)
```
etwas wie
```
 WHERE SourceSystemKey BETWEEN 1 AND 9
```
oder
```
 WHERE SourceSystemKey >= 1 AND SourceSystemKey <= 9
```
wenn SourceSystemKey ist eine ganze Zahl. Aber ich glaube nicht, dass es zu einer großen Veränderung.

Was ich zuerst testen, ist die Neuerstellung von Statistiken und erstellen Sie Indizes für die Tabelle und warten Sie einige Zeit. Umbau kommt nicht sofort, es wird davon abhängen, wie ausgelastet ist der server aber dieser Satz ist gut strukturiert, die für den index vom Optimierer verwendet werden.

Grüße.
- Was meinst du mit "Sie brauchen nicht zu der Gruppe, die von einem festen Feld" ? Er sucht für das maximale Datum.
- Ich habe versucht, die zwischen-und es hat sich daran nichts ändern. Es ist die index, und der erste index suchen zurück 665 Millionen Zeilen. Mit der union sucht man Reihe (top 1) für jeweils max richtig sortiert, und seine " super-schnell. Ohne die union es will 665 Millionen Zeilen und durchläuft die Menge. Es ist verrückt. Beide queries definitiv mit den gleichen Indizes im plan.
- Andomar: ich Sprach über die GROUP BY-weil, wenn man "WO SourceSystemKey = 3" ich sehe keinen Sinn in der "GROUP BY SourceSystemKey", denn es gibt nur eine SourceSystemKey. Es gibt nichts zu Gruppe, Sie sind auf der Suche nach dem absoluten MAX-Wert, dass die WHERE-filter. Aber jeder Weg der optimizer kennt es und sollte nicht das problem sein. (edit: Sprechen wir über den zweiten Befehl. Im ersten Fall ist die GRUPPE, INDEM Sie OK ist, natürlich)
- SQL Server erfordert eine GRUPPE DURCH, bevor Sie können Aggregate wie MAX()
- Es ist nicht erforderlich in diesem Szenario. Im Allgemeinen ist es nicht erforderlich, wenn Sie nicht brauchen, um Daten gruppieren. Versuchen Sie einfach (zum Beispiel): select max(object_id) aus sys.Tabellen where name like '%A%' Dies funktioniert in SQL Server 2005.
- Du hast Recht, die Gruppe ist nicht erforderlich, die union version der Abfrage. Ich war mit kopieren/einfügen der Abfrage. Es hat keinen Einfluss auf die Abfrage-plan, wenn.
- Danke, das spart mir eine Menge Tipparbeit!
InformationsquelleAutor j.a.estevan
0

Haben Sie versucht, erstellen Sie einen weiteren index nur auf die SourceSystemKey Spalte? Die hohe Anzahl logischer Lesevorgänge wenn man diese Spalte in der where-Klausel lässt mich denken, dass es eine index/table scan. Könnten Sie ausführen, wird der Ausführungsplan auf diese und sehen, ob das der Fall ist? Der Ausführungsplan kommen könnte mit einen index-Vorschlag als gut.

InformationsquelleAutor Nir

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.