Autor Téma: Optimalizácia Query  (Přečteno 1415 krát)

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6141
  • Karma: 44
    • Verze Delphi: W10 + D11
Optimalizácia Query
« kdy: 21-09-2020, 18:05:28 »
Nedokážem optimalizovať tento dopyt
Kód: [Vybrat]
SELECT D.FKFOCS, F.SHORTTITLE,
  COUNT(DISTINCT D.FKSERVICEPARAMETERS) AS CNTSERVICE,
  EXTRACT(MONTH FROM MAX(D.DEPOSITPERIOD)) AS MAXMON,
  MAX(D.DEPOSITPERIOD) AS MAXPERIOD,
   SUM(D.DEPOSIT) AS DEPOSIT
FROM DEPOSITS D
  INNER JOIN FOCS F ON (D.FKFOCS = F.IDFOCS)
WHERE (F.ISACTIVE = 1) AND (F.DELETED = 0) AND(F.FKUSERAPPS = 1)
  AND (D.DEPOSITPERIOD BETWEEN '1.1.2016' AND '31.12.2016')
GROUP BY D.FKFOCS, F.SHORTTITLE
ORDER BY F.SHORTTITLE
Skúšal som to pomocou WITH a EXISTS. Najhoršie to je so SUM(D.DEPOSIT)
Tabuľka DEPOSITS má skoro 3,5 mil. záznamov.
V DB manažéri to trvá 4 min, v aplikácii menej ako 2 min.
Teraz časť WHERE nerozhoduje, lebo všetky záznamy vyhovujú podmienke WHERE (F.ISACTIVE = 1) AND (F.DELETED = 0) AND(F.FKUSERAPPS = 1)
W10 64b, Delphi 10.4, FireBird 3.08
Expert na kladenie nejasne formulovaných otázok.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6141
  • Karma: 44
    • Verze Delphi: W10 + D11
Re:Optimalizácia Query
« Odpověď #1 kdy: 21-09-2020, 18:57:37 »
Urobil som si rozbor údajov, ktoré mám k dispozícii a zistil som, že z agregačných funkcií potrebujem zistiť len SUM()!
Nezdá sa mi, žeby na to existovala nejaká finta.
W10 64b, Delphi 10.4, FireBird 3.08
Expert na kladenie nejasne formulovaných otázok.

Offline pf1957

  • Padawan
  • ******
  • Příspěvků: 3335
  • Karma: 139
    • Verze Delphi: D2007, XE3, DX10
Re:Optimalizácia Query
« Odpověď #2 kdy: 21-09-2020, 19:00:15 »
Najhoršie to je so SUM(D.DEPOSIT)
Tabuľka DEPOSITS má skoro 3,5 mil. záznamov.
Nezabyval jsem se vlastnim SQL dotazem, ale jestli chces secist velke mnozstvi zaznamu, tak to holt neco zabere. Obejit se to da treba tak, ze si budes nektere hodnoty pocitat prubezne v triggerech a vysledky si udrzovat v nejake extra tabulce.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6141
  • Karma: 44
    • Verze Delphi: W10 + D11
Re:Optimalizácia Query
« Odpověď #3 kdy: 21-09-2020, 19:29:23 »
Alebo, že sa na to vykašlem!
Mám tabuľku, v ktorej zákazníkovi TERAZ zobrazím výpis SVB, posledný mesiac, počet služieb a celkový predpis za.
Počet služieb a celkový predpis sú len informačné údaje. Takže ich môžem s pokojným svedomím vyhodiť von.
W10 64b, Delphi 10.4, FireBird 3.08
Expert na kladenie nejasne formulovaných otázok.

Offline vandrovnik

  • Guru
  • *****
  • Příspěvků: 1293
  • Karma: 51
    • Verze Delphi: 10.3
Re:Optimalizácia Query
« Odpověď #4 kdy: 21-09-2020, 19:34:07 »
Zkusil jsem na svém desetiletém PC posčítat údaje z 16,5 M záznamů - trvalo to 27 sekund (data už byla ve Windows cache), takže 2 nebo 4 minuty na 3,5 M záznamů se mi zdá nějak moc.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6141
  • Karma: 44
    • Verze Delphi: W10 + D11
Re:Optimalizácia Query
« Odpověď #5 kdy: 21-09-2020, 20:49:54 »
Akosi si neviem zvyknúť, že mám k dispozícii údaje a tak ich mám aj používať.
Vďaka nim som teraz Query celkom vyhodil a mám to okamžite :)
W10 64b, Delphi 10.4, FireBird 3.08
Expert na kladenie nejasne formulovaných otázok.

Offline Slappy

  • Hrdina
  • ****
  • Příspěvků: 252
  • Karma: 12
  • Slappy
    • Verze Delphi: 11 Alexandria + Vsetky :)
    • unSigned
