Autor Téma: Pokus: porovnanie dvoch dopytov. Hlava mi to neberie  (Přečteno 574 krát)

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6924
  • Karma: 44
    • Verze Delphi: W10 + D11.1

Moja logika zlyháva. Vysvetlí mi niekto, prečo je v druhom prípade polovičný čas? Veľmi ma to prekvapilo a mám silné nutkanie to všade opraviť/zmeniť. Keď sa tým už babrem, tak poriadne.
14 sek. Pôvodný dopyt. Agregačné funkcie sú v CTE
Kód: [Vybrat]
WITH D AS
  (SELECT D.FKOWNER_FLAT, SUM(D.DEPOSIT) AS DEPOSIT, SUM(D.PAY) AS PAID
  FROM DEPOSITS D
  WHERE D.FKFOCS = :FKFOCS AND D.DEPOSITPERIOD BETWEEN :FIRSTDATE AND :LASTDATE
  GROUP BY D.FKOWNER_FLAT)
SELECT D.FKOWNER_FLAT, F.FLATNUMBER, O.FULLNAME, D.DEPOSIT, D.PAID, (D.PAID - D.DEPOSIT) AS DIFFERENCE
FROM FLATS F
  INNER JOIN OWNER_FLAT OWF ON F.IDFLATS = OWF.FKFLAT
  INNER JOIN D ON OWF.IDOWNER_FLAT = D.FKOWNER_FLAT
  INNER JOIN OWNERS O ON OWF.FKOWNERS = O.IDOWNERS
6 sek. Agregačné funkcie sú ZA CTE
Kód: [Vybrat]
WITH D AS
  (SELECT D.FKOWNER_FLAT, D.DEPOSIT, D.PAY, D.PAY - D.DEPOSIT AS DIFFERENCE
  FROM DEPOSITS D
  WHERE D.FKFOCS = :FKFOCS AND D.DEPOSITPERIOD BETWEEN :FIRSTDATE AND :LASTDATE)
SELECT D.FKOWNER_FLAT, F.FLATNUMBER, O.FULLNAME, SUM(D.DEPOSIT) AS DEPOSIT, SUM(D.PAY) AS PAID,
  SUM(D.DIFFERENCE) AS DIFFERENCE
FROM FLATS F
  INNER JOIN OWNER_FLAT OWF ON F.IDFLATS = OWF.FKFLAT
  INNER JOIN D ON OWF.IDOWNER_FLAT = D.FKOWNER_FLAT
  INNER JOIN OWNERS O ON OWF.FKOWNERS = O.IDOWNERS
GROUP BY D.FKOWNER_FLAT, F.FLATNUMBER, O.FULLNAME
To je k tým dlhým dopytom. Bod B)
Win11 64b, Delphi 11.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline František

  • Guru
  • *****
  • Příspěvků: 867
  • Karma: 8
    • Verze Delphi: comunity 10.4.2, D2007, D11
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #1 kdy: 07-12-2022, 20:51:56 »
velmi som sa neponaral, ale mas tam rozdielny pocet GROUP BY stlpcov, a v druhom pripade grupujes v troch tabulkach

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6924
  • Karma: 44
    • Verze Delphi: W10 + D11.1
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #2 kdy: 07-12-2022, 21:14:06 »
Podľa mňa GROUP BY určite nehrá úlohu.
Jediný rozdiel je v použití agregačných funkcií.
Keď som robil jednoduché pokusy:
  • Priamo agregačné funkcie - prvý prípad
  • Najprv výber a na to potom agregačné funkcie - druhý prípad
tak som tam nenameral významné rozdiely. Tu taký skok.
Win11 64b, Delphi 11.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline Jan Fiala

  • Hrdina
  • ****
  • Příspěvků: 344
  • Karma: 4
    • Verze Delphi: 10.4.1
    • PSPad editor
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #3 kdy: 08-12-2022, 06:35:05 »
Někdy je rychlejší vybrat data a až pak provádět agragaci, zvlášť pokud je dat hodně. Výsledek záleží na optimalizaci, kterou provádí SQL server, na návrhu tabulky, na použitých indexech...
« Poslední změna: 08-12-2022, 06:38:39 od Jan Fiala »

