SQL: wewnętrzne połączenie dwóch masywnych tabel

Nie jestem ekspertem od dostrajania SQL, ale łączenie setek milionów wierszy w polu VARCHAR nie brzmi jak dobry pomysł w żadnym znanym mi systemie bazodanowym.

Możesz spróbować dodać kolumnę całkowitą do każdej tabeli i obliczyć hash w polu Nazwa, który powinien uzyskać możliwe dopasowania do rozsądnej liczby, zanim silnik będzie musiał spojrzeć na rzeczywiste dane VARCHAR.

Może trochę offtopic, ale: "Zauważyłem, że komputer czasami zacina się co 30 sekund (mniej więcej) na kilka sekund."

To zachowanie jest charakterystyczne dla taniej macierzy RAID5 (a może dla pojedynczego dysku) podczas kopiowania (a twoje zapytanie najczęściej kopiuje dane) gigabajtów informacji.

Więcej o problemie - nie możesz podzielić zapytania na mniejsze bloki? Jak Nazwy zaczynające się od A, B itp lub ID w określonych zakresach? Może to znacznie zmniejszyć transakcyjne / blokujące.

Po pierwsze, 100-metrowe połączenia nie są wcale nierozsądne ani rzadkie.

Jednak podejrzewam, że przyczyna słabych wyników, które widzisz, może być związana z klauzulą INTO. Dzięki temu nie tylko robisz połączenie, ale także piszesz wyniki do nowej tabeli. twoja obserwacja na temat tak ogromnego pliku dziennika jest zasadniczo potwierdzeniem tego.

Jedna rzecz, aby spróbować: usunąć INTO i zobaczyć, jak to działa. Jeśli wykonanie jest rozsądne, to aby zwrócić się do powolny zapis należy upewnić się, że plik dziennika DB Znajduje się na oddzielnym woluminie fizycznym od danych. Jeśli nie jest, głowice dysków będą tracić (wiele poszukiwań) podczas odczytu danych i zapisu dziennika, a Twój perf upadnie(prawdopodobnie do 1/40 do 1/60 tego, co mogłoby być inaczej).

Spróbowałbym może usunąć operator 'LIKE', ponieważ nie wydaje się, że robisz jakieś dopasowanie wieloznaczne.

Zgodnie z zaleceniami, chciałbym hashować nazwę, aby połączenie bardziej rozsądne. Zdecydowanie rozważyłbym zbadanie przypisania identyfikatora podczas importu partii za pomocą wyszukiwania, jeśli jest to możliwe, ponieważ wyeliminowałoby to konieczność późniejszego łączenia (i potencjalnie wielokrotnego wykonywania takiego nieefektywnego łączenia).

Widzę, że masz ten indeks na TypeID - to bardzo by pomogło, gdyby był w ogóle selektywny. Dodatkowo dodaj kolumnę z Hashem nazwy do ten sam indeks:

SELECT EGM.Name
       ,BioEntity.BioEntityId
INTO AUX 
FROM EGM 
INNER JOIN BioEntity  
    ON EGM.TypeId = BioEntity.TypeId -- Hopefully a good index
    AND EGM.NameHash = BioEntity.NameHash -- Should be a very selective index now
    AND EGM.name LIKE BioEntity.Name

Kolejna sugestia, którą mogę zaoferować, to próba uzyskania podzbioru danych zamiast przetwarzania wszystkich wierszy 100 M na raz, aby dostroić zapytanie. W ten sposób nie musisz spędzać tyle czasu na czekaniu, aby zobaczyć, kiedy zakończy się Twoje zapytanie. Następnie możesz rozważyć sprawdzenie planu wykonania zapytania, który może również zapewnić pewien wgląd w problem.

100 milionów płyt to ogromna ilość. Powiedziałbym, że do pracy z tak dużą bazą danych potrzebujesz dedykowanego serwera testowego. Używanie tej samej maszyny do wykonywania innej pracy podczas wykonywania takich zapytań nie jest praktyczne.

Twój sprzęt jest w miarę sprawny, ale żeby tak duży sprzęt działał przyzwoicie potrzebowałbyś jeszcze większej mocy. Czterordzeniowy system z 8GB byłby dobrym początkiem. Poza tym musisz upewnić się, że Twoje indeksy są prawidłowo skonfigurowane.

Czy masz jakieś podstawowe klucze lub indeksy? czy można go wybrać etapami? tzn. gdzie nazwa jak ' a%', gdzie nazwa jak ' B%', itd.

Ręcznie ustawiłem niektóre indeksy; zarówno EGM, jak i BioEntity miały nieklastrowany Indeks obejmujący zawierający TypeId i nazwę. Jednak zapytanie trwało pięć dni i również się nie skończyło, więc próbowałem uruchomić Database Tuning Advisor, aby to zadziałało. Zasugerował usunięcie moich starszych indeksów i utworzenie statystyk i dwóch grupowanych indeksów (po jednym na każdej tabeli, zawierającym tylko TypeId, który uważam za dość dziwny-lub po prostu głupi - ale dałem mu szansę w każdym razie).