Re:Optimalizácia Query
« Odpověď #6 kdy: 22-09-2020, 05:55:47 »
Nepoužívam SQL v Delphi vôbec (naša app nerobí s DB), takže neviem presne ako to je, ale v Microsoft SQL Management Studiu je možné debugovať SQL queries a optimalizovať ich.

Je možné zobraziť Execution plan a pozrieť si, čo sa vlastne "vnútri" deje, vyzerá to takto: https://www.apriorit.com/dev-blog/381-sql-query-optimization.

Ak je v tvojej DB je nejaký podobný nástroj, skús ho pohľadať, dajú sa s tým dosiahnuť fantastické výsledky, keď sa to SQL "rozoberie na súčiastky".

V úplne najhoršom prípade by bolo treba celú DB skonvertovať do MS SQL a oddebugovať si dané query tam, ale to je už overkill :)
Moje projekty: http://www.unsigned.sk Tvorba cool dizajnovych instalatorov v NSIS a Inno Setup. Rozsirenie pre Visual Studio a RAD Studio pre tvorbu NSIS a Inno Setup instalatorov.

Offline pf1957

  • Padawan
  • ******
  • Příspěvků: 3335
  • Karma: 139
    • Verze Delphi: D2007, XE3, DX10
Re:Optimalizácia Query
« Odpověď #7 kdy: 22-09-2020, 08:17:17 »
Excellent
Rated 1 time
V úplne najhoršom prípade by bolo treba celú DB skonvertovať do MS SQL a oddebugovať si dané query tam, ale to je už overkill :)
No hlavne by to bylo prakticky k nicemu, protoze kazdy RDBMS se chova jinak...

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6141
  • Karma: 44
    • Verze Delphi: W10 + D11
Re:Optimalizácia Query
« Odpověď #8 kdy: 22-09-2020, 09:31:10 »
Sú tam nástroje na plán, analýzu... Problém je vo mne. Že to neviem čítať a veľmi tomu nerozumiem :(
W10 64b, Delphi 10.4, FireBird 3.08
Expert na kladenie nejasne formulovaných otázok.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6141
  • Karma: 44
    • Verze Delphi: W10 + D11
Re:Optimalizácia Query
« Odpověď #9 kdy: 22-09-2020, 16:31:44 »
Po analýze problému a uvedomení si súvislostí som prišiel na skutočnosť, že mi stačí do jednej (krátkej) tabuľky pridať jedno pole a môžem zabudnúť na nejaký Sum() :)
"Obsluha" hodnoty toho poľa nebude ťažká. Aj keď to bude na viacerých miestach. Len 2 alebo 3 formuláre.
W10 64b, Delphi 10.4, FireBird 3.08
Expert na kladenie nejasne formulovaných otázok.

Offline vandrovnik

  • Guru
  • *****
  • Příspěvků: 1293
  • Karma: 51
    • Verze Delphi: 10.3
Re:Optimalizácia Query
« Odpověď #10 kdy: 22-09-2020, 17:48:26 »
Pokud tou "obsluhou toho pole" máš na mysli, že si budeš z aplikace průběžně udržovat hodnotu součtu, tak to je špatně :) Dělá se to pomocí triggerů v databázi, abys nemusel řešit, které 2-3 formuláře to mají hlídat (a až časem uděláš další takový formulář, tak ještě ten...).

Offline Jirka

  • Hrdina
  • ****
  • Příspěvků: 441
  • Karma: 9
    • Verze Delphi: XE2
Re:Optimalizácia Query
« Odpověď #11 kdy: 22-09-2020, 18:57:13 »
Zkusil jsem na svém desetiletém PC posčítat údaje z 16,5 M záznamů - trvalo to 27 sekund (data už byla ve Windows cache), takže 2 nebo 4 minuty na 3,5 M záznamů se mi zdá nějak moc.
Dal by se někam vystavit skript s daty - at si to taky mohu zkusit :-)

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6141
  • Karma: 44
    • Verze Delphi: W10 + D11
Re:Optimalizácia Query
« Odpověď #12 kdy: 22-09-2020, 19:08:21 »
Pokud tou "obsluhou toho pole" máš na mysli, že si budeš z aplikace průběžně udržovat hodnotu součtu, tak to je špatně :) Dělá se to pomocí triggerů v databázi, abys nemusel řešit, které 2-3 formuláře to mají hlídat (a až časem uděláš další takový formulář, tak ještě ten...).
Nie, nemám toto na mysli. Ja ten súčet ako taký nepotrebujem. Ja len potrebujem vedieť či v danom mesiaci pre dané SVB bola vykonaná akákoľvek úhrada predpisu. To som sa snažil zistiť tým Sum() a kontrolou či je výsledok 0.
Ak teraz niečo uhradím (mám aj automatické úhrady a pod.), tak si to poznačím do inej krátkej tabuľky a je vymaľované.
Tému považujte za uzatvorenú. Pracujem na realizácii opísaného.
W10 64b, Delphi 10.4, FireBird 3.08
Expert na kladenie nejasne formulovaných otázok.

