Przerzedzanie wyników na serwerze, wyciąganie co któregoś rekordu Opcje

[ky3orr]

Witam wszystkich!

zmagam sie z problemem pobierania danych do wykresu.
Chodzi o to, że użytkownik ma mozliwośc podania zakresu dat za jaki okres chce zobaczyć wykres.
Danych za ten okres moze byc kilka, a może być 100milionów.
Zakładam, że komponent wykresowy akceptuje powiedzmy 10000 punktów i tu pytanie:
-czy mozna skonstruować takie zapytanie, aby w przypoadku gdy liczba rekordów wyniku była większa niż 100tysięcy to wtedy wyciągnie tylko co niektóre (kwestia algorytmu wyliczającego co ile punktów pobierać rekord do wyniku - np. jakiś mod) tak, żeby tabela końcowa wracająca z serwera do aplikacji miała nie wiecej niż te 100tysięcy wierszy?

data zawarta w rekordach jest typu double (zmodyfikowana data julianska MJD).

jesli ktoś bawił się z takim czymś to chetnie usłyszę którędy droga

pozdrawiam

Ten post edytował ky3orr 29.03.2009, 01:56:47

[erix]

Danych za ten okres moze byc kilka, a może być 100milionów.

Może where na resztę z dzielenia przez jakis warunek?

tak, żeby tabela końcowa wracająca z serwera do aplikacji miała nie wiecej niż te 100tysięcy wierszy?

No to pozostaje chyba generowanie liczb losowych w zależności od liczby; order by rand() jest zbyt czasochłonne.

[nospor]

najpierw zliczasz ile masz wszystkich wierszy (zwykly select count(*))
jesli malo, to generujesz zapytanie co pobiera wszystkie
jesli duzo to generujesz zapytanie co pobiera co n-ty. tu masz przyklad na takie zapytanie:
http://nospor.pl/mysql-faq-n25.html#faq-7

[maly_swd]

nospor->Malo optymalnie:

wystarczy zwykle zapytanie select * from stats order by date
sprawdzenie ile zwrocilo rekordow: mysql_num_rows($uchwyt_do_zapytania)

pozniej sprawdzasz if($ilosc_rekordow>100000) $step=round($iloscrekordow/100000); else $step=1;

i na koniec w while($dane=mysq_fetch_array( $uchwyt_do_zapytania)){

mysql_data_seek($uchwyt_do_zapytania, $pozycja+$step);
$pozycja=$pozycja+$step;

}

* to jest tylko szkic logiczny ale przedstawia co trzeba uzyc i jak

[erix]

~maly_swd, niby czemu? Twój przykład zeżre dużo więcej zasobów (przede wszystkim pamięci) niż ~nospora.

[nospor]

nospor->Malo optymalnie:

yyy, opierasz swoją teorie na doswiadczeniach? To szybko lec i poprawiaj kody, które do tej pory napisales

[piotrooo89]

~maly_swd stworzyłeś jakiegoś potworka pamięciożernego. nie ma nic lepszego od przerzucenia liczenia na serwer SQL.

[ky3orr]

nospor dzięki za linka.
teraz muszę rozkminić jak to działa, ale jest trop

jak sobie poradzę z tematem przedstawię receptę w wątku.

pozdrawiam

Ten post edytował ky3orr 30.03.2009, 22:12:47

[maly_swd]

Twoj sposob jest dobry pod warunkiem ze znamy STEP.

*tak moj przyklad jest poparty doswiadczeniem i testami na tabelach po 10-20milionow rekordow

[nospor]

Twoj sposob jest dobry pod warunkiem ze znamy STEP.

No bo przeciez znamy. Jesli masz x rekordow a chcesz sobie wyswietlic z nich tylko y gdzie y < x to wyliczenie step to podstawa matematyki ktorej w podstawowce uczą

*tak moj przyklad jest poparty doswiadczeniem i testami na tabelach po 10-20milionow rekordow

Chcesz policzyc ile rekordow ma baaaardzo duza tabela. Ta bardzo duuuza tabela ma 20mln rekordow i ty w tym celu pobierasz z bazy danych wszystkie 20 mln rekordow..... mistrzu, jestes wielki.... tak samo wielki jak optymalny twoj skrypt

edit:
wykonaj sobie najpierw taki kod

[PHP] pobierz, plaintext 
<?php
$sql = 'select * from twojabardzoduzatabela';
$res = mysql_query($sql) or die(mysql_error());
$count = mysql_num_rows($res);
echo $count;
?>
[PHP] pobierz, plaintext 

zmierz ile skrypt zajął pamięci, jak długo sie wykonywał

następnie wykonaj taki kod

[PHP] pobierz, plaintext 
<?php
$sql = 'select count(*) from twojabardzoduzatabela';
$res = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($res);
$count = $row[0];
echo $count;
?>
[PHP] pobierz, plaintext 

