Autor Téma: Skrátenie času dopytu vytvorením indexu  (Přečteno 668 krát)

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 7303
  • Karma: 44
    • Verze Delphi: W11 + D11.3.1
Skrátenie času dopytu vytvorením indexu
« kdy: 04-12-2022, 11:42:20 »

Kód: [Vybrat]
CREATE TABLE DEPOSITS (
  IDDEPOSITS                    PRIMARYKEY,
  FKFOCS                        FOREIGNKEY,
  FKOWNER_FLAT                  FOREIGNKEY,
  FKSERVICEPARAMETERS           FOREIGNKEY,
  DEPOSIT                         CURRENCY NOT NULL,
  DEPOSITPERIOD                   DATEFROM,
  PAY                             CURRENCY DEFAULT 0 NOT NULL,
  CONSTRAINT PK_DEPOSITS PRIMARY KEY (IDDEPOSITS)
);
CREATE ASC INDEX DEPOSITS_IDX1 ON DEPOSITS (DEPOSITPERIOD);
CREATE ASC INDEX I_DEPOSITS_ ON DEPOSITS (DEPOSITPERIOD, FKFOCS); //  Pridané
Text dopytu:
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.DEPOSITPERIOD BETWEEN '1.1.2022' AND '1.12.2022' AND D.FKFOCS = 3130
  GROUP BY D.FKOWNER_FLAT)   
//  Táto časť nie je zaujímavá. Je rýchla
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
Údaje o záznamoch - Počet:
  • záznamov mám 16 740 000
  • FOC 15 = 1 116 000 záznamov na FOC
  • FKOWNER_FLAT 4 500
Nenastolo žiadne skrátenie času vykonania dopytu. Viď druhý index. Čo ma prekvapilo.
Ako mám vlastne definovať indexy, aby som mal z toho zisk. Najčastejšie podmienky vo WHERE sú na
  • DEPOSITPERIOD
  • FKFOCS a DEPOSITPERIOD
  • FKOWNER_FLAT a DEPOSITPERIOD - + prípadne FKFOCS
Analýza plánu
Kód: [Vybrat]
Select Expression
    -> Nested Loop Join (inner)
        -> Nested Loop Join (inner)
            -> Aggregate
                -> Filter
                    -> Table "DEPOSITS" as "D D" Access By ID
                        -> Index "FK_DEPOSIT_OWF" Full Scan
                            -> Bitmap And
                                -> Bitmap
                                    -> Index "DEPOSITS_IDX1" Range Scan (lower bound: 1/1, upper bound: 1/1)
                                -> Bitmap
                                    -> Index "FK_DEPOSITS_FOCS" Range Scan (full match)
            -> Filter
                -> Table "OWNER_FLAT" as "OWF" Access By ID
                    -> Bitmap
                        -> Index "PK_OWNER_FLAT" Unique Scan
        -> Filter
            -> Table "FLATS" as "F" Access By ID
                -> Bitmap
                    -> Index "PKFLATS" Unique Scan
        -> Filter
            -> Table "OWNERS" as "O" Access By ID
                -> Bitmap
                    -> Index "PK_OWNERS" Unique Scan
Win11 64b, Delphi 11.3.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 7303
  • Karma: 44
    • Verze Delphi: W11 + D11.3.1
Re:Skrátenie času dopytu vytvorením indexu
« Odpověď #1 kdy: 04-12-2022, 12:59:22 »
+ otázka B)
Má význam robiť index nad dátumom DEPOSITPERIOD? Pomôže mi to v niečom? Momentálne ho mám definovaný
Viem, že cudzie kľúče sú automaticky indexované.
Win11 64b, Delphi 11.3.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline pf1957

  • Padawan
  • ******
  • Příspěvků: 3516
  • Karma: 139
    • Verze Delphi: D2007, XE3, DX10
