MEGOLDVA - SQL gyorsítása - ismétlődő műveletek

Adatbázis: SQL, XML DB

Sziasztok,

tudnátok pár tippet adni, miként lehetne felgyorsítani egy SQL lekérdezést (MS SQL 2000-en), ahol azonos műveltet kell többször végrehajtani. A következőről van szó: a nyitott tételeket kell kilistáznom ügyfelenként összesítve az alábbi mezőkkel:

Ügyfél
Tartozás összesen
Lejárt tartozás
30 napon belül esedékes
30-60 napon belül esedékes
60 napon túl esedékes
30 napon belül lejárt
30-60 napon belül belül lejárt
60 napon túl lejárt

A riport megvan, de nagyon lassú. Maga az alap egyszerű (ld. lent), a gondjaim a következők:
1.
Az "Összeg"-et kalkulálni kell (kedvezmények, árfolyamkülönbözet stb. elég hosszú műveletsor extra case vizsgálatokkal), azaz több ezer rekord esetében soronként ugyanazt a műveletet hajtja végre a szerver min. 8-szor. Sajnos függvényt nem tudok, használni, mert Crystal Reporthoz kellene és az nem szereti.
2.
Ha lehet már a szerveren szeretném elvégezni az összesítést, mert a Crystalt ebben is lassúnak tartom.

DECLARE @RefDatumPlusz30 datetime
Set @RefDatumPlusz30 = DATEADD(dd, 30, GETDATE())
DECLARE @RefDatumPlusz60 datetime
Set @RefDatumPlusz60 = DATEADD(dd, 60, GETDATE())
DECLARE @RefDatumMin30 datetime
Set @RefDatumMin30 = DATEADD(dd, -30, GETDATE())
DECLARE @RefDatumMin60 datetime
Set @RefDatumMin60 = DATEADD(dd, -60, GETDATE())

SELECT
BP_ID,
SUM(Összeg),
SUM(CASE Esedekes <= GETDATE() THEN Összeg ELSE 0) AS Lejárt,
SUM(CASE Esedekes > GETDATE() THEN Összeg ELSE 0) AS Esedékes,
SUM(CASE Esedekes >= @RefDatumMin30 AND Esedekes < GETDATE() THEN Összeg ELSE 0) AS Lejárt30,
SUM(CASE Esedekes >= @RefDatumMin30 AND Esedekes < @RefDatumMin60 THEN Összeg ELSE 0) AS Lejárt60,
SUM(CASE Esedekes <= @RefDatumMin60, GETDATE()) THEN Összeg ELSE 0) AS Lejárt60Plusz,
SUM(CASE Esedekes < @RefDatumPlusz30 AND Esedekes > GETDATE() THEN Összeg ELSE 0) AS Esedekes30,
SUM(CASE Esedekes >= @RefDatumPlusz30 AND Esedekes < @RefDatumPlusz60 THEN Összeg ELSE 0) AS Esedekes60,
SUM(CASE Esedekes >= @RefDatumPlusz60 THEN Összeg ELSE 0) AS Esedekes60Plusz
FROM
NyitottTételek
GROUP BY
BP_ID

Előre is kösz a tippeket!

  • 4260 megtekintés

( uid_17626 | 2009. 11. 23., h – 15:24 )

Nem ismerem az MSSQL-t, de a MySQl-nél az alábbi dolgo gyorsít a lekérdezésen:

WHERE legtöbbet_szűrő_kifejezés_ AND kevesebbet__szűrő_kifejezés
a legtöbbet_szűrő_kifejeézés által használt tábla legyen indexelt.

Amúgy az említett feladatot én úgy oldottam meg, hogy van egy "kifizetés_kelte" mező és egy "fizetesi_hatarido" mező, ami date('YYYY-mm-dd'), ha nincs kifizetve, akkor 0000-00-00, erre meg gyorsan keres az sql (100.000 rekord van fent).

