PDO + MySQL, optymalizacja zapytania Opcje

[mummle]

Witam forumowiczów.
Mam pewien zgrzyt z kodem PHP. Od razu uprzedzam - moja znajomość PHP i SQL są na poziomie ... raczkującego dziecka. Stąd prośba o pomoc.
Mam skrypt który ma za zadanie:
1. Insert nowych danych do tabeli.
2. Update istniejących.

Dane pobierane są z pliku CSV. Plik zawiera dane sprzedażowe z 4 okresów, przesyłany jest z końcem pojedynczego okresu rozliczeniowego. Dane z pliku dublują się z danymi w bazie stąd konieczność ich odświeżenia.

Całość skryptu wygląda tak:

[PHP] pobierz, plaintext 
<?
$f=$_FILES['r_update'];
$file=$f['tmp_name'];
$c_fname=$f['name'];
$lines=file($file,FILE_SKIP_EMPTY_LINES);
reset($lines);
$newr=0;
$updr=0;
 
// pobranie danych do tablicy $lines z przesłanego pliku CSV z _POST. Ustawienie zmiennych informacyjnych na 0.
try {
$dbk = new PDO('mysql:host=localhost;dbname=baza', 'root', 'root');
$dbk->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$szukaj=explode(";",$lines[0]);
$odd=$szukaj[0];
$issue=$szukaj[4];
$issue_min=$szukaj[4]-5;
$issue_max=$szukaj[4]+5;
 
//Połącznie z bazą. Wydobycie danych do stworzenia widoku/perspektywy.
 
$start=$dbk->query('create view pomocnadlon as select * from r_dane where oddzial like ''.$odd.'' and nr between '.$issue_min.' and '.$issue_max.'');
// tworzę widok/perspektywę by ograniczyć wyszukiwanie w bazie
reset($lines);d
    foreach ($lines as $line) {
            $dane=explode(";", $line);
            for ($i=0;$i<=4;$i++) {
                    $dane[$i]=trim($dane[$i]);
             }
//exploduję linie pliku i wycinam spacje na początku i na końcu
 
        $x="select * from pomocnadlon where id_psd like '".$dane[1]."' and nr=".$dane[4];
        $vstart=$dbk->query($x);
        $vstart->setFetchMode(PDO::FETCH_ASSOC);
//nową instancją istniejącego obiektu PDO pobieram dane o tym czy rekord istnieje
        
        if (empty($vstart)) {
            $insert="INSERT INTO pr_dane (oddzial, id_psd, dost, zwr, nr) VALUES ('".$dane[0]."','".$dane[1]."',".$dane[2].",".$dane[3].",".$dane[4].")";
            $insert_do=$dbk->query($insert);
            $newr++;
        } else {
            $update="update r_dane set oddzial='".$dane[0]."', id_psd='".$dane[1]."', dost=".$dane[2].", zwr=".$dane[3].", nr=".$dane[4]." where oddzial like '".$dane[0]."' and id_psd like '".$dane[1]."' and nr=".$dane[4];
            $update_do=$dbk->query($update);
            $updr++;
        }
    }
//Istnieje - update, czyli ELSE. Nie istnieje bo jest EMPTY - INSERT.
 
$vstart=$dbk->query('drop view pomocnadlon');
echo "Dodano nowe rekordy! ".$newr." <br/>";
echo "Zaktualizowano rekordy! ".$updr." <br/>";
}
catch (PDOException $e) {
    echo 'Połączenie nie mogło zostać utworzone: ' . $e->getMessage();
 }
?>
[PHP] pobierz, plaintext 

Plik csv ma postać:
Oddział;ID;Ilość;Niesprzedane;Nr_dostawy. Max 6000 wierszy.

Skrypt pada mi po 300 sekundach od odpalenia, przestawiłem sobie czas wykonania w php.ini na 300. Mam wrażenie, że to co napisałem albo się zapętla, albo nie wyrabia jeśli codzi o skuteczność zapytań. O co chodzi? Jak przyspieszyć UPDATE?

pzdr
mummle

Ten post edytował mummle 16.03.2009, 10:56:14

[erix]

[PHP] pobierz, plaintext 
<?php
$lines=file($file,FILE_SKIP_EMPTY_LINES);
?>
[PHP] pobierz, plaintext 

Rób przez pętle z fgets" title="Zobacz w manualu PHP" target="_manual.

