| Autor | Zpráva | ||
|---|---|---|---|
| xerno Profil |
#1 · Zasláno: 8. 7. 2012, 13:51:03
Dobrý den drazí kolegové.
Můj cíl není získat přesný SQL dotaz, ale spíše radu, zamyšlení, jak by to řešil někdo jiný. Problém není jednoduchý, proto trocha teorie: Potřebuji vypsat seznam produktů (tabulka products), které vždy patří do nějaké kategorie (tabulky categories a sucategories). Kategorie mají stromový charakter. Pro představu - jedná se o autodíly. Každý díl může patřit na různé množství různých vozů. Známe konkrétní vůz. A produkty, které na něj patří, jsou v tabulce prices (archaický název, tabulka má 26 000 000 záznamů a bude hůř). Jenže dodavatel nechce některé výrobce autodílů v současné době dodávat, proto je tu tabulka vyrobci, která říká, kterého výrobce můžeme zobrazit. Tabulka categories může obsahovat 2-3 vrstvy, subcategories 2-6 vrstev. Dokud tabulky neprojdeme, nemůžeme vědět, v kolika vnořených vrstvách se produkt nachází. Má to svojí logiku a důvody (například proč jsou kategorie ve dvou tabulkách). Náčrt tabulek s vynecháním zbytečných sloupců: categories, subcategories: id, parent_id, name prices: id, parent_id, category, type (ID vozu), product products: id, name, vyrobce, category, subcategory vyrobci: id, name, skryt Tento problém je ve směs ještě jednoduchý, pro představu: (nekomentujte prosím míchání češtiny a angličtiny, také se mi to nelíbí) SELECT
`categories`.`name`AS`kategorie`,
`products`.`vyrobce`AS`vyrobce`,
`products`.`name`AS`nazev`,
`subcategories`.`parent_id`
...
FROM
`prices`,
`products`,
`categories`,
`vyrobci`,
`subcategories`
WHERE
`prices`.`type`=1 AND // toto je náš vůz
`products`.`id`=`prices`.`product`AND
`categories`.`id`=`prices`.`category`AND
`vyrobci`.`name`=`products`.`vyrobce`AND
`vyrobci`.`skryt`=0 AND
`subcategories`.`id`=`prices`.`parent_id`
GROUP BY`kategorie`,`vyrobce`,`nazev`
ORDER BY`kategorie`,`vyrobce`,`nazev`Tento způsob je tu od předchozího programátora a rozhodně není efektivní, protože pro výpis stromu musím v cyklu tohoto dumpu pokládat dotazy na subcategories a categories. Sice jich můžu mnoho vyfiltrovat kešováním předešle použité kategorie, ale to na rychlosti scriptu moc nepřidá. Už jenom z principu na příkladu: vypsaných produktů je 4000, v každém cykluse musím dotazovat na nadřazené kategorie, což může činit 2-8 dotazů. Krát 4000? :-) A to nejlepší na závěr: jakékoli PHP řešení musí pryč, protože je nutné kategorie také seřadit podle české abecedy. Řadit se musí také ve všech vnořených vrstvách kategorií. Aby bylo zcela jasno, předkládám současné řešení živě: VW Golf III 1.8 (jedná se o nejobsáhlejší vůz, jakékoli kešování stromu je vypnuté a dole můžete vidět jak dlouho trval script (samotný dotaz uvedený výše netrvá ani 0,1s)) Tedy: na snadě vidím řešení buď pomocí vnořených selectů, duplicitních tabulek, nebo vytváření MySQL funkcí, kde by se využila klauzule WHILE (ne nepodobně, jako je to nyní v PHP). Duplicitní tabulky rovnou můžu vynechat, protože vzhledem k tomu, že počet nadřazených kategorií může být vždy jiný, je toto řešení zcela kontraproduktivní a kdoví jestli vůbec možné. Za jakýkoli nápad budu rád. Předem díky. |
||
| Kajman Profil |
#2 · Zasláno: 8. 7. 2012, 14:29:16
Znáte metodu "traverzování kolem stromu" pro efektivnější dotazování na stromové struktury?
|
||
| LaMMa Profil |
Ak dobre chápem, chceš sa zbaviť tých dotazov do db v cykle a zoradiť kategórie podľa názvu. Napadlo mi riešenie v php, čo by mohlo efektívne fungovať:
Najprv vyberieš všetky id kategorii produktov z daného auta. Podľa týchto id vyberieš z db(...where id in (string_s_vybranymi_id) order by name) kategórie zoradené podľa názvu, rekurzívne až po najvyššiu kategóriu. Získaš tak strom kategórií v poradí akom si chcel s kategóriami, ktoré sa týkajú len daného auta. Potom začneš tento strom vypisovať a buď: a) ku každej kategórii prejdeš v cykle produkty(ktoré si selectneš pred cyklom). Ak id kategórie sedí s kategóriou produktu, vypíšes produkt a unsetneš ho, aby ho zbytočne neprechádzalo v cykle pri ďalších kategóriách. Takto by si si vystačil s pár dotazmi na začiatku a v cykloch sa už budeš hrať len s poľami. b) ku každej kategórii spravíš dotaz na produkty do db, čo by mohlo byť v konečnom dôsledku rýchlejšie ako prechádzať pole so všetkými produktami vo variante a). V každom prípade je kategóri omnoho menej ako produktov, a budeš robiť už len 1 dotaz ku kategórii, preto by to malo fičať rádovo rýchlejšie ako tá pôvodná verzia. Namiesto tých 4000*{2,...,8} dotazov by si sa dostal možno na stovku. Osobne by som sa priklonil k b) |
||
| xerno Profil |
#4 · Zasláno: 8. 7. 2012, 16:46:12
Kajman:
Znám, ale asi by mi příliš nepomohl. Kategorie se mohou měnit a navíc vždy znám "nejmladší dítě" a musím se dostat k rodičům. Traverzování kolem stromu má výhodu především když se snažím vypsat všechny vnořené prvky. Pokud existuje lepší způsob, který by se sem dal snadno zakomponovat, se s ním. LaMMa: Zajímavé řešení (a). Akorát práce s poli (a zvláště velká poli) není v PHP příliš rychlá. Mě napadlo něco podobného ve smyslu vypsání kategorií do polí, z nichž bych si sestavil strom, pak se dotázal na produkty a vyfiltrovat nepoužité kategorie. V podstatě podobně špatné řešení. Zajímavější je řešení (b), které by bylo jistě rychlejší, ale stále je těch dotazů velké množství. Původně jsem myslel, že bychom zůstali u jednoho dotazu, který by si všechno obstaral sám. Což by bylo pravděpodobně nejrychlejší řešení, i kdyby samotný dotaz trval řekněme 0,5-1s - protože všechno by již bylo připravené, PHP by jen vypisovalo data. |
||
| LaMMa Profil |
Až tak veľké množstvo ich nie je a s jedným dotazom to nespravíš. Teda aspoň ja si neviem predstaviť ten šialený dotaz, ktorým by si dostal z db zoradený strom kategórií s produktami. Keď tak by si musel využiť db funkcie s cyklami a to už by bolo podobne zložité ako php. Ale v každom prípade, by som výpis produktov a hľadanie ich kategórií otočil na výpis kategórií a k nim produktov. Vyrieši to aj rýchlosť aj problémy s radením kategórií.
Ešte k tomu b) by si si mohol pamať id kategórií z prvého dotazu(všetky id kategorii produktov z daného auta) a dotazovať sa do db na produkty ku kategórii, len ak boli vybraté v tom dotaze. Čiže by sa preskočili "medzikategórie". |
||
| juriad Profil |
#6 · Zasláno: 8. 7. 2012, 17:08:59
http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back
od strany 48 je popis 4 způsobů jak v sql reprezentovat stromovou strukturu path enumeration by mohlo pomoct, když si rozmyslíš pořadí skládání id v řetězci, aby dotazy byly prefixové (LIKE 'neco%') |
||
| Kajman Profil |
#7 · Zasláno: 9. 7. 2012, 10:17:36
xerno:
„navíc vždy znám "nejmladší dítě" a musím se dostat k rodičům“ I seznam všech předků lze pak získat jednodušeji. Mrkněte např. na Drobečková navigace na http://php.vrana.cz/traverzovani-kolem-stromu-prakticky.php |
||
| xerno Profil |
#8 · Zasláno: 9. 7. 2012, 11:51:41
Kajman, juriad:
Dobrá, tak tedy změnit vypisování stromu. Jistě po vyřešení tohoto problému tabulky s kategoriemi upravím. Ale i při použití této metody zůstane nemalé množství dotazů - nejdřív jeden velký (již uvedený) a v cyklu pro něj 1-2 dotazy na strom. A také to neřeší řazení kategorií podle abecedy. LaMMa: Dotaz klidně může vypadat šíleně, stačí ho jen přehledně zapsat. Zatím mi to připadá jako nejrozumější řešení, protože ať už by se stromová data získávala traverzováním kolem stromu nebo současnou metodou, uvnitř jednoho dotazu by to bylo velmi rychlé. Ale zase řešení přes funkci mi už připadá přehnané. |
||
| Tori Profil |
#9 · Zasláno: 9. 7. 2012, 13:23:54
xerno:
„A také to neřeší řazení kategorií podle abecedy.“ A co takhle: V databázi byste měl uložený strom a zajistil, že bude uložený v abecedním pořadí (anebo přidat dopočítaný sloupec navíc). Pak byste si vytáhl celý strom do PHP (a tam ho cachoval) jako pole ve tvaru pořadí => [id => podkategorie2, id => podkategorie1, id => kategorie], pořadí => ... V dotaze by se našly jen produkty + pořadí (pod)kategorie, do které potří, a seřadily podle toho pořadí. Asi by se trochu zkomplikovalo v PHP rozlišování kategorie, kolik vnořených seznamů se má uzavřít. Možná to je blbost, nevim. |
||
|
Časová prodleva: 12 let
|
|||
0