Ha a számla egy céghez kötödik, akkor én a a lekérdezést a cég azonosítójával kezdem (WEHERE partner_id = '$pid' AND ...)
7 év alatt rájöttem, hogy a számlán nem szabad tárolni a semmit sem dinamikusan, mindent sajnos le kell tárolni mégegyszer sql-ben. Ilyen a termék neve is, mert, ha megváltozik, akkor a régi számlán is megváltozik.
Arra is rájöttem, hogy a kedvezményes árat kell elraktároznom és nema frankó árat + a kedvezményt amit majd realtiem számolok.
Igazából számlák esetén az sql-t adatbázis képességeit semennyire nem kell használnom, csak egy antúr select és kész. Eza fajta hozzáállás nagyobb adatbázist, de gyorsabb elérést hozott nekem.

Sz*r forrásból nehéz aranyat csinálni.
De ilyenkor is van megoldás.
ETL - Extract, Target, Load
azt szoktuk csinálni, hogy lemásoljuk a minket érdeklő adatokat olyan formába, hogy a lekérdezéseink gyorsan fussanak rajta. Azt meg tetszőlegesen lehet indexelni, stb.

--
Ami elől menekülsz, az után szaladsz.

( bambano | 2009. 11. 23., h – 18:02 )

Ezt postgresben úgy szoktam, hogy kivonom az aktuális dátumból a tartozás keletkezési időpontját, azt kerek hónapra kerekítem és eszerint csinálok egy group-ot. Ez pár sor eredményt ad, azt összeadom programból vagy valami másból.

mssql-t nem ismerem.

( samuf v | 2009. 11. 23., h – 18:18 )

Nem lehet, hogy elfogy a memória és azért lassul be a lekérdezés?

"Sajnos függvényt nem tudok, használni, mert ..."
Ezalatt a tárolt eljárást érted?

Egy 70000-es rekordszámnál már előfordulhat, hogy az 1-2G ram nem elég egy ilyen lekérdezéshez. Főleg, ha az "Esedekes" és a "BP_ID" oszlopok nincsenek index-elve, és gondolom nem csak ennek a lekérdezésnek a kiszolgálása a dolga a szervernek.

Bár nem tudom, hogy a MsSQL, hogyan kezeli a group by-t, de feltételezem a teljes eredményhalmazt tárolnia kell, amíg ki nem értékeli az összes rekordot. Szóval lehet, hogy egy tárolt eljárás is segítene for select-el (MsSQL-hez nem értek, de biztos van benne).


(firebird-ben)

for select distinct BP_ID from NyitottTételek
into :BP_ID do
begin
SELECT
SUM(Összeg),
SUM(CASE Esedekes <= GETDATE() THEN Összeg ELSE 0) AS Lejárt,
...
FROM NyitottTételek where BP_ID = :BP_ID
into :osszeg, ...

sussped;
end

Ha tárolt eljárást nem is tudsz, használni a Crystal Report miatt?! próbáld ki nálam volt, hogy ez a megoldás nagyságrendekkel gyorsabban eredményt adott.

Azt írta: "lassul be". Ez helyhatározói toldalék, tehát a kérdés, hogy hova lassul be, jogos.

Más kérdés, hogy egy 3 giga ramos régi szerverem 50-100 millió rekordos nagyságrenddel szokott szórakozni, a 70 ezer rekord az a napi bővülés nagyjából. Ja, ez postgres.

Egy mostani modernebb szerverprociban van 6 mega cache, abban elfér 70 ezer rekord, ha 89 bájt vagy annál kevesebb a rekordméret. Én nem látom itt a komoly terhelésre utaló jeleket.

Gondolom az a 100 millió rekordos tábla megfelelően van index-elve és egy sima "select * from tabla"-nak nem kell túl sok memória. Az is lehet, hogy az egy tranzakcióra kiosztható memória korlátozva van, akkor pedig lehet akármennyi memória a gépben. Szóval lehet, hogy nem is a select-el van a gond.

