[php][mysql]"array_diff" dla dużej liczby rekordów - algorytm działania Opcje

[sabat24]

W bazie danych trzymam 250tys. produktów, które zostały pobrane z zewnętrznego serwera. Raz dziennie muszę sprawdzać, czy nie pojawiły się w ofercie nowe produkty. Zewnętrzny serwer podsyła mi dane w pakietach po 100 produktów. Każdy produkt posiada unikalne ID (zarówno zewnętrzne jak i wewnętrzne). W przypadku, gdyby tych produktów było niewiele, wystarczyłoby pobrać 100 ID z zewnętrznego serwera do tablicy A, produkty z bazy danych do tablicy B i prostą funkcją znaleźć różnice w dwóch tablicach. Problemem w moim wypadku jest dość znaczna wielkość danych oraz serwowanie ich w pakietach od strony zewnętrznego serwera. Mógłby mi ktoś podpowiedzieć, jak najlepiej rozwiązać ten problem? Co rozumiem przez najlepiej, wyjaśnię pod koniec. Moje warianty rozwiązań znajdują się poniżej (jest to to, co samemu udało mi się wymyślić na szybko - proszę się tym nie sugerować zupełnie):
1. Pobrać pakiet 100 produktów z serwera, sprawdzić SELECT ... IN, które mam w bazie danych i dodać te, których nie mam. Wady: 2500 zapytań SELECT ... IN, które chyba niezbyt dobrze radzi sobie z indeksami (chodzi mi o klauzulę IN).
2. W zasadzie wariant powyższy, ale złączyć kilka pakietów z serwera zewnętrznego i dopiero wysłać zapytanie do bazy. Co prawda liczba zapytań maleje, ale zwiększa się zakres w IN.
3. Pobrać 250tys. produktów z serwera zewnętrznego, posortować po ID i zapisać do pliku np. XML albo txt. Pobrać 250tys. produktów z bazy danych, posortować po tym samym ID i zapisać do pliku. Odczytywać to sekwencyjnie i porównywać na bieżąco. Wady: nie wiem, czy nie zajadę dysku przy tak dużym odczycie i zapisie danych na dysk oraz czy wystarczy pamięci, by posortować po ID dane z serwera zewnętrznego.

Najlepsze rozwiązanie dla mnie to takie, które w dość rozsądny sposób będzie używało zasoby serwera. Sama szybkość działania nie jest tak istotna, jak zasobożerność serwera, gdyż całość pewnie będzie na hostingu.

Ten post edytował sabat24 6.03.2012, 19:20:37

[toffiak]

Zastanawiałeś się nad zewnętrzna aplikacją, taką która pobierze wszystkie dane o produktach,połączy się z bazą danych twojego serwisu i wyszuka zmiany a następnie uaktualni bazę na serwerze. Takie rozwiązanie może być lepsze i szybsze niż przerabianie tego w php, nawet gdy całość będzie aktualizowała bazę cyklicznie na przykład za pomocą crona.

Napisałeś o hostingu, na współdzielonym takie zabawy szybko wychwytują admini a potem są problemy.

Ten post edytował toffiak 6.03.2012, 19:48:42

[sabat24]

Na tym etapie wolałbym wykluczyć zewnętrzne aplikacje. Jak na razie biorę pod uwagę opcję CRON + PHP wywoływane co jakiś czas. Ważne, aby cała operacja zamknęła się w sumie 24h. W przypadku mojego wariantu pierwszego, byłoby to 100 zapytań na godzinę, co raczej nie powinno zbyt mocno obciążyć serwera. Gdyby jednak żadna firma hostująca nie zgodziła się na działanie takiego skryptu, w chwili obecnej bardziej prawdopodobne jest postawienie tego na dedyku, niż zewnętrzna aplikacja.

[irmidjusz]

A może wrzucić dane wszystkich produktów (pobieranych w paczkach z zewnętrznego serwera) do osobnej tabeli i dopiero wtedy wykonać jedno duże zapytanie do bazy znajdujące w tej tabeli produkty, których nie ma w tabeli głównej?

Jeśli serwer nie wyrobi z takim zapytaniem (ale to trzeba przetestować!), to można by robić porównania z tabelą główną w paczkach, ale nie po 100 produktów (bo bardzo dużo zapytań), tylko np. po 1000 czy 10000 - trzeba by to eksperymentalnie ustalić, jaki jest czas wykonywania oraz zasobożerność. Pytań mogło by być znacznie mniej z zachowaniem ich niewielkiego (akceptowalnego) kosztu wykonania. Sprawdzona paczka wierszy w każdej iteracji może być np. usuwana.

Zakładam, że przechowujesz zewnętrzne ID produktów w tabeli głównej, więc takie zapytania powinny być szybkie i proste, szczególnie, jeśli jest to indeks.

Napisz jak się z tym już uporasz jak rozwiązałeś problem i jak to działa

[sabat24]

Zrobiłem tak, jak radziłeś, czyli stworzyłem tymczasową tabelę na najnowsze dane, po czym wykonałem zapytanie kopiujące odpowiednie dane pomiędzy tabelami. Jak na razie testowałem to na stosunkowo niewielkiej liczbie rekordów, więc jak to działa finalnie na paru milionach rekordów, wypowiem się za kilka dni. Zasada działania jest taka:
1. łączone są trzy kolejne paczki po 100 produktów (więcej nie zmieści się w pojedynczym INSERCIE) i dodawane do tabeli tymczasowej
2. gdy w tabeli tymczasowej mam 4000 (wartość jak na razie przyjęta z góry) produktów, są one kopiowane poniższym zapytaniem do tabeli głównej, a tabela tymczasowa jest czyszczona (TRUNCATE)

Gdyby ktoś potrzebował, użyłem zapytania:

[SQL] pobierz, plaintext 
INSERT INTO tabela_glowna (lista_pol) SELECT lista_pol FROM tabela_tymczasowa WHERE NOT EXISTS (SELECT pole_z_indeksem FROM tabela_glowna WHERE tabela_glowna.unikalne_ID = tabela_tymczasowa.unikalne_ID)
[SQL] pobierz, plaintext 

unikalne_ID - są to pola, zawierające te same ID produktu nadane przez zewnętrzny serwer i mają ustawiony index w bazie.

Ten post edytował sabat24 7.03.2012, 17:12:26

[crocodillo]

[SQL] pobierz, plaintext 
INSERT INTO tabela_glowna (lista_pol) SELECT lista_pol FROM tabela_tymczasowa WHERE NOT EXISTS (SELECT pole_z_indeksem FROM tabela_glowna WHERE tabela_glowna.unikalne_ID = tabela_tymczasowa.unikalne_ID)
[SQL] pobierz, plaintext 

Lepiej chyba będzie użyć INSERT IGNORE