Bardzo mała szybkość zapytań do SQL Opcje

[JohnyBB]

Witam.
Jestem w miarę początkującym i doszedłem do problemu z którym nie mogę sobie poradzić, mianowicie:
jest sobie baza danych SQL, jest skrypt PHP który ma za zadanie wczytanie danych z pliku XML.
Plik XML - dość duży - około 16 tys pozycji.
Skrypt 'leci' po kolei i wczytuje dane z pliku, jeśli tylko wyświetlam te dane to trwa to moment- natomiast gdy robię UPDATE do bazy trwa to bardzo długo i wysypuje się po pewnym czasie.
Prawdopodobnie mam gdzieś błąd który zwalnia całą procedurkę - czy mogę prosić o pomoc forumowiczów ?

CODE

$data = simplexml_load_file(file.xml');


foreach($data -> produkty as $produkty)
{
foreach($produkty -> produkt as $produkt)
{

$id = (string) $produkt['id'];
$cena_netto = (string) $produkt['cena_netto'];
$dostepny = (string) $produkt['dostepny'];


if ($dostepny>0)
{

$query = "
UPDATE produkt SET wyswietlony=1,cena_netto='$cena_netto' WHERE kod_producenta LIKE '$id'
";
$result = mysql_query($query)
or die("Query UPDATE is failed");
}


}
}

Ten post edytował JohnyBB 16.05.2008, 13:52:09

[kszychu]

Proszę poprawić bbcode.

[batman]

No cóż. Skoro masz 16 tyś. pozycji to nie ma co się dziwić, że wolno działa, zwłaszcza, że update jest robiony dla większości produktów. Można nieco poprawić wydajność zapytania, jednak i tak nic to nie pomoże, jeśli uruchamiasz skrypt w przeglądarce. Masz dwa wyjścia:
1. Podzielić plik na mniejsze paczki i każdą paczkę z osobna mielić (samo dzielenie pliku może się nie udać z powodu dużego rozmiaru pliku).
2. Uruchomić skrypt w konsoli.

[kitol]

Wg mnie. wykonywanie 16tys razy mysql_query jest nieoptymalne. Z doświadczenia wiem, że lepiej jest zrobić konwersje pliku XML do formatu csv (lub innego rozdzielonego dowolnymi znakami). Oczywiście za pomocą PHP w pętli (szybkość będzie taka jak przy wyświetlaniu). Następnie wywołanie w mysql LOAD DATA INFILE nazwa_pliku..... która załaduje dane do bazy.

LIKE jest niezbędne? Nie można użyć = (IMG:http://forum.php.pl/style_emoticons/default/questionmark.gif)
Przy każdym zapytaniu modyfikujesz tylko jeden czy więcej wierszy w tabeli?

[zimi]

jeśli jesteś baaaardzo początkujący to może musisz zrobić klucz na `kod_producenta` (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)
aczkolwiek z prostego rachunku wynika że jeśli zrobisz 16000 update'ów z którego każdy dajmy na to zajmuje 10 ms, co jest chyba dość realne, to mamy 3 min... w sumie nie napisałeś co znaczy bardzo długo..., nie napisałeś też co i jak się wysypuję...

BTW. LIKE który nie wykorzystuję żadnych "znaczków dowolności", jest chyba równoważny =... zdaję się że tak kiedyś wyczytałem... ale mogę się mylić...

[JohnyBB]

Przy każdym zapytaniu modyfikuję tylko jeden wiersz w tabeli.
Sęk w tym że ta baza instnieje i nie mogę jej zmieniać - więc zmiana klucza odpada.
Bardzo długo znaczy - około godziny. Wysypuje się w taki sposób, ze wyświetla się "nie można odnaleźć strony itd." nie pojawia się zatem żaden Error - pewnie jest przekroczony czas jakiś.
LIKE nie jest niezbędne - takie znalazłem rozwiązanie - czyli zamieniam na =
---

1. Podzielić plik na mniejsze paczki i każdą paczkę z osobna mielić (samo dzielenie pliku może się nie udać z powodu dużego rozmiaru pliku).
2. Uruchomić skrypt w konsoli.

ad1 - odpada - nie ma na to czasu bawić się z krojeniem xml'a 1 dziennie.
ad2 - można jaśniej ? jak uruchomić skrypt w konsoli ?

Wg mnie. wykonywanie 16tys razy mysql_query jest nieoptymalne. Z doświadczenia wiem, że lepiej jest zrobić konwersje pliku XML do formatu csv (lub innego rozdzielonego dowolnymi znakami). Oczywiście za pomocą PHP w pętli (szybkość będzie taka jak przy wyświetlaniu). Następnie wywołanie w mysql LOAD DATA INFILE nazwa_pliku..... która załaduje dane do bazy.

czyli LOAD DATA INFILE wykonuję z MyAdmina ? po uprzedniej zamianie XML na inny format ?

[kitol]

Możesz robić wszystko ręcznie i wówczas LOAD DATA wykonujesz z MyAdmina. Wygodniej jest to umieścić na końcu skryptu który dokonuje konwersji na CSV. Musisz mieć ustawiony klucz unikalny oraz dodać parametr REPLACE do LOAD DATA jeżeli chcesz UPDATEować dane, w przeciwnym razie dane zostaną dopisane do tabeli. Więcej o load data możesz przeczytać tu
Modyfikacje kluczy możesz wykonywać na działającej bazie- z tym nie ma problemu.

[Kocurro]

Po primo - używasz mało wydajnej metody by odczytywać XML'a.

Po sekundo zamiast LIKE daj = .

Po tertio - jeśli robisz update to nie wywalaj bez tranzakcji w przypadku błędu bo integralności danych się sypie.

Po quatro - PHP wykłada się na alokacji pamięci w tym wypadku. Stwórz własny parser xml (patrz funkcja xml_parse czy jakoś tak), wywal niepotrzebne pętla.

Po quinto - jeśli możesz użyj prepared statements

Po .... - nie stosuj konstrukcji w PHP'ie " $zmienna " - przy takiej ilości danych faktycznie może to trochę obciążać

Po .... - powiedz jaka baza danych i (jeśli to ma znaczenie) jaki typ tabel a porawdzę więcej

Po .... - dodaj klucze

Po .... - zależnie od bazy danych możesz być zmuszony po tak ostrej zmianie danych dokonać analizy danych by odnowić klucze

Po .... - dlaczego cena netto jest ciągiem znaków a nie liczbą decimal ? to jest niepotrzebne obciążanie bazy danych (czyt. źle zaprojektowana baza )

pozdr.
Łukasz (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

[JohnyBB]

Możesz robić wszystko ręcznie i wówczas LOAD DATA wykonujesz z MyAdmina. Wygodniej jest to umieścić na końcu skryptu który dokonuje konwersji na CSV. Musisz mieć ustawiony klucz unikalny oraz dodać parametr REPLACE do LOAD DATA jeżeli chcesz UPDATEować dane, w przeciwnym razie dane zostaną dopisane do tabeli. Więcej o load data możesz przeczytać tu
Modyfikacje kluczy możesz wykonywać na działającej bazie- z tym nie ma problemu.

kurcze chyba w to się nie zagłębię - finalnie ma być to automatycznie uruchamiane przez usera... więc odpadają takie operacje...
Kocurro:
ad 1 - chodzi Ci o : simplexml_load_file ? to mało wydajne ?
ad 2 - wstawiłem =
zamieniłem stringi , tzn usunąłem te przekształcenia i zadziałało - jednak długo to trwa a problem jest ciekawy...
Jak pisałem jestem noobem więc - dodaj klucze ? rozumiem ze do tabel ale w przypadku gdy jest źle zaprojektowana baza musiałbym całą zaprojektować od nowa a co za tym idzie przepisać cały kod - nie ma na to czasu niestety.

powiedz jaka baza danych

tzn jaka ? postgres mysql ? chodzi Ci o serwer?

nie stosuj konstrukcji w PHP'ie " $zmienna "

w jaki sposób mam to zastąpić ?
Pozdrawiam

[Shili]

w jaki sposób mam to zastąpić ?

Z tego co rozumiem, to po prostu wpisz $zmienna, bez cudzysłowa. Cudzysłów jest dodatkowo parsowany w poszukiwaniu na przykład zmiennych, więc siłą rzeczy jego wykonanie trwa dłużej. Zamiast tego łącz łańcuchy.

Ten post edytował Shili 17.05.2008, 14:02:53

[em1X]

Lepiej pokaż strukturę Twojej tabeli. Źle zaprojektowana tabela znacznie spowalnia na niej działanie, a już nie raz widziałem takie perełki, że trzymano liczby 0 lub 1 (boolean) w polach typu bigint(1).

[Kicok]

Oprócz powyższych porad spróbuj jeszcze zrobić coś takiego:

- Zdejmij wszystkie klucze/indeksy które nie są niezbędne przy tej operacji (kurs SQL -> ALTER TABLE )
- Zrób te 16 tyś. UPDATE
- Ponakładaj klucze/indeksy z powrotem
- Wykonaj REPAIR TABLE


Natomiast w manualu MySQL znalazłem coś takiego:

If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes.

This feature can be activated explicitly for a MyISAM table. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

[batman]

ad2 - można jaśniej ? jak uruchomić skrypt w konsoli ?

Uruchomienie skryptu w konsoli oznacza, że uruchamiasz terminal (konsolę), następnie przechodzisz do odpowiedniego katalogu (polecenie cd), a następnie uruchamiasz skrypt

Kod

php ./twoj_skrypt.php

Nie wiem jak to się robi pod windą (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg) ale domyślam się, że podobnie.

[JohnyBB]

batman: to jest na virtualnym serwerze - więc na konsolę się nie załaduję - nie mam konta na shell'u.

kicok: klucze w tabeli czy w całej bazie ?

em1x: pomimo, ze baza jest fatalnie zaprojektowana, strukturę już wcześniej poprawiłem więc nie ma takich kruczków.

[em1X]

em1x: pomimo, ze baza jest fatalnie zaprojektowana, strukturę już wcześniej poprawiłem więc nie ma takich kruczków.

więc czemu jej nie pokażesz? przecież to żadna tajemnica.. no chyba, że się wstydzisz (IMG:http://forum.php.pl/style_emoticons/default/aaevil.gif)

[yivan]

Z tego wszystkiego najwolniej działa mysql_query(). Spróbuj najpierw przepisać dane, które wymagają zaktualizowania do jednej długiej tablicy. Potem daj unset($data) jeśli te dane nie są Ci już potrzebne. Następnie iterując tablicę z danymi utwórz dłuuuugie zawierające jednocześnie ok 1000 instrukcji Update, ew co 1000 wierszy rób mysql_query().

Wszystko po to abyś nie robił do 16tyś mysql_query ale maks kilkanaście, tak będzie szybciej.

[JohnyBB]

em1x: nie chcę pokazywać bazy - powiedzmy że się wstydzę, choć nie ja ją projektowałem.

yivan - ok - domyslam się że to opóźnia, bo ciągle nawiązuje połączenie aby 1 update wykonać i zamyka. spróbuję Twojej metody.

OK - pytanie - jak zadać kilka UPDATE w jednym mysql_query ? Jakieś błędy mi powychodziły przy testach.