Dotaz: Optimalizácia selectu s in klauzulou s veľa riadkami

[Peter]

Dobrý deň vám prajem.

Mám tento select, ktorý síce funguje v poriadku, ale keď mám v tabuľke veľa dát, tak sa vykonáva veľmi dlho - momentálne je to cca 1,5 minúty:

select ID from Vydajka
where (
  (IdentifikatorStromu,IdentifikatorRodica) in (select IdentifikatorStromu,max(IdentifikatorRodica) from Vydajka group by IdentifikatorStromu) and
  Ciselnik_StavVyplnovania_ID="5" and
  DatumVydania<=DATE_ADD(CURDATE(),INTERVAL -30 DAY)
)

a snažil som sa ho zoptimalizovať pomocou temp tabuľky, len vážne netuším či to je takto dobre (hlavne sa neviem dopracovať k tomu ako sql vlastne interpretuje zdvojený "in")

create temporary table if not exists transfer as (select IdentifikatorStromu as d1,max(IdentifikatorRodica) as d2 from Vydajka group by IdentifikatorStromu);
select ID from Vydajka, transfer
where (
  identifikatorStromu=transfer.d1 and
  identifikatorRodica=transfer.d2 and
  Ciselnik_StavVyplnovania_ID="5" and
  DatumVydania<=DATE_ADD(CURDATE(),INTERVAL -30 DAY)
)

Za odpoveď, poprípade iné návrhy na optimalizáciu vám vopred veľmi pekne ďakujem.

Odpovědi

[Kit]

Zkus místo IN použít JOIN.

[Lyco]

dost možná není problém v dotazu ale v indexech.

Pošli nám EXPLAIN původního dotazu, jakou DB používáš, strukturu všech tabulek z dotazu (včetně indexů) a výsledek dotazů

select count(*) from Vydajka;
select count(*) from (select IdentifikatorStromu from Vydajka group by IdentifikatorStromu) t;
select count(*) from Vydajka where Ciselnik_StavVyplnovania_ID="5";
select count(*) from Vydajka DatumVydania<=DATE_ADD(CURDATE(),INTERVAL -30 DAY);

BTW ta konstrukce

SELECT id FROM t WHERE (x, y) in (SELECT x, MAX(y) FROM t GROUP BY x)

se mi nelíbí, co je její účel? Čím je maximální IdentifikatorRodica významný? To je nějaké hledání ve stromové struktuře? Pokud ano, asi se to rozbije, pokud se někdy záznamy přečíslují.

[Tomáš]

in operátor je zakuklený join v případě kdy je na pravé straně nějaký výraz nad tabulkou (což máte). A join je relativně nejdražší operace ze všech. Pokud hledáte poslední záznam z každé skupiny IndentifikátorStromu zkuste použít OLAP funkce. Tedy asi takto:

select 
  T.ID
from 
  (
    select
      V.*
      ,rank() over (partition by IndentifikátorStromu order by IdentifikatorRodica desc) as rnk
    from Vydajka V
  ) T
where T.rnk=1 
  and Ciselnik_StavVyplnovania_ID="5" 
  and DatumVydania<=DATE_ADD(CURDATE(),INTERVAL -30 DAY)
;

Zavedení temp tabulky má smysl pouze v tom případě, že potřebujete data z ní použít 2x (což není Váš případ). Jinak optimizer si dělá temp tabulky automaticky vnitřně pokud nejdou operace seřadit do pipeline. Libovolná operace hash/merge join, group by,sort vede na vnitřní temp tabulky. Ale naopak nested loop join lze řadit do pipeline stejně jako where/having filtraci s konstantou-

Zkuste si rozmyslet zda by filtrační podmínky na Ciselnik_StavVyplnovania_ID a DatumVydania neměly být ve vnitřním selectu. Mnou uvedený přepis má stejnou sémantiku jako Váš select, ale možná chcete trošku něco jiného.

Pokud používáte mysql/maria/... tak ty, pokud si matně pamatuji, OLAP funkce neuměly. Tam by jste musel sáhnout po kurzoru.

Pokud by jste byl na Oracle tak by šlo použít ještě lepší optimalizaci pomocí konstrukce max(ID) keep (last order by IdentifikatorRodica)

[vlasta]

mozna bych zacal s tim, ze bych vyfiltroval zaznamy uz v tom group by poddotazu a ten nadrazeny bych pouzil jenom pro zjisteni id na zaklade te dvojice identifikatoru, bude mene dat na groupovani a za predpokladu, ze ta dvojice je indexovana, by to melo byt rychlejsi.

Lepsi by samozrejme bylo pouziti nejake rankovani pomoci analyticke funkce, nebo jak umi oracle keep(dense_rank), ale podle konstrukci predpokladam, ze se jedna o mysql a tam to resit pomoci lokalnich promennych s partitioningem bude asi necitelny opruz.

SELECT ID
  FROM Vydajka
 WHERE (IdentifikatorStromu, IdentifikatorRodica) IN (
   SELECT IdentifikatorStromu,
          MAX(IdentifikatorRodica)
     FROM Vydajka 
    WHERE Ciselnik_StavVyplnovania_ID = "5"
      AND DatumVydania <= DATE_ADD(CURDATE(),INTERVAL - 30 DAY)
    GROUP BY IdentifikatorStromu)

[krocan]

To je ovsem zcela jiny dotaz a bude mit jiny vysledek.

[vlasta]

Pravda, trosku jsem se nechal unest jako pentium, bylo by to rychlejsi, ale nejspis blbe, s takovymi vazbami bez analytickych funkci bude asi 1,5 minuty uspech ):

[kaaja]

Problém je samozdřejmě v tom, že select IdentifikatorStromu,max(IdentifikatorRodica) from Vydajka group by IdentifikatorStromu musí projít celou tabulku Vydajka (případně pokud existuje index nad IdentifikatorStromu, IdentifikatorRodica tak projde tenhle index), což je pro něj dost složité.

Klidně je možné, že dotaz upravit moc nepůjde. Pokud není index nad (IdentifikatorStromu, IdentifikatorRodica) asi bych ho zavedl, ale nemusí to být řešení.

Teoreticky by například mohlo být řešení něco takového

select ID from Vydajka v
where 
 Ciselnik_StavVyplnovania_ID="5" and
  DatumVydania<=DATE_ADD(CURDATE(),INTERVAL -30 DAY)
and not exists (
     select 0 from Vydajka vp 
       where vp.IdentifikatorStromu = v.IdentifikatorStromu 
        and vp.IdentifikatorRodica > v.IdentifikatorRodica
)

ale fungovalo by to pouze pokud by podmínky na datum a stav nechali jen málo řádků, pak by se pomocí nestedloopů mohlo jen párkrát zeptat do případného indexu. Pokud se nelíbí not exists mělo by fungovat také toto

select v.ID from Vydajka v
left join Vydajka vp on vp.IdentifikatorStromu = v.IdentifikatorStromu and vp.IdentifikatorRodica > v.IdentifikatorRodica
where vp.ID is null and 
  v.Ciselnik_StavVyplnovania_ID="5" and
  v.DatumVydania<=DATE_ADD(CURDATE(),INTERVAL -30 DAY)

Asi bych také uvažoval o nějakém aplikačním řešení. Je možné, že tabulka Vydajka se plní tak, že by bylo možné si "výsledky" poddotazu někam průběžně ukládat při změně dat. Případně pokud nejsou vždy potřeba zcela aktuální data, tak by bylo možné na poddotaz udělat materializované view s nějakou dobou obnovování a do dotazu připojovat toto view.