Offline pepak

  • Padawan
  • ******
  • Příspěvků: 1572
  • Karma: 37
    • Pepak.net
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #4 kdy: 08-12-2022, 06:41:13 »
První dotaz říká: Za tabulku DEPOSITS mi spočítej součty za každého vlastníka. Až to budeš mít, tak ke každému vlastníkovi připoj nějaké údaje a také tyto součty.

Druhý dotaz říká: Ke každému vlastníkovi připoj nějaké údaje a jeho součty.

Tzn. první dotaz je posloupnost dvou operací, samostatně optimalizovaných a následně propojených přes natural join, zatímco druhý dotaz je jedna operace, komplet celá optimalizovaná.

A jak píše Jan Fiala, někdy je rychlejší jedno a někdy druhé. Záleží na konkrétní struktuře tabulek, na způsobu výpočtu, na velikosti dat v jednotlivých tabulkách, atd.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6924
  • Karma: 44
    • Verze Delphi: W10 + D11.1
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #5 kdy: 08-12-2022, 09:43:33 »
Ďakujem za odpovede.
Zhoda je v jednom. Údajov je veľa 16,74 mil. DataSet vráti 139 500.
Z toho vyplýva, že môžem tak akurát experimentovať, čo je pre danú situáciu lepšie ???  To je časovo dosť náročné. No niekedy to stojí zato.
Win11 64b, Delphi 11.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline Jan Fiala

  • Hrdina
  • ****
  • Příspěvků: 344
  • Karma: 4
    • Verze Delphi: 10.4.1
    • PSPad editor
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #6 kdy: 08-12-2022, 12:48:17 »
Jak máš postavené indexy?
Pokud hodně pracuješ s podmínkách se sloupci FKFOCS a DEPOSITPERIOD současně, zkus si vytvořit index na obě pole.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6924
  • Karma: 44
    • Verze Delphi: W10 + D11.1
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #7 kdy: 08-12-2022, 14:52:09 »
Jak máš postavené indexy?
Pokud hodně pracuješ s podmínkách se sloupci FKFOCS a DEPOSITPERIOD současně, zkus si vytvořit index na obě pole.
To je jediný ktorý mám. Ostatné nemali žiaden účinok.
Win11 64b, Delphi 11.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline Jan Fiala

  • Hrdina
  • ****
  • Příspěvků: 344
  • Karma: 4
    • Verze Delphi: 10.4.1
    • PSPad editor
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #8 kdy: 09-12-2022, 10:30:57 »
Indexy se staví tak, aby co nejvíc omezily výběr a staví se na nejpoužívanější podmínky.
Pak záleží na optimalizátoru, jak indexy použije. Někdy se optimalizator chová jako debil a musíš mu v dotazu vnutit konkrétní index. Příklad pro Firebird:
https://firebirdfaq.org/faq158/

Netuším, jestli FireBird umí clustered indexy. To je index, kde jsou data fyzicky uspořádaná podle indexu a opět to zrychlí výběr.


Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6924
  • Karma: 44
    • Verze Delphi: W10 + D11.1
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #9 kdy: 10-12-2022, 15:19:39 »
Zistil som, že v DB nikto za nič neručí. Moja skúsenosť s pokusmi
  • pridal som navyše niečo do WHERE (alebo INNER JOIN?). Už si presne nepamätám. Čas odozvy sa významne zvýšil
  • pridal som do WHERE tri podmienky, ktoré tam budú. Čas z 2min 20sek padol pod 50 sek.
Z toho mi vychádza, že mi neostáva nič iné, len so všetkým experimentovať. Prípadne tam pretlačiť použitie indexu.
Win11 64b, Delphi 11.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6924
  • Karma: 44
    • Verze Delphi: W10 + D11.1
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #10 kdy: 12-12-2022, 21:24:37 »