Egyébként meg itt mindenki magyar szakra járt?

Én ezt a problémát az jó sok tempnek tűnő táblával oldanám meg.
Egy robot ami a 100 megát megfelelő igények szerint kialakított táblába tolja, x időnként vagy eredeti adaton végzett update esetén azonnal frissíti a tempet (utóbbi nem robot hanem a programba kell beépíteni). A táblát úgy kell felépíteni, hogy ne kelljen join-olni magyarán egy egyszerű selet from legyen az egész jó sok indexszel a szükséges mezőkön. Igaz ez az egész ad egy pofont az adattárolásnak, viszont kvázi realtime tudsz keresni.
Meg lehet bolondítani memóriába gyorstárazással, sql query gyorstárral mint plusz rétegek.

Volt ilyesmiről szó meetuppon Turulcsirip témakörében:
http://blog.meetup.hu/2009/11/09/novemberi-videok/
2. videó első 2 perc kb a lényeg

szolgaltataslassulas, daemonok... ajaj. ez a lazy cache is vicc, kb minden cache igy mukodik.. process forking, meg bash.. khm, khm. :)

tudna irni vki komplex peldat erre? kivancsi vagyok, hogy a sajat frameworkomben milyen nehez kifejezni/skalazni egy konkret feladatot. akar privatban, NDAval, akarmivel :)

(nem hiszem, h csak ilyen ganyul lehetne megoldani)

Én is ezt javasoltam mivel az eredeti adatokat csak nézheti és nem piszkálhatja. Gyorsabb egy kívánt adatok dump majd betöltés és abban keresés, mint így kínlódni.

Megjegyzem mivel a feladat naphoz (YYYY-mm-dd) van kötve, mint legkisebb idő egységhez így simán lehetne hajnalban futtatni robot/daemon-ként az appot, ami elvégzi a kívánt feladatokat.
Persze feltéve, hogy a cég egy időzónákon nagyon nem ível át (24h-(app futtatási idő)).

Mindenhogy gány ez az egész, de meg kell találni a legoptimálisabb megoldást amivel a lehető legpontosabb információk kérhetők le. (gondolok itt az utólag sztornóra)

( uid_4656 v | 2009. 11. 23., h – 18:56 )

Erre pedig egy függvény lenne jó :-(

group by rollup vagy group by cube nem jó valamilyen ablakozással az "esedekes" oszlopra?

( illiasgb | 2009. 11. 23., h – 19:39 )

Helló!

Össze dobtam egy tesztet 700 000 rekordra az alapján amit írtál.
Sajnos most nincs a gépen 2000, csak 2008 ,de nagy különbség nem lehet (semmi extrát nem csinálsz), ha kell kipróbálom azon is.
create table teszt (
azon int identity(1,1),
osszeg float,
esedekes datetime) , index nincs.
Ugye alapból hibás a lekérdezésed (szintaktika...), ezt javítottam, így a core centrino laptopon kijön 20-25 másodperc alatt.
Ezt nem tartom lassúnak, a szerver közben 150 mb ramot eszik.
A te esetedben a getdate()-et ki venném a lekérdezésből változóba, mivel a help is írja nem determinisztikus, épp ezért nem írnák függvényt se mert az mssql-ben (2000-ben biztosan) minden udf nem determinisztikus, ezért soronként kiértékeli.

Sajnos, nem adtál tábla szerkezettet, indexet így többet nemm tudok egyelőre tenni.
Több hibátok is lehet, ha auto grow a db és kicsi a növekedés lehet töredezett a fájl, lehet széles a táblátok, és még vagy egy tucat okot feltudnák sorolni ami miatt lassú a lekérdezés.
Mondjuk azt nem írtad hogy mennyi idő az a lassú (csak az az idő ami alatt az adat megjön).
ezenkívül sokat dobna a probléma megoldásán egy query plan + client staistic is.

ui.: magán vélemny 700 000 forintban nem hangzik rosszul rekordnak viszont kevés, egy mezei p4 1gb rammal simán kezeli, még csak lassúnak se mondhato ha a software jól van szervezve ha nem akkor tagyetek alá nagyobb vasat az elodázza a probléma megoldását :D

( almos | 2009. 11. 23., h – 19:50 )

Elképzelhető, hogy a busniess viewer a ludas. Az egyik fél óra alatt futott le, a másiknak több óra kellett.

Most készül a harmadik verzió (jött egy új igény is idő közben), ezt egyelőre query analyserrel tesztelem, ennek a válaszideje pár mp. A business viewerben is ez visszaesik, akkor megvan a hunyó, ha meg nem esik vissza, akkor megoldottam. :)

