Optymalizacja zapytań dla ponad 25 milionów wierszy

Korzystam z MS SQL i muszę uruchomić kilka zapytań w tej samej tabeli według różnych kryteriów. Najpierw uruchomiłem każde zapytanie w oryginalnej tabeli, chociaż wszystkie mają wspólne filtrowanie (tj. Data, status). Zajęło to dużo czasu (około 2 minut).

W wierszach danych znajdują się duplikaty, a wszystkie indeksy NIE są klastrowane. Interesują mnie tylko 4 kolumny dla moich kryteriów, a wynik powinien wypisać tylko liczbę dla wszystkich zapytań.

kolumny potrzebne: TABLE, FIELD, AFTER, DATE, i nie ma indeks na każdej z DATEi TABLE.

Po utworzeniu tabeli tymczasowej zawierającej tylko pola, których potrzebuję, spadła do 1:40 minut, co nadal jest bardzo złe.

CREATE TABLE #TEMP
(
    TABLE VARCHAR(30) NULL,
    FIELD VARCHAR(30) NULL,
    AFTER VARCHAR(1000) NULL,
    DATE DATETIME,
    SORT_ID INT IDENTITY(1,1)
)
CREATE CLUSTERED INDEX IX_ADT ON #TEMP(SORT_ID)

INSERT INTO #TEMP (TABLE, FIELD, AFTER, DATE)
    SELECT TABLE, FIELD, AFTER, DATE 
    FROM mytbl WITH (NOLOCK)
    WHERE TABLE = 'OTB' AND
    FIELD = 'STATUS'

Uruchom to -> (dotyczy 216598 wierszy)

Ponieważ nie wszystkie zapytania opierają się na zakresie dat, nie uwzględniłem go w zapytaniu. Problem polega na tym, że wkładanie zajmuje znacznie ponad 1 minutę . Powyższa wkładka zajęła 1:19 minut

Chcę uruchomić coś takiego dla kilku zapytań:

SELECT COUNT(*) AS COUNT
FROM #TEMP
WHERE AFTER = 'R' AND
DATE >= '2014-01-01' AND
DATE <= '2015-01-01'

Problem z wstawką jest większy niż w przypadku zaznaczenia, ale temp ma o wiele mniej wierszy niż oryginalna tabela, co może być lepsze niż wielokrotne przeglądanie tabeli.

Jak mogę to zoptymalizować?

EDYTOWAĆ

Usunąłem identyfikator sortowania, myślałem, że problem dotyczy głównie wyboru, a nie wstawiania. To było zgadywanie.

Nie mogę utworzyć unikalnego dla żadnego indeksu, ponieważ nie ma unikalnego pola ani wierszy.

Korzystam z programu SQL Server 2012.

Informacje o tabeli : Jest stertą i ma następujące użycie miejsca:

name    rows        reserved    data        index_size  unused
mytbl   24869658    9204568 KB  3017952 KB  5816232 KB  370384 KB

Pytanie dotyczy głównie sposobu optymalizacji instrukcji select:

SELECT [TABLE], [FIELD], [AFTER], [DATE]
FROM mytbl WITH (NOLOCK)
WHERE [TABLE] = 'OTB' AND
[FIELD] = 'STATUS'

Usuwanie niepotrzebnych projekcji i dodawanie założonego dboschematu:

SELECT [AFTER], [DATE] 
FROM dbo.mytbl WITH (NOLOCK)
WHERE [TABLE] = 'OTB'
AND FIELD = 'STATUS';

Bez indeksu takiego jak ([TABLE],[FIELD]) INCLUDE ([AFTER],[DATE])SQL Server ma dwie główne opcje:

1. Skanuj stertę całkowicie (3 GB +); lub
2. Znajdź wiersze pasujące [TABLE] = 'OTB'i [FIELD] = 'STATUS'(za pomocą IDX6), a następnie wykonaj wyszukiwanie stosu (RID) dla każdego wiersza, aby pobrać kolumny [AFTER]i [DATE].

To, czy optymalizator wybierze skanowanie sterty lub wyszukiwanie indeksu z wyszukiwaniem RID, zależy od szacunkowej selektywności [TABLE] = 'OTB'i [FIELD] = 'STATUS'predykatów. Sprawdź, czy szacunkowa liczba wierszy z wyszukiwania jest zgodna z rzeczywistością. Jeśli nie, zaktualizuj swoje statystyki. Przetestuj zapytanie za pomocą podpowiedzi w tabeli wymuszającej użycie indeksu, jeśli warunek ten jest względnie selektywny . Jeśli optymalizator obecnie wybiera wyszukiwanie indeksu, przetestuj wydajność za pomocą podpowiedzi INDEX(0)lub, FORCESCANaby przeskanować stertę.

Poza tym możesz nieco poprawić skanowanie sterty, usuwając część nieużywanego miejsca (370 MB). W SQL Server 2008 można to zrobić, odbudowując stertę. Niewykorzystane miejsce w stosach często wynika z operacji usuwania przeprowadzonych bez blokady tabeli (bez blokady tabeli puste strony nie są zwalniane ze stosu). Z tego powodu tabele, które często się usuwają, są często lepiej przechowywane jako tabela klastrowa.