Poza tym:

[PHP] pobierz, plaintext 
<?php
$update="update r_dane set oddzial='".$dane[0]."', id_psd='".$dane[1]."', dost=".$dane[2].", zwr=".$dane[3].", nr=".$dane[4]." where oddzial like '".$dane[0]."' and id_psd like '".$dane[1]."' and nr=".$dane[4];
?>
[PHP] pobierz, plaintext 

Masz możliwość sprawdzenia bez użycia like?

[mummle]

Dzięki za podpowiedź z fgets - skorzystam.

Zamieniłem like na = (nie wiem czemu uparłem się na ten sposób porównania), ale nadal po 300 sekundach wywala błąd. Skrypt wciąż bez problemu insertuje dane, gorzej z update.

Pozdrawiam
mummle

Ten post edytował mummle 15.03.2009, 08:57:14

[Mephistofeles]

Korzystasz z PDO! Czemu nie używasz bindowania?
Zaoszczędzisz czasu - wtedy tylko raz wyślesz zapytanie, a cały czas będziesz aktualizował dane i wykonywał zapytanie. Poczytaj bindValue i bindParam.

Ten post edytował Mephistofeles 15.03.2009, 10:07:03

[mummle]

Korzystasz z PDO! Czemu nie używasz bindowania?
Zaoszczędzisz czasu - wtedy tylko raz wyślesz zapytanie, a cały czas będziesz aktualizował dane i wykonywał zapytanie. Poczytaj bindValue i bindParam.

A możesz mi podpowiedzieć w jaki sposób mogę to zrobić? Kodem? O bindowaniu wiem, ale nie wiem jak z niego korzystać. Niestety polskie manuale o PDO są dość ubogie (to co znalazłem na wiki mówi, ale nie wyjaśnia, inglisz - chętnie poczytam, przejrzę prototypy obiektów), a poza tym brak im jak i tym z php.net solucji 100% rozwiązań. Opis takiej klasy jest po prostu zbyt ubogi bo jest poglądowy. Chodzi o prezentację a nie o rozwiązania.
Jakbym wiedział jak korzystać z bindowania, co ze sobą niesie to bym nie pytał. Chętnie poznam Twoje źródła wiedzy o PDO. Nie pytam o rybę (choć może w tym przypadku chodzi o nią i jest niezbędna bo czas mnie goni) lecz zapytuję o wędkę, źródła, wiedzę na przyszłość.

