Prośba o pomoc w optymalizacji skryptu - import CSV do MySQL


(northwest) #1

Witam serdecznie,

Chciałbym prosić o pomoc w optymalizacji poniższego skryptu:

$filename = "baza_ksiazek.csv";

        $handle = fopen("$filename", "r");

        $ilosc = 0;

        while (($data = fgetcsv($handle, 2000, ",")) !== FALSE) {

            $result = mysql_query("SELECT * FROM cms_produkty WHERE isbn = '$data[1]';", $connection);

            $ilosc_wierszy = mysql_num_rows($result);

            if ($ilosc_wierszy == 0) {

                if ($data['1'] != "") {

                    $nazwakategorii = null;

                    $nazwapodkategorii = null;

                    $idkategorii = null;

                    $idpodkategorii = null;

                    $nazwakategorii = $data['12'];

                    $nazwapodkategorii = $data['13'];


                    if ($nazwakategorii != "") {

                        $result2 = mysql_query("SELECT * FROM cms_kategorie WHERE nazwa = '$nazwakategorii';");

                        if (mysql_num_rows($result2) == 0) {

                            mysql_query("INSERT INTO cms_kategorie (nazwa, enable) VALUES ('$nazwakategorii', '1');");

                            $wynik = mysql_query("SELECT bf_id FROM cms_kategorie WHERE nazwa = '$nazwakategorii' ORDER by bf_id DESC LIMIT 1;;");

                            while ($wartt2 = mysql_fetch_array($wynik)) {

                                $idkategorii = $wartt2['bf_id'];

                            }

                        } else { 

                            $wynik = mysql_query("SELECT bf_id FROM cms_kategorie WHERE nazwa = '$nazwakategorii' ORDER by bf_id DESC LIMIT 1;;");

                            while ($wartt2 = mysql_fetch_array($wynik)) {

                                $idkategorii = $wartt2['bf_id'];

                            }

                        }

                    }


                    //echo $idkategorii."

";


                    if ($nazwapodkategorii != "") {

                        $result3 = mysql_query("SELECT * FROM cms_podkategorie WHERE nazwa = '$nazwapodkategorii';");

                        if (mysql_num_rows($result3) == 0) {

                            mysql_query("INSERT INTO cms_podkategorie (nazwa, enable, kategoria) VALUES ('$nazwapodkategorii', '1', '$idkategorii');");

                            //echo "INSERT INTO cms_podkategorie (nazwa, enable, kategoria) VALUES ('$nazwapodkategorii', '1', '$idkategorii');";

                            $wynik = mysql_query("SELECT bf_id FROM cms_podkategorie WHERE nazwa = '$nazwapodkategorii' ORDER by bf_id DESC LIMIT 1;;");

                            while ($wartt2 = mysql_fetch_array($wynik)) {

                                $idpodkategorii = $wartt2['bf_id'];

                            }

                        } else { 

                            $wynik = mysql_query("SELECT bf_id FROM cms_podkategorie WHERE nazwa = '$nazwapodkategorii' ORDER by bf_id DESC LIMIT 1;;");

                            while ($wartt2 = mysql_fetch_array($wynik)) {

                                $idpodkategorii = $wartt2['bf_id'];

                            }

                        }

                    }

                    $save = "INSERT INTO cms_produkty (nazwa, obrazek, isbn, cena, autor, wydawca, strony, opis, rok, podatek, cena2, kategoria, grupa, typrekordu, enable) VALUES ('$data[3]', '$data[15]', '$data[1]', '$data[11]', '$data[4]', '$data[5]', '$data[6]', '$data[8]', '$data[7]', '$data[9]', '$data[10]', '$idkategorii', '$idpodkategorii', '1', '1');";

                    //echo $save."

";

                    $save = iconv('ISO-8859-2', 'UTF-8', $save);

                    mysql_query("$save");

                    //$ilosc = $ilosc + 1;

                    if ($ilosc == 100) {

                        die();

                    }

                }

            }

        }