Powiedziałeś, że stworzyłeś klastrowy indeks TypeId w obu tabelach, chociaż wygląda na to, że masz już klucz podstawowy w każdej tabeli(odpowiednio Bioentitiid i EGMId). Nie chcesz, aby Twój TypeId był klastrowym indeksem na tych tabelach. Chcesz, aby Bioentitiid i EGMId były grupowane (które fizycznie posortują Twoje dane w kolejności klastrowego indeksu na dysku. Chcesz nie-klastrowych indeksów na obcych kluczach, których będziesz używać do wyszukiwania. Tj. TypeId. Spróbuj utworzyć klastry kluczy podstawowych i dodać indeks nieklastrowany w obu tabelach, który zawiera tylko TypeId.

W naszym środowisku mamy tabele, które wynoszą około 10-20 milionów rekordów na sztukę. Wykonujemy wiele zapytań podobnych do Twoich, gdzie łączymy dwa zestawy danych na jednej lub dwóch kolumnach. Dodanie indeksu dla KAŻDEGO klucza obcego powinno bardzo pomóc w twojej wydajności.

Proszę pamiętać, że przy 100 milionach rekordów te indeksy będą aby wymagać dużo miejsca na dysku. Wydaje się jednak, że wydajność jest tutaj kluczowa, więc warto.

K. Scott ma całkiem dobry artykuł tutaj , który wyjaśnia niektóre kwestie bardziej dogłębnie.

Powtórzę tu kilka wcześniejszych postów (na które zagłosuję)...

Jak selektywny jest TypeId? Jeśli masz tylko 5, 10 lub nawet 100 różnych wartości w wierszach 100m+, indeks nie robi nic dla Ciebie-zwłaszcza, że i tak zaznaczasz wszystkie wiersze.

Sugerowałbym utworzenie kolumny na sumy kontrolnej (nazwa) w obu tabelach wydaje się być dobre. Być może niech będzie to stała kolumna obliczeniowa:

CREATE TABLE BioEntity
 (
   BioEntityId  int
  ,Name         nvarchar(4000)
  ,TypeId       int
  ,NameLookup  AS checksum(Name) persisted
 )

A następnie utworzyć indeks w ten sposób (użyłbym clustered, ale nawet nonclustered pomogłoby): {]}

CREATE clustered INDEX IX_BioEntity__Lookup on BioEntity (NameLookup, TypeId)

(Sprawdź BOL, istnieją zasady i ograniczenia dotyczące budowania indeksów na kolumnach obliczeniowych, które mogą mieć zastosowanie do Twojego środowiska.)

Zrobione na obu tabelach, powinno to zapewnić bardzo selektywny indeks obsługujący Twoje zapytanie, jeśli zostanie zmienione w ten sposób:

SELECT EGM.Name, BioEntity.BioEntityId INTO AUX
 FROM EGM INNER JOIN BioEntity 
 ON EGM.NameLookup = BioEntity.NameLookup
  and EGM.name = BioEntity.Name
  and EGM.TypeId = BioEntity.TypeId

W zależności od wielu czynników nadal będzie działać długo (nie tylko dlatego, że kopiujesz ile danych do nowej tabeli?), ale powinno to potrwać krócej niż dni.

Dlaczego nvarchar? Najlepszą praktyką jest, jeśli nie potrzebujesz (lub spodziewasz się potrzebować) obsługi unicode, po prostu użyj varchar. Jeśli uważasz, że najdłuższa nazwa jest poniżej 200 znaków, zrobiłbym tę kolumnę varchar(255). Widzę scenariusze, w których haszowanie, które zostało Ci polecone, byłoby kosztowne(wygląda na to, że ta baza danych jest Intensive insert). Jednak przy tak dużym rozmiarze oraz częstotliwości i losowym charakterze nazw Twoje indeksy szybko ulegną fragmentacji w większości scenariuszy, w których indeksujesz na hash (zależny od hasha) lub nazwę.

Zmieniłbym kolumnę nazw w sposób opisany powyżej i sprawił, że klastrowany indeks TypeId, EGMId/Bioentitiid (klucz zastępczy dla każdej tabeli). Następnie możesz dołączyć ładnie na TypeId, a "szorstki" join na Name będzie miał mniej do pętli. Aby sprawdzić, jak długo to zapytanie może działać, spróbuj go dla bardzo małego podzbioru identyfikatorów typów, co powinno dać Ci oszacowanie czasu wykonania (chociaż może ignorować czynniki takie jak rozmiar pamięci podręcznej, wielkość pamięci, szybkość transferu dysku twardego).

Edit: jeśli jest to trwający proces, powinieneś wprowadzić ograniczenie klucza obcego między dwoma tabelami dla przyszłych importów / zrzutów. Jeśli nie trwa, haszowanie jest prawdopodobnie najlepszym rozwiązaniem.