Pokúšam sa pretlačiť použitie indexu
Kód: [Vybrat]
SELECT ...
FROM
  DEPOSITS D
  INNER JOIN OWNER_FLAT OWF ON D.FKOWNER_FLAT = OWF.IDOWNER_FLAT
  INNER JOIN FLATS F ON OWF.FKFLAT = F.IDFLATS
  INNER JOIN OWNERS O ON OWF.FKOWNERS = O.IDOWNERS
  INNER JOIN ENTRIES E ON F.FKENTRY = E.IDENTRIES
  INNER JOIN BLOCKS B ON E.FKBLOCKS = B.IDBLOCKS
  INNER JOIN FOCS FO ON B.FKFOCS = FO.IDFOCS AND D.FKFOCS = FO.IDFOCS
  INNER JOIN SERVICEPARAMETERS SP ON D.FKSERVICEPARAMETERS = SP.IDSERVICEPARAMETERS
  INNER JOIN SERVICES S ON SP.FKSERVICES = S.IDSERVICES
WHERE ...
PLAN (D INDEX (I_DEPOSITS_FP))
ORDER BY FOTITLE, BTITLE, ETITLEFULL, F.FLATNUMBER, OFULLNAME
ale v DB manažéri dostávam chybu
Citace
table OWNER_FLAT is not referenced in plan
Netuším čo to znamená.
Win11 64b, Delphi 11.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline vandrovnik

  • Guru
  • *****
  • Příspěvků: 1455
  • Karma: 52
    • Verze Delphi: 10.3
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #11 kdy: 12-12-2022, 23:39:57 »
table OWNER_FLAT is not referenced in plan
Netuším čo to znamená.

Nejspíš bys musel do plánu uvést, jak má zacházet se všemi těmi odkazovanými tabulkami - stěžuje si, že tabulka OWNER_FLAT není v plánu obsažena.

Jinak teda u FB 3 jsem se osobně nikdy nesetkal se situací, že bych musel specifikovat plán, spíš bych tipoval, že nemáš správně připravené indexy, a nebo že je potřeba aktualizovat selektivitu toho indexu, který by se mohl použít: https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref30/fblangref30-ddl-index.html#fblangref30-ddl-idx-setsttstcs

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6924
  • Karma: 44
    • Verze Delphi: W10 + D11.1
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #12 kdy: 13-12-2022, 08:57:32 »
Citace
Jinak teda u FB 3 jsem se osobně nikdy nesetkal se situací, že bych musel specifikovat plán,
To je v rámci pokusov. Či by to niečo zmenilo. Odkaz si pozriem.
Mám dopyt, ktorý trvá skoro 40 s. Ale je pravda, že vracia 139 500 záznamov.
 V DB manažéri pri druhom spustení to urobí za 17 s. Preto tie pokusy. Viem, že pozadí už existuje nejaká optimalizácia. Netuším aká.
« Poslední změna: 13-12-2022, 09:00:11 od Stanislav Hruška »
Win11 64b, Delphi 11.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline Jan Fiala

  • Hrdina
  • ****
  • Příspěvků: 344
  • Karma: 4
    • Verze Delphi: 10.4.1
    • PSPad editor
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #13 kdy: 14-12-2022, 11:11:10 »
Kolik záznamů obsahuje tabulka Deposits?
Která z těch tabulek je největší?
Někdy pomůže, když si uděláš z té největší výběr do pomocné tabulky a až tu pak teprve napojuješ přes všechny své INNER JOIN. Server pak pracuje s daleko menším počtem záznamů a pokud je ten prvotní výběr z deposits optimalizovaný, pak vše proběhne daleko rychleji.

Offline Jan Fiala

  • Hrdina
  • ****
  • Příspěvků: 344
  • Karma: 4
    • Verze Delphi: 10.4.1
    • PSPad editor
Re:Pokus: porovnanie dvoch dopytov. Hlava mi to neberie
« Odpověď #14 kdy: 14-12-2022, 11:12:36 »
Mám dopyt, ktorý trvá skoro 40 s. Ale je pravda, že vracia 139 500 záznamov.
 V DB manažéri pri druhom spustení to urobí za 17 s. Preto tie pokusy. Viem, že pozadí už existuje nejaká optimalizácia. Netuším aká.

Protože DBManager není debil a nestahuje všech 140 000 záznamů, ale stáhne a ukáže ti pouze první dávku (nastavení fetch). Proto to proběhne daleko rychleji