Aktualizacja tabeli milionami rekordów, minęły 4 dni

Obecnie aktualizuję tabelę z milionami rekordów, minęły 4 dni, a zapytanie jest nadal wykonywane.

Sprawdziłem, czy monitor aktywności pokazuje, czy zapytanie jest uruchomione.

W dzienniku zdarzeń nie ma żadnych błędów.

Pod względem wydajności:

Tempdb na dysku A (850 GB wolnego miejsca)
plik bazy danych na dysku B (750 GB wolnego miejsca)
16 GB pamięci RAM

Proszę zasugerować mi, co powinienem zrobić?

Zapytanie

UPDATE
    dbo.table1
SET 
    costPercentage = ISNULL(t2.PaymentIndex, 1.0),
    t2.TopUp_Amt = (ISNULL(t2.PaymentIndex, 1.0) - 1.0)
    * ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00),
    Total_Tariff_Inc_t2 = ISNULL(t2.PaymentIndex, 1.0)
    * ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00)
FROM
    dbo.table2 t2
WHERE
    LEFT(dbo.test1.procodet, 3) = LEFT(t2.ProviderCode, 3) COLLATE database_default

sql-server sql-server-2008

— Szczęściarz
źródło

Odpowiedzi:

W tym zapytaniu jest interesujący szczegół, którego nie zauważyłem na początku. Dzięki odpowiedzi Fabricio Araujo widzę ją teraz: uzyskujesz dostęp do dwóch tabel. Nigdy wcześniej nie widziałem takiego użycia instrukcji aktualizacji i nie radzę jej używać. Zalecam stosowanie bardziej intuicyjnej składni łączenia dla odpowiedzi Fabricio.

Prawdopodobną przyczyną jest to, że połączenie między dwiema tabelami generuje ogromną liczbę wierszy. Może się to zdarzyć, jeśli LEFT(col, 3)wyrażenie tworzy zduplikowane wartości. Jeśli wyprodukuje 10 duplikatów, spowoduje to 100000 x 100000 = 10000000000 wierszy w wyniku łączenia.

Nie sądzę, że indeksowanie odgrywa tutaj rolę. SQL Server może rozwiązać to nierozłączone połączenie w porządku za pomocą skrótu lub połączenia scalającego. Nie zajmuje 4 dni.

Inną prawdopodobnie przyczyną byłoby niedoszacowanie liczności wejściowych lub wyjściowych złączeń. Program SQL Server mógł wybrać połączenie pętli.

Ponieważ nadal są to spekulacje, zalecam opublikowanie planu zapytań, który rzuci światło na ten problem.

— usr
źródło

To zapytanie wymaga zeskanowania każdego wiersza w tabeli, ponieważ

Myślę, że procodet lub ProviderCode nie są indeksowane
Nawet jeśli zostały zindeksowane, masz LEWĄ, która jest funkcją w predykacie GDZIE
I masz też COLLATE, co jest efektywną funkcją w predykacie GDZIE

„funkcja w predykacie GDZIE” oznacza, że indeksy nie będą używane

Jeśli to zrobisz wsadowo (powiedzmy na UPDATE TOP (10000) ... ORAZ CostPercentage IS NULL), potrzebujesz indeksu na costPercentage i zakładasz, że go ustawiasz.

Jedyne rozwiązania, jakie widzę, to

wypełnij nową tabelę partiami, na podstawie, powiedzmy, klucza podstawowego
utwórz indeksowane kolumny obliczeniowe, aby ukryć wyrażenia LEWE i ZGŁOSZENIE, a następnie uruchom aktualizację

— gbn
źródło

@ gbn .. dzięki, to świetny pomysł .. ale ponieważ dane są w milionach, proces ten zajmie trochę czasu ... Myślałem, że może być sposób na sprawdzenie postępu zapytania?

— szczęście

Dlaczego skanowanie „milionów” wierszy zajmuje 4 dni? Bez względu na to, jak duże i mocno zindeksowane mogą być wiersze, nie powinno to zająć 4 dni. Przyczyna problemu jest nadal nieznana.

— usr

Jeśli regularnie masz do czynienia z dużymi danymi, to co z tego, że masz do tego odpowiedni serwer? Umieść dane na dysku SSD itp.

— TomTom

@Na szczęście. Odpowiadałem na odpowiedź. Coś jest nie tak, czego jeszcze nie znaleźliśmy. To nie jest zapytanie samo w sobie ani sprzęt. To nigdy nie potrwa 4 dni.

— usr

Biorąc pod uwagę, że zapytanie łączy 3-znakową część kolumny z 3-znakową częścią innej kolumny, wynik najprawdopodobniej będzie zawierał duplikaty. Jest to o wiele gorsze niż zwykłe aktualizowanie milionów wierszy. Założę się, że to przeglądanie stołu roboczego w miliardach.

— datagod

Przede wszystkim zmień zapytanie na:

UPDATE t1
SET 
    costPercentage = ISNULL(t2.PaymentIndex, 1.0),
    t2.TopUp_Amt = (ISNULL(t2.PaymentIndex, 1.0) - 1.0)
    * ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00),
    Total_Tariff_Inc_t2 = ISNULL(t2.PaymentIndex, 1.0)
    * ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00)
FROM
  dbo.table1 t1
  inner join dbo.table2 t2
    on LEFT(t1.procodet, 3) = LEFT(t2.ProviderCode, 3) COLLATE database_default

Jak wskazał pierwszy post Jeffa Modena w tej dyskusji , twoje zapytanie jest bardzo podobne do tego, które ostrzegał przed „efektem Halloween”.

Następnie te LEWE wyrażenia muszą zostać zindeksowane. Odpowiedź gbn daje wskazówki, jak to zrobić.

— Fabricio Araujo
źródło