zmierz ile skrypt zajął pamięci, jak długo sie wykonywał

porównaj uzyskane wyniki ze sobą i przestan wkońcu wypisywac glupoty....

ps: twojabardzoduzatabela to tabela, która ma te pare mln rekordów.

[ky3orr]

nospor czy, aby wykonać ten zestaw poleceń:

SET @nr=-1, @coile=5;
select * from (
select @nr:=@nr+1 _nr,tabela.* from tabela
) jakisalias where _nr % @coile = 0;

trzeba zamknąć je w jakąś procedurę czy funkcję?

próbowałem przetestować to w SQL Query Browserze z pakietu MySQL GUI Tools i klumna która powinna być numerowana zwróciła mi NULLe.
podejżewam, że linia z setem i zapytanie wykonały sie niezależnie...

pozdrawiam

Ten post edytował ky3orr 31.03.2009, 09:53:04

[nospor]

SET to jedno zapytanie
kolejne linie to następne jedno zapytanie. One powinny byc wykonane jedno po drugim.
W php bedą to poprostu dwa mysql_query() po sobie.

Nie wiem jak to realizuje twoj SQL Query Browser
Przykladowo sqlyog ktorego uzywam ma opcje wykonywania zapytan jako calosci i dziala ok.

[dr_bonzo]

@ky3orr: Query Browser pod tym wzgledem to gowno, nie potrafi wykonac kilku SQLek oddzielonych srednikiem, wiec musisz wrzucac zapytania (dwa) pojedynczo do JEDNEGO taba.
Uzyj phpMyAdmin - tam takie cos dziala - wrzuc wszystkie zapytania od razu.

[maly_swd]