Re:Skrátenie času dopytu vytvorením indexu
« Odpověď #2 kdy: 04-12-2022, 13:04:36 »
Má význam robiť index nad dátumom DEPOSITPERIOD? Pomôže mi to v niečom? Momentálne ho mám definovaný
Viem, že cudzie kľúče sú automaticky indexované.
Hele, co kdybys postupoval metodou blbec a ty indexy nasazel vsude (pozor na FB, ktery nekde musi mit ascending i descending index), zmeril si to a pak je odebiral a nechal tam jen ty, kterymi dosahnes nejlepsich casu?

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 7303
  • Karma: 44
    • Verze Delphi: W11 + D11.3.1
Re:Skrátenie času dopytu vytvorením indexu
« Odpověď #3 kdy: 04-12-2022, 15:25:24 »
Metóda blbec je moja najobľúbenejšia a najviac používaná. Akurát ju nazývam tak krajšie, ale menej výstižne. Pokus-omyl.
Mám len jeden index - nad dátumom.
Skúsil som DEPOSITPERIOD + FKFOCS a DEPSITPERIOD + FKFOCS + FKOWNER_FLAT. Už som ich vyhodil. Viac kombinácii asi nemá zmysel skúšať.
Najväčšie zrýchlenie dostávam ak
  • vložím do WHERE FKFOCS = :FKFOCS. To sa z 30 a viac sekúnd razom dostanem na 0,5 s
  • občas pomôže ak prepojím FKFOCS v dvoch tabuľkách. Zdá sa mi, že až raz ;D  Aj keď ten INNER je navyše. Toto je jedine pokus-omyl
Mne sa tam nepáči " -> Index "FK_DEPOSIT_OWF" Full Scan". Neviem ako by som sa ho zbavil. Tu by určite pomohlo pretlačenie použitia indexu.
Win11 64b, Delphi 11.3.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline pf1957

  • Padawan
  • ******
  • Příspěvků: 3516
  • Karma: 139
    • Verze Delphi: D2007, XE3, DX10
Re:Skrátenie času dopytu vytvorením indexu
« Odpověď #4 kdy: 04-12-2022, 15:53:29 »
Mne sa tam nepáči " -> Index "FK_DEPOSIT_OWF" Full Scan". Neviem ako by som sa ho zbavil. Tu by určite pomohlo pretlačenie použitia indexu.
To je nejaky odkaz na umely PK v propojovaci tabulce m:n mezi OWNER a FLAT? Nebo co to je? Tam se podivej, jak mas indexy. A u te periody zkus udelat asc i desc index, jestli to nepomuze.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 7303
  • Karma: 44
    • Verze Delphi: W11 + D11.3.1
Re:Skrátenie času dopytu vytvorením indexu
« Odpověď #5 kdy: 04-12-2022, 16:08:17 »
Ani neviem čo to presne je. Pozriem sa na to lepšie.
Skúšam rôzne indexy. Pri dátumoch mením ASC a DSC. Nič nepomáha. Keď zoberiem do úvahy počet záznamov 16,8 Mil a prvý čas 10 -11 sek., tak si nemyslím, že to zrazím nižšie. Jedná sa o to CTE
Kód: [Vybrat]
(SELECT D.FKOWNER_FLAT, SUM(D.DEPOSIT) AS DEPOSIT, SUM(D.PAY) AS PAID  FROM DEPOSITS D  WHERE D.DEPOSITPERIOD BETWEEN '1.1.2022' AND '1.12.2022' AND D.FKFOCS = 3130  GROUP BY D.FKOWNER_FLAT)Vyhodím to ostatné a uvidím.
Win11 64b, Delphi 11.3.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 7303
  • Karma: 44
    • Verze Delphi: W11 + D11.3.1
Re:Skrátenie času dopytu vytvorením indexu
« Odpověď #6 kdy: 04-12-2022, 16:33:06 »
Hm, takže vyšiel som z prvého plánu a vytvoril index FKOWNER_FLAT + FKFOCS. Nový plán je na druhom obrázku.
Čas zhruba polovičný!!! Považujem to za veľký úspech. Som zvedavý, kde všade sa to prejaví.
Win11 64b, Delphi 11.3.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 7303
  • Karma: 44
    • Verze Delphi: W11 + D11.3.1
