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

Offline Stanislav Hruška

  • Padawan
  • ******
  • Příspěvků: 6924
  • Karma: 44
    • Verze Delphi: W10 + D11.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.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: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.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline pf1957

  • Padawan
  • ******
  • Příspěvků: 3479
  • 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ů: 6924
  • Karma: 44
    • Verze Delphi: W10 + D11.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.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.

Offline pf1957

  • Padawan
  • ******
  • Příspěvků: 3479
  • 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ů: 6924
  • Karma: 44
    • Verze Delphi: W10 + D11.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.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: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.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: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.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: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.1, FireBird 4.01
Expert na kladenie nejasne formulovaných otázok.