nospor-> widze ze nie zrozumiales tego co napisalem (szkoda). Jest tam napisane ze jest to szkic. W przypadku jak nie znamy ile baza ma rekordow (nie wykonujemy wczesniej select count(*)...

tylko mamy np select id, text, pole from tabela where pole='cos' (bo takie wyniki chcemy dostac) i wyciagnac z tego 120 rekordow (nie piwrwszych - tylko z STEP x)

Takie zapytanie wykona sie szybko i nie pobierasz wszystkich rekordow do PHP. Druga sprawa ze taki wynik jest cachowany przez mysqla.

[erix]

Druga sprawa ze taki wynik jest cachowany przez mysqla.

No właśnie. CAŁY WYNIK, a nie tylko liczba rekordów. Poza tym, wykonanie dodatkowego zapytania nieraz się bardziej opłaca.

A jeśli już chcesz na upartego liczyć wyciągnięte:

sprawdzenie ile zwrocilo rekordow: mysql_num_rows($uchwyt_do_zapytania)

Użycie tej funkcji jest odradzane, poczytaj trochę, to porozmawiamy. Jeśli już chcesz tak liczyć rekordy, to poszukaj o SQL_CALC_FOUND_ROWS.

[nospor]

nospor-> widze ze nie zrozumiales tego co napisalem (szkoda).

No sorki, ale napisales dosc wyraźnie:

wystarczy zwykle zapytanie select * from stats order by date
sprawdzenie ile zwrocilo rekordow: mysql_num_rows($uchwyt_do_zapytania)

Inaczej tego raczej rozumiec nie mozna

pozniej sprawdzasz if($ilosc_rekordow>100000) $step=round($iloscrekordow/100000); else $step=1;

i na koniec w while($dane=mysq_fetch_array( $uchwyt_do_zapytania)){

mysql_data_seek($uchwyt_do_zapytania, $pozycja+$step);
$pozycja=$pozycja+$step;

Przeciez ty wtym przykladzie ewidentnie pobierasz z mysql wszystkie 20mln rekordow a dopiero potem stepem w php pobierasz tylko co ktorys.
Zorientowales sie dopiero teraz ze sie myliles i starasz sie odwrocic kota ogonem choc i to ci nie wychodzi.

weź wykonaj te dwa przyklady co ci podalem pare postow wczesniej to moze wkoncu do ciebie dotrze w jak wielkim bledzie jestes
Jesli tego nie zrobisz, dalsza dyskusja wydaje sie raczej zbedna

i wyciagnac z tego 120 rekordow

zauwaz ze my tu mowimy o tabeli ktora ma liczbę rekordow liczoną w milionach i chcemy z tego wyciągnac 100tys a nie nedzne 120 rekordów

[ky3orr]

Koledzy!

nie było moją intencją skłócić kogokolwiek zakładając tgen temat, jednak czytając cały topic wiele można sobie uświadomić o istocie pamięciożerności przez tgen czy tantan styl zapytań.

Puki co staram się ujarzmić kilka zapytań jednocześnie

pozdrawiam

[rzymek01]

maly_swd, a czy czasem mysql_num_rows nie przelatuje wszystkiego jeszcze raz?

[nospor]

Puki co staram się ujarzmić kilka zapytań jednocześnie

Z racji, ze przeprowadzilem sobie wewnetrzny test, to masz juz gotowy kod:

[PHP] pobierz, plaintext 
<?php
$time1 = microtime(true);
$sql = 'select count(*) from city_city';
$res = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($res);
$count = $row[0];
 
$coIle = round($count/100000);
echo 'Ilosc wszystkich rekordow:'.$count;
echo '<br />Co ile:'.$coIle;
mysql_query('SET @nr=-1, @coile='.$coIle);
$res = mysql_query('select *
from (select
        @nr:=@nr+1    _nr,
        city_city.*
      from city_city) jakisalias
where _nr % @coile = 0') or die(mysql_error());
$i=0;
while ($row = mysql_fetch_array($res)){
//tu cos sobie rob z rekordem
$i++;
}
echo '<br />Ilosc przetworzonych rekordow:'.$i;
echo '<br />Czas '.(microtime(true) - $time1);
?>
[PHP] pobierz, plaintext 

A oto wynik:
Ilosc wszystkich rekordow:6359961
Co ile:64
Ilosc przetworzonych rekordow:99375
Czas 7.34749293327

A tutaj kod w wersji maly_swd:

[PHP] pobierz, plaintext 
<?php
$time1 = microtime(true);
$res = mysql_query('select * from city_city') or die(mysql_error());
$count = mysql_num_rows($res);
$coIle = round($count/100000);
echo 'Ilosc wszystkich rekordow:'.$count;
echo '<br />Co ile:'.$coIle;
 
$poz = 0;
$i=0;
while ($row = mysql_fetch_array($res)){
$poz+=$coIle;
if (!mysql_data_seek($res,$poz)) break;
$i++;
}
 
echo '<br />Ilosc przetworzonych rekordow:'.$i;
echo '<br />Czas '.(microtime(true) - $time1);
?>
[PHP] pobierz, plaintext 

I wynik:
Ilosc wszystkich rekordow:6359961
Co ile:64
Fatal error: Maximum execution time of 990 seconds exceeded


Jak widac tabela ma ponad 6 mln rekordow.

Dla mojej wersji skrypt wykonywał sie 7 sekund. Procesy systemowe w normie, podkoczyl jedynie troche proces mysql ale nie zuzywal wcale wiecej pamieci niz normalnie.

Dla wersji malego skrypt wykonywal sie koszmarnie dlugo. Proces apachea wskoczyl mi na maxa i zajął ponad 300MB pamieci.
Dodatkowo nie doczekalem sie na wynik. Musialem sztucznie zwiększyc $coIle na 20000 by doczekac sie wyniku. Oto on:
Ilosc wszystkich rekordow:6359961
Co ile:20000
Ilosc przetworzonych rekordow:317
Czas 20.1107699871

PRzykladowo moj skrypt dla tej wartosci $coIle uzyskal nastepujace wyniki
Ilosc wszystkich rekordow:6359961
Co ile:20000
Ilosc przetworzonych rekordow:318
Czas 7.14381098747


Wnioski: no chyba nie musze mowic....

@maly_swd jesli naprawde myslales o innym rozwiązaniu to prosze podaj je dokladnie. Jesli jednak nie, to moze wkoncu przyjmiesz fakty do wiadomosci.

edit:
a, i jeszcze wyniki moich wczesniej podanych skryptow dla tej samej tabeli z 6 mln rekordow

czas dla select count(*): 0.00517010688782
czas dla mysql_num_rows: 6.46986794472

no comments....

[piotrooo89]

robiąć tak jak napisał ~maly_swd można w piękny sposób zajechać PHP. i nawet przy tych nędznych 120 rekordach będzie widoczna różnica. jak że sprawa wymaga testów przeprowadziłem takowe, wyniki:

[PHP] pobierz, plaintext 
<?php
include ('mysql.inc');
$sql = 'select * from termin';
$res = mysql_query($sql) or die(mysql_error());
$count = mysql_num_rows($res);
$czas1 = microtime();
echo $czas1;
echo '</br>';
 
$sql1 = 'select count(*) from termin';
$res1 = mysql_query($sql1) or die(mysql_error());
$row1 = mysql_fetch_array($res1);
$count1 = $row1[0];
$czas2 = microtime();
echo $czas2;
echo '</br>';
echo $czas2-$czas1;
?>
[PHP] pobierz, plaintext 

i wyniki:

Kod

0.56250300 1238533233
0.56390500 1238533233
0.001402

testowane na 222 rekordach przy 100 tyś będzie proporcjonalnie więcej...