Skrátenie času dopytu vytvorením indexu

[Stanislav Hruška]

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

[Stanislav Hruška]

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

[pf1957]

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?

[Stanislav Hruška]

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

[pf1957]

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.

[Stanislav Hruška]

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.

[Stanislav Hruška]

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

[Stanislav Hruška]

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.

[Stanislav Hruška]

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.

[vandrovnik]

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.

[Jan Fiala]

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