Skrypt ma działać w następujący sposób:

  1. sprawdzić czy dany rekord już istnieje w bazie

  2. sprawdzić czy kategoria jest już w bazie - jeśli nie, to dopisać

  3. sprawdzić czy podkategoria jest już w bazie - jeśli nie, to dopisać

  4. jeśli produktu nie ma w bazie - to go dopisujemy....

Skrypt działa... Ale prawdopodobnie da się go bardzo zoptymalizować pod względem wydajności.... :slight_smile:

Czy mógłbym prosić o pomoc?


(ra-v) #2

1.

while (($data = fgetcsv($handle, 2000, ",")) !== FALSE) {$data[1]) ){ [/code]
... bo i po co robić zapytanie jeśli nie ma ISBN
2.
[code=php]$result = mysql_query("SELECT `jakieś_pole_najlepiej_primary_key` FROM cms_produkty WHERE isbn = '".mysql_real_escape_string($data[1])."' LIMIT 1;", $connection); 

Powinno chodzić szybciej (pobiera tylko jedno pole) i być bezpieczniejsze. 
mysql_real_escape_string() warto używać dla łańcuchów znaków w zapytaniach SQL, żeby nikt przypadkowo lub celowo nie wysadził bazy lub serwera.

Popraw zapytania z SELECT * - nie warto pobierać wszystkich pól jeśli nie trzeba. Jeśli chcesz sprawdzić czy choć 1 rekord istnieje, to możesz dodawać LIMIT 1 - wtedy nie będzie przeszukiwał całej bazy, a przerwie przeszukiwania po pierwszym znalezionym rekordzie.

Jeśli chodzi o użycie mysql_fetch_array() jest czasem to nie ma sensu - zajmuje z 2x więcej pamięci, z której wykorzystujesz połowę. Użyj mysql_fetch_assoc() i odwołuj się po nazwie pół ($zmienna['pole']) albo mysql_fetch_row() i odwołuj się po indeksie liczbowym ($zmienna[0]).

To tak na 1. rzut oka;-)

(Grzelix) #3

Jeśli chodzi o optymalizacje wydajności, to jako jedno z rozwiązań sugeruje przeniesienie części logiki do samej bazy danych.

Utwórz procedure składowaną, dzięki czemu dla jednego rekordu z pliku csv zredukujesz liczbę zapytań do bazy z 7 (jak dobrze liczę) do 1. Dodatkowo silnik bazodanowy powinie efektywniej wykonać podane tutaj operacje.