Re:Skrátenie času dopytu vytvorením indexu
« Odpověď #7 kdy: 04-12-2022, 16:42:59 »
Do čerta, NIKDE :'(
Nerozumiem tomu. Ale už to nechám tak.
V jednom prípade mám mierne zlepšenie. Z 28 na 22 s.
« Poslední změna: 04-12-2022, 17:04:02 od Stanislav Hruška »
Win11 64b, Delphi 11.3.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 7303
  • Karma: 44
    • Verze Delphi: W11 + D11.3.1
Re:Skrátenie času dopytu vytvorením indexu
« Odpověď #8 kdy: 04-12-2022, 18:45:58 »
Uplatňuje sa mi len jeden index. Ten ponechám.
Ak spustím "debug exe", to jest mimo Delphi, tak sa dostávam z pôvodných 27 (22 s indexom) na 13 s. Predpokladám, že pri release to bude ešte menej. Snáď tých 10 s - to už je prijateľné. Ešte sa tým potrápim tak, že Query spustím len raz. Nie ako teraz 3x - kvôli podformulárom. Viď diskusiu v "Obecné"
Nemá význam ďalej sa s tým trápiť a niečo na tom meniť.
Pri krátkych časoch (do 5-6 s) nevidieť zmenu. Aj keď som aspoň tú sekundu čakal. Ale to meranie času je také aké je...
Uzatváram tému.
« Poslední změna: 04-12-2022, 18:57:52 od Stanislav Hruška »
Win11 64b, Delphi 11.3.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline vandrovnik

  • Padawan
  • ******
  • Příspěvků: 1528
  • Karma: 52
    • Verze Delphi: 11.3
Re:Skrátenie času dopytu vytvorením indexu
« Odpověď #9 kdy: 10-03-2023, 23:03:15 »
WHERE D.DEPOSITPERIOD BETWEEN '1.1.2022' AND '1.12.2022' AND D.FKFOCS = 3130

Zkusil bych udělat složený index na polích FKFOCS a DEPOSITPERIOD
Podle toho plánu bych řekl, že nyní používá dva samostatné indexy; když vytvoříš jeden složený, mělo by to být rychlejší.

Jen teda nepoužívám BETWEEN, tak nevím, jestli ASC nebo DESC index. Popř. místo BETWEEN použít něco jako:
WHERE D.DEPOSITPERIOD>='1.1.2022' AND D.DEPOSITPERIOD<='1.12.2022' AND D.FKFOCS = 3130
Pokud by pořád měl snahu používat dva indexy místo jednoho, šlo by u jedné z těch nerovností k DEPOSITPERIOD třeba přičíst nulu apod.

Offline Jan Fiala

  • Hrdina
  • ****
  • Příspěvků: 412
  • Karma: 4
    • Verze Delphi: 10.4.1
    • PSPad editor
Re:Skrátenie času dopytu vytvorením indexu
« Odpověď #10 kdy: 12-03-2023, 13:03:10 »
Nekdy je rychlejší vybrat surová data do pomocné tabulky (ideálně paměťové) bez agregací a až pak na to pustit agregaci (SUM apod.)
Samozřejmě na straně SQL serveru, ne na klientovi.
Tohle se líp řeší v uložených procedurách. Pokud to chceš jen SQL dotazem, tak zkusit udělat výběr bez SUM, omezený tvými podmínkami a až na ten výber použít další select, který ti to vysoučtuje. Snad to FB zvládne. Něco jako:

Kód: [Vybrat]
SELECT tmp.FKOWNER_FLAT, SUM(tmp.DEPOSIT) AS DEPOSIT, SUM(tmp.PAY) AS PAID 
  from (
    SELECT D.FKOWNER_FLAT, D.DEPOSIT, D.PAY 
      FROM DEPOSITS D 
      WHERE D.DEPOSITPERIOD BETWEEN '1.1.2022' AND '1.12.2022' AND D.FKFOCS = 3130
      ) tmp       
GROUP BY tmp.FKOWNER_FLAT