Czytałem o bindowaniu parametrów ale nie wiem jak to działa. Pomożesz? Help, need help (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

pzdr
mummle

Ten post edytował mummle 15.03.2009, 13:01:11

[Mephistofeles]

Moje źródła? Wikibooks (PHP), manual (wbrew pozorom jest tam całkiem sporo opisane), i spory opis na WebCity - tutaj.
Bindowanie/podpinanie to operacja na poziomie bazy, więc jest całkowicie odporna na SQL Injection, i czasami szybsza od łączenia parametrów w PHP.
Robi się to tak:

[PHP] pobierz, plaintext 
<?php
$stmt = $pdo->prepare('SELECT * FROM xxx WHERE yyy = :yyy'); // Jak widzisz zostawiłem lukę na parametr - :yyy
 
$stmt -> bindValue(':yyy', $y, PDO::PARAM_INT); // Podpinanie wartości - kopiuje zawartość zmiennej, 3 parametr to typ zmiennej
$stmt -> bindParam(':yyy', $y, PDO::PARAM_INT); // Podpinanie przez referencję, nie kopiuje zmiennej, jest szybsze
 
$stmt -> execute(); // Wykonujesz zapytanie
(...) // I dalej już normalnie np. $stmt -> fetch(PDO::FETCH_ASSOC);
?>
[PHP] pobierz, plaintext 

Aaa, zapomniałbym - przy tym możesz zapytanie przygotować przed pętlą, a jeśli podpinasz referencyjnie to też możesz zostawić, a wykonujesz w pętli. PDO już sobie wyśle odpowiednią wartość przy referencji.
No i zamiast pobierać dane możesz nałożyć choćby unique na jakąś kolumnę, i wykonywać INSERT (...) ON DUPLICATE (...) (IMG:http://forum.php.pl/style_emoticons/default/smile.gif) .

Ten post edytował Mephistofeles 15.03.2009, 17:51:46

[mummle]

[PHP] pobierz, plaintext 
<?php
try {
$dbk = new PDO('mysql:host=localhost;dbname=baza', 'root', 'root');
$dbk->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$szukaj=explode(";",$lines[0]);
$odd=$szukaj[0];
$issue=$szukaj[4];
$issue_min=$szukaj[4]-5;
$issue_max=$szukaj[4]+5;
 
$start=$dbk->query('create view pomocnadlon as select * from r_dane where oddzial=''.$odd.'' and nr between '.$issue_min.' and '.$issue_max.'');
$search_do=$dbk->prepare('select * from pomocnadlon where id_psd= :id_psd and nr= :nr');
$search_do->bindParam(':id_psd', $dane[1], PDO::PARAM_STR);
$search_do->bindParam(':nr', $dane[4], PDO::PARAM_INT);
 
$insert_do=$dbk->prepare('INSERT INTO `r_dane` (`oddzial`, `id_psd`, `dost`, `zwr`, `nr`) VALUES (:oddzial, :id_psd, :dost, :zwr, :nr)');
$insert_do->bindParam(':oddzial', $dane[0], PDO::PARAM_STR);
$insert_do->bindParam(':id_psd', $dane[1], PDO::PARAM_STR);
$insert_do->bindParam(':dost', $dane[2], PDO::PARAM_INT);
$insert_do->bindParam(':zwr', $dane[3], PDO::PARAM_INT);
$insert_do->bindParam(':nr', $dane[4], PDO::PARAM_INT);
 
 
$update_do=$dbk->prepare('UPDATE `r_dane` set oddzial= :oddzial, id_psd= :id_psd, dost= :dost, zwr= :zwr, nr= :nr where oddzial= :oddzial and id_psd= :id_psd and nr= :nr');
$update_do->bindParam(':oddzial', $dane[0], PDO::PARAM_STR);
$update_do->bindParam(':id_psd', $dane[1], PDO::PARAM_STR);
$update_do->bindParam(':dost', $dane[2], PDO::PARAM_INT);
$update_do->bindParam(':zwr', $dane[3], PDO::PARAM_INT);
$update_do->bindParam(':nr', $dane[4], PDO::PARAM_INT);
 
reset($lines);
    foreach ($lines as $line) {
            $dane=explode(";", $line);
            for ($i=0;$i<=4;$i++) {
                    $dane[$i]=trim($dane[$i]);
             }
//        $x="select * from pomocnadlon where id_psd='".$dane[1]."' and nr=".$dane[4];
//        $vstart=$dbk->query($x);
        $search_do->execute();
        $search_do->setFetchMode(PDO::FETCH_ASSOC);
        
        if (empty($search_do)) {
//            $insert="INSERT INTO pr_dane (oddzial, id_psd, dost, zwr, nr) VALUES ('".$dane[0]."','".$dane[1]."',".$dane[2].",".$dane[3].",".$dane[4].")";
//            $insert_do=$dbk->query($insert);
            $insert_do->execute();
            $newr++;
        } else {
//            $update="update r_dane set oddzial='".$dane[0]."', id_psd='".$dane[1]."', dost=".$dane[2].", zwr=".$dane[3].", nr=".$dane[4]." where oddzial='".$dane[0]."' and id_psd='".$dane[1]."' and nr=".$dane[4];
//            $update_do=$dbk->query($update);
            $update_do->execute();
            $updr++;
        }
    }
$vstart=$dbk->query('drop view pomocnadlon');
echo "Dodano nowe rekordy! ".$newr." <br/>";
echo "Zaktualizowano rekordy! ".$updr." <br/>";
}
?>
[PHP] pobierz, plaintext 

Poczytałem, zmodyfikowałem kod zgodnie z sugestiami i nadal wywala się po 300 sekundach... O co kaman? Może jest jakiś inny sposób na UPDATE?

Pozdrawiam
mummle

Ten post edytował mummle 16.03.2009, 15:17:44

[dr_bonzo]

Dzizez

1. fopen + fgetcsv - tym sie czyta CSV'y
2. ile masz linii w tym pliku?
3. zapisuj sobie do innego pliku liczbe przetworzonych rekordow - to bedziesz wiedzial co jest nie tak i w ktorym miejscu pada
4. Nigdzie nie definiujesz zmiennej $dane przed jej uzyciem

[Mephistofeles]

Napisałem jeszcze, żebyś skorzystał z INSERT ON DUPLICATE KEY, poczytaj o tym, ominiesz SELECTa.

[mummle]

Dzizez

1. fopen + fgetcsv - tym sie czyta CSV'y
2. ile masz linii w tym pliku?
3. zapisuj sobie do innego pliku liczbe przetworzonych rekordow - to bedziesz wiedzial co jest nie tak i w ktorym miejscu pada
4. Nigdzie nie definiujesz zmiennej $dane przed jej uzyciem

2. Do 12 tysięcy.
3. Przetwarza ok 600-800 wierszy.
4. Zmienną zdefiniowałem, mój błąd.

Napisałem jeszcze, żebyś skorzystał z INSERT ON DUPLICATE KEY, poczytaj o tym, ominiesz SELECTa.

Problem w tym, że jednoznaczna i unikalna jest tylko para danych oddział+id. Niestety ID w oddziałach się powtarzają.

Ostatecznie, korzystając ze wszystkich rad mam coś takiego, co nadal update'uje najwyżej... 600 rekordów:

[PHP] pobierz, plaintext 
<?
$f=$_FILES['r_update'];
$file=$f['tmp_name'];
$uchwyt = fopen ($file,"r");
$dane = fgetcsv($uchwyt, 100, ";");
reset($dane);
$newr=0;
$updr=0;
 
try {
$dbk = new PDO('mysql:host=localhost;dbname=baza', 'root', 'root');
$dbk->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
$odd=$dane[0];
$issue=$dane[4];
$issue_min=$dane[4]-5;
$issue_max=$dane[4]+5;
 
$start=$dbk->query('create view pomocnadlon as select * from r_dane where oddzial=''.$odd.'' and nr between '.$issue_min.' and '.$issue_max.'');
$search_do=$dbk->prepare('select * from pomocnadlon where id_psd= :id_psd and nr= :nr');
$search_do->bindParam(':id_psd', $dane[1], PDO::PARAM_STR);
$search_do->bindParam(':nr', $dane[4], PDO::PARAM_INT);
 
$insert_do=$dbk->prepare('INSERT INTO `r_dane` (`oddzial`, `id_psd`, `dost`, `zwr`, `nr`) VALUES (:oddzial, :id_psd, :dost, :zwr, :nr)');
$insert_do->bindParam(':oddzial', $dane[0], PDO::PARAM_STR);
$insert_do->bindParam(':id_psd', $dane[1], PDO::PARAM_STR);
$insert_do->bindParam(':dost', $dane[2], PDO::PARAM_INT);
$insert_do->bindParam(':zwr', $dane[3], PDO::PARAM_INT);
$insert_do->bindParam(':nr', $dane[4], PDO::PARAM_INT);
 
 
$update_do=$dbk->prepare('UPDATE `r_dane` set oddzial= :oddzial, id_psd= :id_psd, dost= :dost, zwr= :zwr, nr= :nr where oddzial= :oddzial and id_psd= :id_psd and nr= :nr');
$update_do->bindParam(':oddzial', $dane[0], PDO::PARAM_STR);
$update_do->bindParam(':id_psd', $dane[1], PDO::PARAM_STR);
$update_do->bindParam(':dost', $dane[2], PDO::PARAM_INT);
$update_do->bindParam(':zwr', $dane[3], PDO::PARAM_INT);
$update_do->bindParam(':nr', $dane[4], PDO::PARAM_INT);
 
reset($dane);
    while ($dane!==FALSE) {
            for ($i=0;$i<=4;$i++) {
                    $dane[$i]=trim($dane[$i]);
             }
        $search_do->execute();
        $search_do->setFetchMode(PDO::FETCH_ASSOC);
        
        if (empty($search_do)) {
            $insert_do->execute();
            $newr++;
        } else {
            $update_do->execute();
            $updr++;
        }
    }
$start=$dbk->query('drop view pomocnadlon');
echo "Dodano nowe rekordy! ".$newr." <br/>";
echo "Zaktualizowano rekordy! ".$updr." <br/>";
}
catch (PDOException $e) {
    echo 'Połączenie nie mogło zostać utworzone: ' . $e->getMessage();
}
fclose ($uchwyt);
?>
[PHP] pobierz, plaintext 

Pozdrawiam
mummle

Ten post edytował mummle 16.03.2009, 16:47:37

[Mephistofeles]

Ale ON DUPLICATE KEY przecież sprawdza tylko unikalne klucze, więc czemu miałby nie przyjmować zwykłych pól?

[mummle]

Ale ON DUPLICATE KEY przecież sprawdza tylko unikalne klucze, więc czemu miałby nie przyjmować zwykłych pól?

Z tego co zrozumiałem potrzebuję zdefiniować kolumnę jako UNIQUE. Problem w tym, iż nie mam takiej możliwości.

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `r_dane` (
  `oddzial` text COLLATE utf8_unicode_ci NOT NULL,
  `id_psd` text COLLATE utf8_unicode_ci NOT NULL,
  `dost` int(11) NOT NULL,
  `zwr` int(11) NOT NULL,
  `nr` int(11) NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
[SQL] pobierz, plaintext 

Unikatowe może być tylko połączenie pól oddzial i id_psd. Oddział pojawia się wielokrotnie i jest powtarzalny. Id_psd może być takie samo w przypadku różnych oddziałów. Fajnie i prosto byłoby gdyby tak nie było (IMG:http://forum.php.pl/style_emoticons/default/smile.gif) Wiem. Jedyna opcja by skorzystać z ON DUPLICATE KEY to stworzenie "sumy" tych dwóch pól jako dodatkowe, automatycznie generowane pole w bazie, nadanie mu UNIQUE, które chyba nie przejdzie dla pola tekstowego i potem zmiana zapytania, wprowadzenie zmiennej łączącej wartości z pliku CSV i podstawienie jej za KEY. Mylę się? Tylko jak to zrobić? I czy to jest możliwe?

pzdr
mummle

[Mephistofeles]

A nie możesz dodać pola id generowanego z np. md5(oddzial.id_psd)? Albo nawet samo oddzial.id_psd.

Ten post edytował Mephistofeles 16.03.2009, 19:56:17

[mummle]

A nie możesz dodać pola id generowanego z np. md5(oddzial.id_psd)? Albo nawet samo oddzial.id_psd.

I to jest myśl, stworzyć coś jakby pole z serialem rekordu. Tylko dodałbym do tego jeszcze .nr. W ten sposób będę mógł utworzyć UNIQUE (tylko czy da się go stworzyć dla pól text lub blob?) i skorzystać z ww rozwiązania. A może przekształcić to na jakiś ciąg binarny? MD5 generuje ciągi alfanumeryczne jak dobrze pamiętam i tu może być problem z zastosowaniem indeksacji.
Potestuję, dam znać.

pzdr
mummle

Ten post edytował mummle 16.03.2009, 21:10:03

[Mephistofeles]

Na varchary też przecież wchodzi indeks - wiele skryptów ma unique na loginie (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg) .

[mummle]

Na varchary też przecież wchodzi indeks - wiele skryptów ma unique na loginie (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg) .

Stanęło na tym:

[PHP] pobierz, plaintext 
<?php
try {
$dbk = new PDO('mysql:host=localhost;dbname=baza', 'root', 'root');
$dbk->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
    foreach ($lines as $line) {
            $dane=explode(";", $line);
            for ($i=0;$i<=4;$i++) {
                    $dane[$i]=trim($dane[$i]);
            }
            $serial=$dane[0]."_".$dane[1]."_".$dane[4];
            $qry="SELECT * FROM r_dane WHERE serial='".$serial."'";
            $search_do=$dbk->query($qry);
            $row = $search_do->fetch();
            
            if (empty($row['oddzial']) && empty($row['id_psd'])) {
            $qry="INSERT INTO r_dane (oddzial, id_psd, dost, zwr, nr, serial) VALUES ('".$dane[0]."','".$dane[1]."',".$dane[2].",".$dane[3].",".$dane[4].",'".$serial."')";
            $insert_do=$dbk->query($qry);
            $newr++;
            } else {
            $qry="UPDATE r_dane SET dost=".$dane[2].", zwr=".$dane[3]." WHERE serial='".$serial."'";
            $update_do=$dbk->query($qry);
            $updr++;
            }
    }            
 
echo "Dodano nowe rekordy! ".$newr." <br/>";
echo "Zaktualizowano rekordy! ".$updr." <br/>";
}
?>
[PHP] pobierz, plaintext 

Śmiga aż miło. Zastanawiam się komu podziękować (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

Pozdrawiam
mummle

Ten post edytował mummle 17.03.2009, 16:54:41