Wydajność skanowania stosu zależy od ilości tabeli przechowywanej w pamięci, ilości danych, które należy odczytać z dysku, liczby stron, szybkości trwałego przechowywania, tego, czy skanowanie jest związane z operacjami we / wy, czy procesorem ( równoległość może pomóc).

Jeśli wydajność jest nadal nie do zaakceptowania po zbadaniu wszystkich powyższych kwestii, spróbuj uzasadnić nowy indeks. Jeśli dostępny w Twojej wersji SQL Server, możliwy filtrowany indeks dla podanego zapytania będzie:

CREATE INDEX index_name
ON dbo.mytbl ([DATE],[AFTER])
WHERE [TABLE] = 'OTB'
AND [FIELD] = 'STATUS';

Zastanów się także nad kompresją indeksu, jeśli jest to dostępne i korzystne. Bez nowego indeksu niewiele można zrobić, aby poprawić wydajność danego zapytania.

Myślę, że istnieje potrzeba zmiany tutaj indeksów, ponieważ:

• masz zadanie do wykonania (te wielokrotne zapytania)
• woluminy hurtowni danych (ponad 25 milionów wierszy) i
• problem z wydajnością.

Byłby to również dobry przypadek użycia dla nieklastrowanych indeksów magazynu kolumn wprowadzonych w SQL Server 2012, tj. Podsumowania / agregacji kilku kolumn w dużej tabeli z wieloma kolumnami.

Chociaż indeksy te powodują efekt uboczny polegający na tym, że tabela jest tylko do odczytu (z wyjątkiem przełączania partycji), mogą transformować wydajność agregowanych zapytań we właściwych warunkach. Aspektem tylko do odczytu można zarządzać, upuszczając i ponownie tworząc indeks lub dane z prostej zmiany partycji w tabeli.

Ustawiłem prosty zestaw testowy, aby naśladować twoją konfigurację, i zauważyłem dobrą poprawę wydajności:

USE tempdb
GO

SET NOCOUNT ON
GO

-- Create a large table
IF OBJECT_ID('dbo.largeTable') IS NOT NULL
DROP TABLE dbo.largeTable
GO
CREATE TABLE dbo.largeTable ( 

    [TABLE] VARCHAR(30) NULL,
    FIELD VARCHAR(30) NULL,
    [AFTER] VARCHAR(1000) NULL,
    [DATE] DATETIME,
    SORT_ID INT IDENTITY(1,1),

    pad VARCHAR(100) DEFAULT REPLICATE( '$', 100 )
)
GO

-- Populate table
;WITH cte AS (
SELECT TOP 100000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.largeTable ( [TABLE], FIELD, [AFTER], [DATE] )
SELECT 
    x.tableName, 
    y.field,
    z.[after],
    DATEADD( day, rn % 1111, '1 Jan 2012' )
FROM cte c
    CROSS JOIN ( VALUES ( 'OTB' ), ( 'AAA' ), ( 'BBB' ), ( 'CCCC' ) ) x ( tableName )
    CROSS JOIN ( VALUES ( 'STATUS' ), ( 'TIME' ), ( 'POWER' ) ) y ( field )
    CROSS JOIN ( VALUES ( 'R' ), ( 'X' ), ( 'Z' ), ( 'A' ) ) z ( [after] )

CHECKPOINT

GO 5

EXEC sp_spaceused 'dbo.largeTable'
GO

SELECT MIN([DATE]) xmin, MAX([DATE]) xmax, FORMAT( COUNT(*), '#,#' ) records
FROM dbo.largeTable
GO

-- Optionally clear cache for more comparable results; DO NOT RUN ON PRODUCTION SYSTEM!!
--DBCC DROPCLEANBUFFERS
--DBCC FREEPROCCACHE
--GO

DECLARE @startDate DATETIME2 = SYSUTCDATETIME()

SELECT COUNT(*) AS COUNT
FROM dbo.largeTable
WHERE [AFTER] = 'R' 
  AND [DATE] >= '2014-01-01' 
  AND [DATE] <= '2015-01-01'

SELECT DATEDIFF( millisecond, @startDate, SYSUTCDATETIME() ) diff1
GO

-- Add the non-clustered columnstore
CREATE NONCLUSTERED COLUMNSTORE INDEX _cs ON dbo.largeTable ( [TABLE], FIELD, [AFTER], [DATE] )
GO

-- Optionally clear cache for more comparable results; DO NOT RUN ON PRODUCTION SYSTEM!!
--DBCC DROPCLEANBUFFERS
--DBCC FREEPROCCACHE
--GO

-- Check query again
DECLARE @startDate DATETIME2 = SYSUTCDATETIME()

SELECT COUNT(*) AS COUNT
FROM dbo.largeTable
WHERE [AFTER] = 'R' 
  AND [DATE] >= '2014-01-01' 
  AND [DATE] <= '2015-01-01'

SELECT DATEDIFF( millisecond, @startDate, SYSUTCDATETIME() ) diff2
GO

Moje wyniki, 6 sekund w 0,08 sekundy:

Podsumowując, spróbuj zbudować sprawę ze swoim szefem, aby zmienić indeksy lub przynajmniej stworzyć pewnego rodzaju nocny proces, w którym rekordy te zostaną wyryte w tabeli / bazie danych raportowania tylko do odczytu, gdzie możesz wykonać swoją pracę, i dodaj indeksowanie odpowiednie dla tego obciążenia pracą.