Pokus: porovnanie dvoch dopytov. Hlava mi to neberie

[Stanislav Hruška]

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.IDOWNERS6 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.FULLNAMETo je k tým dlhým dopytom. Bod B)

[František]

velmi som sa neponaral, ale mas tam rozdielny pocet GROUP BY stlpcov, a v druhom pripade grupujes v troch tabulkach

[Stanislav Hruška]

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.

[Jan Fiala]

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...

[pepak]

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.

[Stanislav Hruška]

Ď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.

[Jan Fiala]

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.

[Stanislav Hruška]

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.

[Jan Fiala]

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.

[Stanislav Hruška]

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.

[Stanislav Hruška]

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, OFULLNAMEale v DB manažéri dostávam chybu

table OWNER_FLAT is not referenced in plan

Netuším čo to znamená.

[vandrovnik]

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

[Stanislav Hruška]

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á.

[Jan Fiala]

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.

[Jan Fiala]

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