Ogólne uwagi do kodu:

  1. Staraj się używać języka angielskiego przy nazywaniu zmiennych (ogólnie nie mieszaj języków, konwencji im szybciej zaczniesz tym lepiej dla Ciebie i dla nas też - kod jest dużo bardziej czytelny)

  2. Staraj się używać nazw zmiennych określających ich przeznaczenie np; zamiast $result3 $subcategoryFromDb

  3. Tutaj nie jestem pewien ale wydaje mi się że zapytanie INSERT zwraca id wstawionego rekordu, nie ma potrzeby ponownego selecta - sprawdź doc (lub http://stackoverflow.com/questions/8973 ... hen-get-id)


(ra-v) #4

Teoretycznie powinien, ale w praktyce w MySQL często szybciej działa 10 prostszych zapytań niż 1 większe.


(northwest) #5

Witam serdecznie,

Teraz mam problem z zaimportowaniem 70MB pliku CSV do MySQL.

Mam następujący skrypt:

$filename = "baza_ksiazek.csv";

        $handle = fopen("$filename", "r");

        $ilosc = 0;

        while (($data = fgetcsv($handle, 2000, ",")) !== FALSE) {

            $result = mysql_query("SELECT bf_id FROM cms_produkty WHERE isbn = '" . mysql_real_escape_string($data[1]) . "' LIMIT 1;", $connection);

            $ilosc_wierszy = mysql_num_rows($result);

            if ($ilosc_wierszy == 0) {

                if ($data['1'] != "") {

                    $nazwakategorii = null;

                    $nazwapodkategorii = null;

                    $idkategorii = null;

                    $idpodkategorii = null;

                    $nazwakategorii = $data['12'];

                    $nazwapodkategorii = $data['13'];


                    if ($nazwakategorii != "") {

                        $result2 = mysql_query("SELECT * FROM cms_kategorie WHERE nazwa = '$nazwakategorii';");

                        if (mysql_num_rows($result2) == 0) {

                            mysql_query("INSERT INTO cms_kategorie (nazwa, enable) VALUES ('$nazwakategorii', '1');");

                            $wynik = mysql_query("SELECT bf_id FROM cms_kategorie WHERE nazwa = '$nazwakategorii' ORDER by bf_id DESC LIMIT 1;;");

                            while ($wartt2 = mysql_fetch_assoc($wynik)) {

                                $idkategorii = $wartt2['bf_id'];

                            }

                        } else {

                            $wynik = mysql_query("SELECT bf_id FROM cms_kategorie WHERE nazwa = '$nazwakategorii' ORDER by bf_id DESC LIMIT 1;;");

                            while ($wartt2 = mysql_fetch_assoc($wynik)) {

                                $idkategorii = $wartt2['bf_id'];

                            }

                        }

                    }


                    //echo $idkategorii."

";


                    if ($nazwapodkategorii != "") {

                        $result3 = mysql_query("SELECT bf_id FROM cms_podkategorie WHERE nazwa = '$nazwapodkategorii' LIMIT 1;");

                        if (mysql_num_rows($result3) == 0) {

                            mysql_query("INSERT INTO cms_podkategorie (nazwa, enable, kategoria) VALUES ('$nazwapodkategorii', '1', '$idkategorii');");

                            //echo "INSERT INTO cms_podkategorie (nazwa, enable, kategoria) VALUES ('$nazwapodkategorii', '1', '$idkategorii');";

                            $wynik = mysql_query("SELECT bf_id FROM cms_podkategorie WHERE nazwa = '$nazwapodkategorii' ORDER by bf_id DESC LIMIT 1;;");

                            while ($wartt2 = mysql_fetch_assoc($wynik)) {

                                $idpodkategorii = $wartt2['bf_id'];

                            }

                        } else {

                            $wynik = mysql_query("SELECT bf_id FROM cms_podkategorie WHERE nazwa = '$nazwapodkategorii' ORDER by bf_id DESC LIMIT 1;;");

                            while ($wartt2 = mysql_fetch_assoc($wynik)) {

                                $idpodkategorii = $wartt2['bf_id'];

                            }

                        }

                    }

                    $save = "INSERT INTO cms_produkty (nazwa, obrazek, isbn, cena, autor, wydawca, strony, opis, rok, podatek, cena2, kategoria, grupa, typrekordu, enable) VALUES ('$data[3]', '$data[15]', '$data[1]', '$data[9]', '$data[4]', '$data[5]', '$data[6]', '$data[8]', '$data[7]', '$data[10]', '$data[11]', '$idkategorii', '$idpodkategorii', '1', '1');";


                    $save = iconv('ISO-8859-2', 'UTF-8', $save);

                    mysql_query("$save");


                }

            }

Import ma sprawdzić czy dana kategoria/podkategoria/produkt istnieją w bazie - i jeśli nie - to go dodać...

Problem w tym, że skrypt zwraca błąd: Fatal error: Out of memory (allocated 43778048) (tried to allocate 42997761 bytes) in /ksiegarnia/import.php on line 55

Czy ktoś ma może pomysł jak wybrnąć z tego problemu?

Z góry dziękuję bardzo za pomoc,

Northwest


(Konrad Kosowski) #6

Dwa:

1) w php.ini poszukaj linii: memory_limit =, i zmien rozmiar na wieksza

2) przebuduj tak skrypt, zeby zamiast raz wczytywac 2000 linii wczytywal 4 razy po 500.


(northwest) #7

pkt 1 - nie mogę zrobić, to jest na home.pl :frowning:

a jak zrobić taki podział? da się jakoś z poziomu skryptu??


(ra-v) #8

Najprościej fizycznie podzielić na kilka plików np. po 10MB i importować osobno.

$filename = $_GET['plik'].".csv";[/code]

i wywołanie http://www.domena.pl/skrypt.php?plik=czesc5