(a memória biztosan nem kevés, elég komoly HW van alatta)

Nem mindenki banknak csinál rendszert.
Azt gondolom, hogy nagyon sok kereskedelmi világcégnek (pl. Metro) meg lehet csinálni a rendszerét tranzakció kezelés nélkül is, a cég felépítése végett.
Kevés olyan hely van a kereskedelemben a pénzügyi területeken kívül, ahol egy helyen kell meglennie mindennek.

A tranzakció kezelésnek semmi köze a pénzügyhöz, lehet hogy félreértjük egymást.

A tranzakció kezelés arról szól, hogy ha több adatot rögzítesz egyben és az adatrögzítésnél feltétel, hogy vagy minden rögzüljön vagy semmi, akkor azt csak tranzakció kezeléssel tudod megoldani.

És azt is, mikor használjuk a miatt-ot és mikor a végett-et?
Ez még az a terület, ami kihozza belőlem a grammatikanácit...

A kérdésre visszatérve: a mysql gpl-es verziója, amikor utoljára néztem, nem tudott tranzakciót és lassabb volt az általam használatos dolgokra, mint a postgres. Elég nehéz lenne indokolni, hogy miért válasszam azt.

Nézőpont kérdése. Nem fejtettem ki, mert felesleges, hiszen itt kevés ember üzemeltet kereskedelmi céget zárva tartási idővel.

Elmagyarázom. Képzelj el egy XXXXX szintű nagykereskedést. Itt felesleges tranzakciót kezelni, mert csak azt az árút tudja lehúzni a vevő ami ott van. Kasszánként egy user, egy adatbázis. Este elküldi a helyi szervernek az adatokat, hogy mit adott el. (Így a sztornóval már nem kell foglalkoznia).

A helyi szerver összesít és lejelent a központnak, mint ahogy a többi áruház is teszi. Továbba berendeli a fogyást akár automata módon is.

A központban a fogyások alapján összeállítanak árlistákat, mondjuk minden terméknek van 3 féle ára, attól _IS_ függően, hogy melyik áruházat mennyi konkurens és milyen áron veszi körül.

Hetente egyszer a főszerverről letöltik az új árakat, amiket a helyi szerver kikínál a kasszáknak.

Ebben a folyamatban, hol kell már commit/rollback? Melyik eladás függ a másik eladás/beszerzástől realtime? Nem 1 db pénzegység van többszintű egyidejű hozzáféréssel! Bankokat szopatták anno azzal amikor egy tranzakciókezelés még súlyos 10 perceket vett igénybe, hogy kivették cash a pénztárba a pénzt+az ATM-ből is. Íg duplázva meg a betett összeget.

Ettől függetlenül biztosan hülyeségeket írok. :) Megszoktam.

ez a bankos dolog ez szerintem urban legend, de van ra vmi link? :)

mondjuk ott kell tranzakcoi, amikor a rendszer futtat vmi update-sorozatot (sosem mondtam, hogy ertek a kereskedelemhez, meg aruhazam sincs, ellenben ha tobb dolgot akarsz egymastol fuggoen updatelni, oda nem art tranzakcio). szerintem ilyen elofordulhat egy aruhazon belul [nem aruhaz-rendszer!]

