Firebird SQL optimalizálás

Firebird SQL optimalizálás
2007-09-14T20:45:23+02:00
2007-09-15T16:01:08+02:00
2022-11-05T22:05:44+01:00
nova76
Van egy szálloda adatbázisa, ahol egy táblában sorba rögzítik a vendégeket:

Id, vennnev(neve), adosza(adószáma), erkezi(k), utazik.

Szeretném elkerülni, hogy egy nem teljesen épelméjű felhasználó kétszer rögzítsen be egy vendéget. Ehhez ugyan rengeteget kellene dolgoznia(duplán kell rögzíteni, érkeztetni, utaztatni, számlát kiállítani, fizettetni a vendéggel), de van aki képes rá. Igaz a fizetés többnyire elmarad, mert a vendégnek azért van esze...
A helyzet az hogy sehogyan sem tudnám kiszűrni, hiszen nincs egyedi azonosító, ami a vendéget beazonosítja. Illetve van egy adószámunk, ami jó erre, de azt nem mindenki adja meg. Viszont azeket az adószámos emberkéket szeretném legalább kiszűrni.

Ezzel meg is vannak azok az "adószámok", amik kétszer érkeznek vagy utaznak egy nap:
select ven.adosza from vendegek ven where ven.utazik>'2007.01.01' and ven.adosza is not null and ven.adosza<>'' group by ven.adosza, ven.utazik having count(*)>1 Union select ven.adosza from vendegek ven where ven.utazik>'2007.01.01' and ven.adosza is not null and ven.adosza<>'' group by ven.adosza, ven.erkezi having count(*)>1

viszont ezt hiába dobom a felhasználó elé, nem fogja egyesével átnézni, mert ezek csak adószámok. Ezért valami ilyesmi kellene:
select * from vendegek where adosza in (
select ven.adosza from vendegek ven where ven.utazik>'2007.01.01' and ven.adosza is not null and ven.adosza<>'' group by ven.adosza, ven.utazik having count(*)>1 Union select ven.adosza from vendegek ven where ven.utazik>'2007.01.01' and ven.adosza is not null and ven.adosza<>'' group by ven.adosza, ven.erkezi having count(*)>1
)

Csak hát ez így piszkosul lassú. Ha elötte kimentem az adószámokat (a memoriába) és utána az in záradékba behelyezem az union select helyett, akkor gyors. Nem lehet valahogy rákényszeríteni a FB-öt, hogy ugyanígy tegyen?
Mutasd a teljes hozzászólást!
A select mezőlistájába is beleveheted a vendég további adatait pl MAX-al:
select ven.adosza, max(ven.vennev), stb.

