Chciałbym poprosić o sugestie / pomysły jak można podejść do poniższego problemu. Chodzi o sugestie narzędzi, rozwiązań, algorytmów... Cokolwiek.
Problem jest "ogólnie łatwy" ale złożony obliczeniowo, a zależy mi na maksymalnej optymalizacji czasowej wykonywania tych obliczeń.
Załóżmy, że mamy przykładową tabelę TABLE o strukturze ( wszystkie pola to integer ):
Z tabeli chcemy wyciągnąć pewne dane spełniające warunki wynikające z zapytania ( niekoniecznie zapytania SQL ):
Niech przykładową strukturę zapytania określa kilka poniższych zmiennych ( pseudokod PHP ):
$D1 = 100 ( wartości od 0 to 10000 )
$D2 = 250 ( wartości od 0 to 10000 )
$PL = array[ 1, 3, 5, 7 ] // w tablicy może być od 1 do 56 elementów o wartościach 0 - 100
$sumFrom = 2000 ( wartości od 0 to 1000000 )
$sumTo = 5000 ( wartości od 0 to 1000000 )
Na potrzeby przykładu załóżmy, też że przykładowa tabela to klasyczna tabela w bazie SQL:
Aby uzyskać pożądany wynik najpierw wybieramy rekordy spełniające łatwiejszą część warunku:
Select * from TABLE whwre D1 >= 100 and D2 <= 250 and CN in ( 1, 3, 5, 7 )
W wyniku wykonania zapytania pozostaną nam dwa rekordy o id=1,2
Teraz dla każdego z rekordów, które spełniają powyższy warunek musimy wykonać dodatkowe obliczenia, których wynik uzależniony jest od zawartości tablicy $PL i pewnej zależności wartości tablicy z kolumną TABLE.KA.
Te obliczenia mogą wyglądać np. tak:
function calcRow( $PL, $sumFrom, $sumTo, $tableRow ){
if ( count ( $PL ) > tableRow.RA ) return -1 ;
if ( count ( $PL ) <= tableRow.RA ) {
$sum = 0 ;
foreach ( $PL as item ){
if ( item < tableRow.KA )
$sum = $sum + tableRow.CA1
else
$sum = $sum + tableRow.CC2
}
}
if ( $sum < $sumFrom ) || ( $sum > $sumTo ) return -1 ;
return ( $sum );
}
Gdzie leży problem? No kilka jest...
- W praktyce kolumn jest około 50 a baza ma 500 000 000 rekordów.
- Odpowiedzi na zadane pytania musimy uzyskiwać w czasie poniżej 100ms.
- Baza jest modyfikowana "na bieżąco" rekordy dochodzą, są modyfikowane lub kasowane.
Generalnie problem mam rozwiązany na 3 sposoby:
0. Wszystko na MySQL wyszukiwane SQL z funkcją składowaną. Działało to strasznie...
- Mix SQL + PHP ale to dział sensownie jedynie do 1000 000 rekordów ( nieco lepiej niż czysty SQL );
- Aplikacja (skompilowanwa), w której dane siedzą w RAM, wszystkie dane są poindeksowane posortowane itp..., a same obliczenia są liczone na bieżąco. Na 100 000 000 rekordów działa to fajnie i mieści się w wymaganych czasach ale pożera ponad 30GB RAM i jest czasochłonny problem z aktualizacją danych bo trzeba aktualizować indeksy i wykonywać ponowne sortowania.
Każdy ma jednak swoje wady. Jak można to zrobić inaczej?