Megjegyzem mind a két példa bank+kereskedelem hazai volt.

Tesco (szerintem ma is így működik)
Budapest Bank 1999-ben, még 10 perced volt a fenti műveletre, de ez akkor még banktitok volt, így is sokszor beszopták és nem tudták behajtania pénzt.
Anyu kint állt az ATM-nél apu meg bent a pénztárnál. Hiába voltak kamerás felvételek a szándékosságot nem tudták bizonyítani, így csak polgári peres úton tudták volna behajtani a pénzt. Sohasem mentek utána, hanem az ilyen userek blacklistre kerültek és a bankok egymás közt cseréltek listát.

Azért fontos banknál a tranzakció kezelés mert a kifizetés függ az egyenlegtől és fontos, hogy legyen sorban állás két független sql műveletnél, ugyanis ma technikailag megoldható lenne, hogy egy egyszerű script mondjuk másodpercenkét 5x utaljon webbankból, fedezet nélkül. Ezért ezt biztonságosan _CSAK_ tranzakció kezeléssel lehet megoldani.

Nem vitatom, hogy máshol is jól jön a tranzakció kezelés, de azt igen, hogy mindenhová kell a gazdaságban. Sőt!

Felhasználok szétválogatása, pedig egy user_id mezővel megvalósítható a táblában. Nálam pl minden tábla kötelező mezője:

id
created_user
created_date
modify_user
modify_date
company_id (több céget kezel a rendszer)

Na így néz ki egy "üres" tábla nálam.

ps.: bankos trükknél nincstelen strómanokat alkalmaztak szervezetten, a háttérben feltehetőleg ugyanazokkal az emberekkel

Szerintem te a tranzakció szó egy másik jelentéséről vitázol. Tranzakció alatt sokmindent értünk, a józsefvárosi piacon csomagtartóból eladott uzin át a egy adott egységnyi adat bevitelén/módosításán keresztül egy pénztári tranzakcióig.

Én két banknál dolgozom jelenleg számlásan és a pénztárapu-atmanyu tranzakciót már 94-ben nem tudták volna elkövetni attól függetlenül, hogy a DB2 tranzakciókezelte-e az áramló adatokat avagy sem.

Én azt gondolom, hogy függetlenül attól, hogy pénzügyi téren adatbázisolunk - (ezt nem is értem, miért tünteted ki) mindig tranzakciókezelést használunk az adatbázisműveleteknél, ha:

- az adatvagyon értékét már értjük
- több műveletet végzünk egymás után zsinórban a logikai művelet fizikai kivitelezése közben
- több táblát érint a logikai müvelet fizikai kivitelezése
- ....

Ahha... bejön egy lista egy fiókból, hogy ezt meg ezt adtuk el, mondjuk van benne 300 féle tétel. Elkezded betolni a törzsbe, mikor áramszünet van vagy szétszakad a hálózat vagy bármi történik. Az adatbázisod meg széthullott darabokra.

A tranzakció nem csak banki tranzakciókra kell, hanem arra, hogy az adatbázisod látható oldala mindig konzisztens maradjon.

Jogos. erre nem gondoltam. Látszik, hogy én csak helyi hálón dolgozom :)

ps.: Megjegyzem, hogyha minden rekordnak van egyedi azonosítója, akkor a duplázás is elkerülhető, tranzakció kezelés nélkül is.

ps2: Legtöbbször ilyen adatcserére nem közvetlen sql-t használnak, hanem plaintext átküldik a közvetítő fájlt, ahol jelölve van a fájl vége valamilyen módon, hogy tudja a fogadó fél, hogy megérkezett minden adat nem csak megszakadt a feltöltés. Így működik pl. egy internet bankos csoportos átutalási megbízás feltöltése is.