Spróbowałbym rozwiązać problem nieszablonowo, może jest jakiś inny algorytm, który mógłby wykonać zadanie znacznie lepiej i szybciej niż baza danych. Oczywiście wszystko zależy od charakteru danych, ale istnieją pewne algorytmy wyszukiwania ciągów, które są dość szybkie( Boyer-Moore, ZBox itp.), lub inny algorytm datamining (MapReduce ?) Dzięki starannemu opracowaniu eksportu danych możliwe byłoby zgięcie problemu w celu dopasowania go do bardziej eleganckiego i szybszego rozwiązania. Ponadto, może być możliwe, aby lepiej zrównoleglaj problem i z prostym klientem wykorzystaj cykle bezczynności systemów wokół ciebie, istnieją ramy, które mogą w tym pomóc.

Wynikiem tego może być lista krotek refid, których można użyć do szybszego pobierania pełnych danych z bazy danych.

To nie przeszkadza w eksperymentowaniu z indeksem, ale jeśli musisz czekać 6 dni na wyniki, myślę, że uzasadnia to zasoby spędzone na odkrywaniu innych możliwych opcji.

Moje 2 cent

Ponieważ nie prosisz DB o wykonywanie jakichkolwiek fantazyjnych operacji relacyjnych, możesz łatwo to skryptować. Zamiast zabijać DB potężnym, ale prostym zapytaniem, spróbuj wyeksportować dwie tabele (czy możesz uzyskać kopie offline z kopii zapasowych?).

Po wyeksportowaniu tabel, napisz skrypt, aby wykonać to proste połączenie za Ciebie. Wykonanie zajmie mniej więcej tyle samo czasu, ale nie zabije DB.

Ze względu na wielkość danych i czas trwania zapytania do uruchom, nie będziesz to robić zbyt często, więc proces wsadowy offline ma sens.

Dla skryptu, będziesz chciał indeksować większy zbiór danych, a następnie iterację przez mniejszy zbiór danych i szukać w indeksie dużego zbioru danych. Będzie O(n*m) do uruchomienia.

Jeśli dopasowanie hash zużywa zbyt wiele zasobów, wykonaj zapytanie w partiach, powiedzmy, 10000 wierszy na raz, "przechodząc" kolumnę TypeID. Nie powiedziałeś o selektywności TypeID, ale prawdopodobnie jest on wystarczająco selektywny, aby móc wykonywać partie tak małe i całkowicie pokrywać jeden lub więcej TypeID na raz. Szukasz również połączeń pętlowych w swoich partiach, więc jeśli nadal masz połączenia hashowe, Wymuś połączenia pętli lub Zmniejsz rozmiar partii.

Korzystanie z partii będzie również, w prosty tryb odzyskiwania, utrzymuj dziennik tran z rosnących bardzo duże. Nawet w prostym trybie odzyskiwania, ogromne połączenie, takie jak robisz, pochłonie mnóstwo miejsca, ponieważ musi zachować całą transakcję otwartą, podczas gdy podczas wykonywania partii może ponownie użyć pliku dziennika dla każdej partii, ograniczając jego rozmiar do największego potrzebnego do jednej operacji wsadowej.

Jeśli naprawdę potrzebujesz dołączyć do nazwy, możesz rozważyć kilka tabel pomocniczych, które konwertują nazwy na identyfikatory, zasadniczo naprawiając denormalizowany projekt tymczasowo (jeśli nie możesz naprawić go na stałe).

Pomysł na sumę kontrolną też może być dobry, ale sam w to nie grałem.

W każdym razie tak wielki hash match nie będzie tak dobry, jak szeregowe połączenia pętli. Gdybyś mógł dostać połączenie scalające, byłoby super...

Zastanawiam się, czy czas wykonania jest brany przez join czy przez transfer danych.

Zakłada się, że średni rozmiar danych w kolumnie Name wynosi 150 znaków, w rzeczywistości będziesz miał 300 bajtów plus inne kolumny na rekord. Pomnóż to przez 100 milionów rekordów, a otrzymasz około 30 GB danych do przesłania do klienta. Czy uruchamiasz klienta zdalnego lub na samym serwerze ? Może czekasz aż 30GB danych zostanie przesłane do twojego klienta...

EDIT: Ok, widzę, że jesteś wstawianie do tabeli Aux. Jakie jest ustawienie modelu odzyskiwania bazy danych?

Aby zbadać wąskie gardło Po Stronie sprzętu, może być interesujące, czy ograniczającym zasobem jest odczyt danych lub zapis danych. Możesz rozpocząć uruchamianie Monitora wydajności systemu windows i rejestrować na przykład długość kolejek do odczytu i zapisu dysków.

Idealnie, powinieneś umieścić plik dziennika db, tabele wejściowe i tabelę wyjściową na oddzielnych woluminach fizycznych aby zwiększyć prędkość.