Az az érdekes, hogy elméletileg az FB optimizer észlelhetné, hogy az IN belső selectje nem hivatkozik a külső részre ("select * from vendegek where adosza"), így attól függetlenül elég lenne egyszer lefuttatni. Nem tudom hanyas FB-vel dolgozol, de ha nem 2-es, akkor én érdekességképpen lefuttatnám azon is.
Mutasd a teljes hozzászólást!

  • De, írhatsz olyan tárolt eljárást, aminek a kimenete egy eredményhalmaz, aztán ezt kötöd a selectbe.
    Nálad ez kb. így néz ki:
    set term !; CREATE OR ALTER PROCEDURE GET_ADOSZAMOK ( UTAZIK timestamp ) RETURNS ( ADOSZAM varchar(50) ) AS BEGIN for select ven.adosza from vendegek ven where ven.utazik>:UTAZIK and ven.adosza is not null and ven.adosza<>'' group by ven.adosza, ven.utazik having count(*)>1 into :ADOSZAM do begin SUSPEND; end for select ven.adosza from vendegek ven where ven.utazik>:UTAZIK and ven.adosza is not null and ven.adosza<>'' group by ven.adosza, ven.erkezi having count(*)>1 into :ADOSZAM do begin SUSPEND; end END! set term ;! commit;

    Aztán ezt kötöd be a következő lekérdezésbe:
    select * from vendegek where adosza in (select adoszam from GET_ADOSZAMOK('2007.01.01')
    Mutasd a teljes hozzászólást!
  • Így is ugyanaz a gond.
    A getadoszamok minden egyes vendegek sornál lefut, hiszen a tárolt eljárás végeredményét sem tudom a memóriában tartani. Tehát így is ugyanolyan lassú.
    A vendégek tábla 40-50e sor körül van, tehát a tárolt eljárás 40-50eX fut le. Még nem vártam ki, mennyi ideig tart... Ha lekérem az union selectet, akkor kapok kb 40 adószámot, kb 1ms alatt. Ha az adószámokat rakom az in záradékba így
    select * from vendegek where adosza in (80000000001, 8000000002, ...)
    , akkor ujabb 1ms a végeredmény. Csakhát ezt SQL-ben hogyan oldom meg???
    Mutasd a teljes hozzászólást!
  • Tedd egy ideiglenes táblába az IN záradékba kerülő értékeket, majd JOIN adószám alapján. Az jóval gyorsabb lesz, nekem legalábbis bevált hasonló esetekben, mivel jóval gyorsabban tud a szerver két párhuzamos datastream-et JOIN-olni, mint egy ilyen ciklus a ciklusban jellegű dolgot.

    Üdv:Alex
    Mutasd a teljes hozzászólást!
  • Csakhogy firebird alatt nincs ideiglenes tábla.

    Szerintem akkor csináld azt, hogy létrehozol egy tárolt eljárást, amiben összevágod egy stringbe az in záradékba kerülő adószámokat, majd rakd össze a végső selectet ebbe a stringbe és futtasd azt.
    Lehet ugyanis Firebirdben is egy stringben levő sql utasítást futtatni. Erre való az EXECUTE STATEMENT utasítás.
    Példa:
    EXECUTE STATEMENT 'SELECT COUNT(' || TableField || ') FROM ' || TableName || ' WHERE ' || TableField || ' = ' || CAST(:KEYVALUE AS VARCHAR(30)) INTO KeyCount;
    Mutasd a teljes hozzászólást!
  • 2.1-től (CREATE GLOBAL TEMPORARY TABLE) van, de természetesen nem erre gondoltam, hanem - helyesen fogalmazva - egy meglévő táblába kellene betenni ideiglenesen, a lekérdezés idejére. Az IN-nel az a baj, hogy korlátozott a záradékban szereplő értékek száma, ez minden RDBMS-nél így van természetesen, nemcsak FB-nél. Ráadásul az IN-es értékekre nem mindig tud jó PLAN-t készíteni az optimizer, persze, ha valaki járatos a PLAN készítésben, akkor lehet vele kísérletezni, én mostanában igen mélyen beleástam magam és hihetetlen sebességeket sikerült elérnem saját PLAN biztosításával.

    Hatékony megoldás lehet még a leszármazott táblák alkalmazása is (SELECT ... FROM (SELECT ... FROM ...)), de szintén csak 2.0-tól.
    Mutasd a teljes hozzászólást!
  • A select mezőlistájába is beleveheted a vendég további adatait pl MAX-al:
    select ven.adosza, max(ven.vennev), stb.

    Az az érdekes, hogy elméletileg az FB optimizer észlelhetné, hogy az IN belső selectje nem hivatkozik a külső részre ("select * from vendegek where adosza"), így attól függetlenül elég lenne egyszer lefuttatni. Nem tudom hanyas FB-vel dolgozol, de ha nem 2-es, akkor én érdekességképpen lefuttatnám azon is.
    Mutasd a teljes hozzászólást!
  • Köszönöm szépen a segítséget, de azt hiszem stradicé a pont.
    Mutasd a teljes hozzászólást!
Tetszett amit olvastál? Szeretnél a jövőben is értesülni a hasonló érdekességekről?
abcd