Offline vandrovnik

  • Guru
  • *****
  • Příspěvků: 1293
  • Karma: 51
    • Verze Delphi: 10.3
Re:Optimalizácia Query
« Odpověď #13 kdy: 22-09-2020, 19:48:45 »
Dal by se někam vystavit skript s daty - at si to taky mohu zkusit :-)

To bohužel nemůžu, jsou to data z ostré databáze. Tabulka má 90 sloupců, já sčítal jeden z nich typu numeric(12,4), pro účely toho součtu přetypovaný na numeric(16,4), aspoň myslím :)

Offline Jan Fiala

  • Hrdina
  • ****
  • Příspěvků: 256
  • Karma: 3
    • Verze Delphi: 10.4.1
    • PSPad editor
Re:Optimalizácia Query
« Odpověď #14 kdy: 27-09-2020, 12:03:14 »
Píšeš, že tabulka má 3,5 milionu záznamů. Všechny jsou v tom tebou zjišťovaném období v roce 2016?
Je v tabulce index na DEPOSITPERIOD? Použila Interbase zmiňovaný index v tom dotazu nebo procházi scanem všechny záznamy?

Děláme s MS SQL a desítky milionů záznamů v tabulkách nejsou nic výjimečného. Občas se setkáme s tím, že si to MS SQL optimalizátor si vyhodnotí dotaz "divně" a je třeba mu index v SQL vnutit pomocí WITH. Pak zpracování dotazu místo minuty proběhne v řádu sekund.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6141
  • Karma: 44
    • Verze Delphi: W10 + D11
Re:Optimalizácia Query
« Odpověď #15 kdy: 27-09-2020, 12:25:27 »
Píšeš, že tabulka má 3,5 milionu záznamů. Všechny jsou v tom tebou zjišťovaném období v roce 2016?
Je v tabulce index na DEPOSITPERIOD? Použila Interbase zmiňovaný index v tom dotazu nebo procházi scanem všechny záznamy?
.
Děláme s MS SQL a desítky milionů záznamů v tabulkách nejsou nic výjimečného. Občas se setkáme s tím, že si to MS SQL optimalizátor si vyhodnotí dotaz "divně" a je třeba mu index v SQL vnutit pomocí WITH. Pak zpracování dotazu místo minuty proběhne v řádu sekund.
Ide o generované testovacie údaje. V tomto prípade sa prechádza celá tabuľka
Všetky údaje patria do roku 2016
Áno, ten index mám
.
Teraz mi ten dopyt zbehne za 6-7 sekúnd. S tým som spokojný, lebo sa spúšťa len pri štarte aplikácie. Ostatné existujúce volania tohto dopytu som už odstránil.
Pochopením toho čom mám k dispozícii a doplnením poľa do jednej krátkej tabuľky.
.
Pri daných vygenerovaných údajoch (nie vždy mám dodržané správne väzby medzi tabuľkami - cudzie kľúče + plus k tomu nejaká podmienka) sa mi jednotlivé formuláre otvárajú rádovo 0 až 3,5 sekundy. Čo je oproti pôvodnému stavu vynikajúci výsledok.
.
Až na dve výnimky s 11 a 6 sekundami. To je dané skutočnosťou, že na prvom používam dva a druhom jeden podformulár, kde generujem 3 (druhy) * 10 * 5 komponentov. Jeden z nich je ComboBox a tým napĺňam Items. Možnože pri tom napĺňaní mám medzeru ???  To mi  žerie najviac času.
.
Už nemám čo riešiť. Ak sa program dostane von, tak k tak veľkému naplneniu tabuľky určite nedôjde.
Počet záznamov tej dlhej tabuľky je daný vzorcom: pre jeden mesiac = počet všetkých bytov * počet služeb. Ja mám teraz 31 služieb. V praxi ich bude určite menej. Tak 20 - 25.
W10 64b, Delphi 10.4, FireBird 3.08
Expert na kladenie nejasne formulovaných otázok.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6141
  • Karma: 44
    • Verze Delphi: W10 + D11
Re:Optimalizácia Query
« Odpověď #16 kdy: 27-09-2020, 12:46:09 »
Ešte dopĺňam, že dáta som generoval pre 100 SVB a každé má 320 bytov. Ak zoberieme 3 obyvateľov na jeden byt, tak to 960 000 ľudí. Takých miest máme len pár. Pričom počet obyvateľom môjho mesta je 33 860 ;D . Je jasné, že taký zákazník sa nikdy nenájde.
Pri generovaní predpisov, to je tých 3,5 mil, mi generátor skolaboval. Malo ich byť okolo 6,4 mil.
W10 64b, Delphi 10.4, FireBird 3.08
Expert na kladenie nejasne formulovaných otázok.