( Little Dragon | 2009. 11. 23., h – 20:34 )

Eloszor is a tempdb-nek hozz letre annyi adat file-t, amennyi processorod van, de max. 8-at.
Ez segit a tempdb-hasznalatabol adodo IO problemakon.
(http://support.microsoft.com/kb/328551
Bekezdes: Increase the number of tempdb data files with equal sizing
A trace flagek-et ne piszkald
)

A GETDATE() fuggven ismetelt hivasai helyett, tarold azt is valtozoban, ahogyan a tobbivel is teszed.
PL:
DECALRE @MAINAP DATETIME;
SET @MAINAP = GETDATE();

Set @RefDatumPlusz30 = DATEADD(dd, 30, @MAINAP)

majd
.
.
.
SUM(CASE Esedekes <= @MAINAP THEN Összeg ELSE 0) AS Lejárt,
.
.
.

ezzel a GETDATE() fuggvenyhivasok szamat lecsokkentheted 1-re, tobb tizezer helyett.

Ha naponta tobbszor futtatod, akkor tedd bele egy tarolt eljarasba. Mar, ha kivitelezheto (execution plan tarolva, nem kell ujraforditani minden futas elott,...).

A tabla particionalasan is erdemes elgondolkozni, bar 2000-nel kacifantos, 2005 eseten eleg egyszeru, 2008-nal meg mar felesleges.

Hat, remelem segit. Majd tudasd, hogy mi az eredmeny.

A parhuzamositason meg ugy segitenek, hogy:

SELECT
BP_ID,
SUM(Összeg),
SUM(CASE Esedekes <= GETDATE() THEN Összeg ELSE 0) AS Lejárt,
SUM(CASE Esedekes > GETDATE() THEN Összeg ELSE 0) AS Esedékes,
SUM(CASE Esedekes >= @RefDatumMin30 AND Esedekes < GETDATE() THEN Összeg ELSE 0) AS Lejárt30,
.
.
.
FROM NyitottTételek
GROUP BY
BP_ID

helyett ezt:

SELECT
DISTINCT BP_ID
, (SELECT SUM(Összeg) FROM NyitottTételek WHERE BP_ID = NYT.BP_ID AND Esedekes <= @MAINAP ) AS Lejárt
, (SELECT SUM(Összeg) FROM NyitottTételek WHERE BP_ID = NYT.BP_ID AND Esedekes > @MAINAP ) AS Esedékes
, (SELECT SUM(Összeg) FROM NyitottTételek
WHERE
Esedekes < @MAINAP
AND
Esedekes >= @RefDatumMin30
AND
BP_ID = NYT.BP_ID
) AS Lejárt30
.
.
.
FROM NyitottTételek AS NYT
GROUP BY
BP_ID

Ennek szerintem gyorsitania kellene, mert kevesebb a fuggvenyhivasok szama a SELECT-ben es nincs egy CASE sem, valamint tobbszalu lehet a lekerdezes (ezt azert ellenorizd, hogy segitett-e).

( hrgy84 | 2009. 11. 24., k – 14:03 )

Nem olvastam vegig, de nezted mar az execution plan-t? Abba nem latsz okossagokat?
Amugy en ezt kicsomagolnam fuggvenybe.
-- 


()=() Ki oda vagyik,
('Y') hol szall a galamb
C . C elszalasztja a
()_() kincset itt alant.

( almos | 2009. 11. 25., sze – 22:40 )

MEGVAN!!!
A Business Viewer a ludas. A query önmagában jó lett volna (persze azért sokat finmítottam rajta), DE a BV nem képes elfogadható válaszidővel feldolgozni több lekérdezést. Itt az volt a gond, hogy egy második SQL kérte le az ügyfél nevét. A két lekérdezést egybegyúrva a BV-nek csak egy SQL-t kellett végrehajtani. Most a riport pár mp alatt lefut.

Mindenkinek kösz a tippeket!