MSSQL proxy
2022-09-20T13:54:58+02:00
2022-09-23T09:52:27+02:00
2022-09-26T06:27:26+02:00
needback
Sziasztok!

Készítettem egy (Delphi) programot, ami SQL adatbázisba ír logot, és egy másik (Delphi) programmal onnan kéri le azokat. Az íráshoz természetesen megkövetelt a közvetlen MSSQL kapcsolat, de olvasáshoz, mivel a kliens bárhol lehet, bármilyen gépről kapcsolódhat, nem kötelezhetem, hogy az SQL Native Client-et felrakja, így valamint biztonsági okból a random helyről kapcsolódást akár tilthatják is. Mivel a központi szoftverrel állandó kapcsolatban áll, arra gondoltam, hogy a lekérdezéseket azon keresztül végzem. Sok kísérletezés után kiderült, hogy a lekért adatok a kliens oldalon a memóriába kerülnek, és hamar elfogy a memória (32 bites progi, nem portolható 64 bitesre), míg, ha sikerül közvetlenül kapcsolódni, akkor az ADO megoldja, hogy csak az éppen aktuális rekord kerüljön a memóriába a feldolgozás idejére.

Próbálkoztam fájlba írással, de onnan az ADO RecordSet a teljes tartalmat a memóriába tölti, és ugyanott vagyok. Kísérleteztem a darabolással, de mivel a lapozás eléggé favágó módon valósítható csak meg MSSQL-ben (OFFSET, FETCH NEXT) minél "távolabbi" az offset, annál lassabb egy adott csomag lekérdezése.

Kerestem olyan megoldást, ami az ADO connection-t tudná proxizni (egy másik hozzáféréssel rendelkező gépre csatlakozom, és egyszerűen átirányítja a kérést az SQL szerver felé), de nem találtam értelmes leírást. Lehet, hogy csak nem a megfelelő kulcsszavakkal próbálkozom.

Olyan ötlet is eszembe jutott, hogy lekérdezem az adatokat, létrehozok egy local db fájlt, amit aztán a kliens meg tudna nyitni, és csak annyit töltene be, amennyi a kurzor alatti rekordban van. De erre se találtam stabil megoldást, ráadásul eléggé időigényesnek tűnik.

Valakinek valami ötlet?

A segítséget előre is köszönöm:
Needback
Mutasd a teljes hozzászólást!
Kísérleteztem a darabolással, de mivel a lapozás eléggé favágó módon valósítható csak meg MSSQL-ben (OFFSET, FETCH NEXT) minél "távolabbi" az offset, annál lassabb egy adott csomag lekérdezése.

Az nem meglepő, hogy lassú lesz, minden db kezelőben lassul a lekérdezés, ahogy haladsz előre az offset-ben.
Ha TOP és ORDER BY párost használsz helyette, illetve a WHERE-ben mindig olyan adatot kérdezel le, amit még nem kaptál vissza, akkor az gyors lesz (mert levágod vele azt az adathalmazt, amit már korábban visszakaptál).

Egy nagyon egyszerű példával:

SELECT TOP 10 * FROM tábla WHERE id > @utoljára_visszakapott_id ORDER BY id ASC
Ahol az @utoljára_visszakapott_id-t mindig visszaadod a következő lekérdezésnek.
Mutasd a teljes hozzászólást!

  • Szia,

    ha jól értem a leírásodból, azzala  részével van gondod, ami a logot "megmutatja". Ezt hogy csinálod? Lekérdezed mind az összes létező log elemet és egy dbgridben nézegethetik?

    Amit én javaslok, ahogy én csinálnám:
    1; lenne egy szerver alkalmazás ami REST/HTTP/TCPIP/UDP akárhogy fogadja a kliensek kéréseit (írás, olvasás), ez ott lenne az SQL szerver közelében.
    2; Olvasásnál mindenképp korlátosan adnék vissza a szerverből adatokat (egyszerre max egy képernyőnyit 50/100 sornál nem többet), igen meg kell oldani a lapozást, de hát egyszerre 1000000000000000000000 sort az NSA szerverei sem tudnak visszaadni...
    3; a kliensek a megfelő protokollon keresztül beszélgetnek a szerverrel, a kliens alkalmazáson kívül mást nem kell telepíteni, de akár lehet telepítés nélküli verzió is...
    Mutasd a teljes hozzászólást!
  • MrR2016! A folyamat a következő:

    Közvetlen kapcsolat esetén a dolog egyszerű: ADODB objektumon keresztül lekérdezem amit le kell:

    Connection := CreateOleObject('ADODB.Connection'); Connection.Open(CONNECTION_STRING); Command := CreateOleObject('ADODB.Command'); Command.ActiveConnection := Connection; Command.CommandType := adCmdText; Command.CommandText := SELECT_QUERY; RecordSet := Command.Execute;
    Ezután végigmegyek a találatokon, és átkonvertálom az eredményt a megjelenítőben használt formátumra (nem DBgrid):

    while not RecordSet.Eof do begin // konvertálás RecordSet.MoveNext; end;
    A nem közvetlen kapcsolódás esetén próbálkozásaim legutolsó változatában a lekért result-ot lementem egy fájlba (RecordSet.Save), ezt a fájl átküldöm egy RPC request-en keresztül, majd a kliens oldalon a fájlt lementve, a RecordSet.Open paranccsal betöltöm. És ez a betöltés az, ami a teljes fájlt betölti a memóriába, ami elveszi a területet a konvertálásnál létrejövő objektumok elől.

    A memória közvetlen kapcsolódásnál is el tud fogyni (szabad memória figyeléssel ellenőrzöm, hogy ne kritikus mértékben fogyjon el a memória, maradjon hely a grafikus megjelenítésnek is), de megvan a lehetősége, hogy a lehető legtöbb adatot betöltsem, mielőtt elfogyna a rendelkezésre álló memória, így kimaxolva a megjelenített sorok számát. Egyébként több tesztet is lefuttatva már fixen 1 millió rekordra redukáltam a maximális lekérhető sorok számát, mert ez a határszám a kliensnél a betölthető rekordokra (+-10%).

    Próbálkoztam az Open függvény (Open Method (ADO Recordset) - ActiveX Data Objects (ADO)) különböző paramétereinek változtatásával, hátha valamelyik nem tölti be az egészet a memóriába, de nem találtam jó kombinációt.

    ----

    Hack! ID alapján történő darabolás is eszembe jutott. Minél kisebb a csoport mérete, annál kevesebbet eszik egy csoport betöltése és feldolgozása, elllenben annál több időt vesz igénybe. Értelem szerűen a csoport méretének növekedése az idő csökkenését, de a foglalt memóriafogyasztás növekedését eredményezi. Ez minden szempontból elkerülendő, mert a hatékonysági görbe (idő / maximálisan betölthető adat) sajnos púpos, és a két véglet a legoptimálisabb (vagy a leggyorsabb, de legkevesebb adat, vagy legtöbb adat, de nagyon lassú).

    Fontos kiemelni, hogy a szerver és a kliens között RPC kapcsolat van, így minden külön lekérdezés egy komplett HTTP kapcsolat felépítését, a kérés XML-lé alakítását foglalja magában, ami szintén időigényes.

    Ezért gondoltam arra, hogy valami köztes kiszolgálási módot keresek, amit be tudok iktatni a kliens és az SQL közé, mint átjátszó szerver, amivel a közvetlen kapcsolathoz hasonlóan nem a kliens memóriájában tárolná a result-et, hanem az ADO-hoz hasonlóan (vagy az ADO-t használva) rekordonként kapnám meg az eredményeket feldolgozásra, és csak az az egy rekord lenne egy időben a memóriában az egész result-ból.
    Mutasd a teljes hozzászólást!
  • Szia,

    ha az sql szerver oldalon nem oldható meg a konvertálás, és a megjelenítendő adatok kiszámolása, én a log írása során, egy új táblába beírnám a "konvertálás" eredményét is, hogy ne kelljen minden egyes alakalommal újra, és újra kiszámolni minden kliensen. Majd a megjelenítést végző kliensek, ebből kérdeznének le...

    Amennyiben a konvertálás után valamiféle statisztika készítés történik, megpróbálnám azt a lekérdezés részévé tenni, hogy minél kevesebb adatot kelljen visszaadni.

    Nem ismerve a környezetet, de a leírás alapján úgy tippelem a  log írását végző program nem portolható 64 bitre (esetleg valamilyen spéci 32 bites driver miatt?), de a megjelenítést végző, ami csak lekérdez adatbázisból, és megjelenít, az miért nem?

    Én amennyiben megoldható, kerülném a közvetlen adatbázis kapcsolatot a kliens programok esetén, koncepció kipróbálása esetén jó, de éles környezetben nem túl szerencsés.
    Mutasd a teljes hozzászólást!
  • Szia,

    A konvertálás tulajdonképpen az OleVariant értékeket pakolja be egy LogRecord típusba, ahol az egyes oszlopokhoz külön változó van társítva, és ez az objektum merül hozzátársításra egy VirtualStringTree (in-memory ListView nagy adatok megjelenítéséhez) elemhez. Tehát a konvertálás valójában OleVariant to primitive type.

    A szerver és a kliens is 32 bites. Kisebb részben a vásárolt 3rd party modulok, nagyobb részben a Delphi 7-es függés okozza, hogy nem lehet portolni 1 az 1-ben 64 bitesre. Folyamatban van a modulonkénti portolás, de az nagyon sok idő, és egyedül vagyok rá, így ezt a problémát 32 biten kell most orvosolnom.

    A közvetlen kapcsolat biztonsági kockázata hozta elő ezt a dolgot. Igazából reménykedtem, hogy meg tudom oldani sima proxizással, hogy ne kelljen időt várni a "leszedem-átalakítom-átküldöm-visszaalakítom" folyamattal... ami folyamat ráadásul eszi a memóriát kliens oldalon.

    Bíztam benne, hogy van egy olyan program, vagy library, amit fel tudok rakni a szerver mellé, és azon keresztül (felügyelt végrehajtási jogkörrel) el tudom érni az SQL szervert.
    Mutasd a teljes hozzászólást!
  • A helyzet az, hogy végül úgy oldottam meg, hogy szerver oldalon elindítottam a lekérdezést (Hack által javasolt darabolást használva), a darabokat lementettem külön fájlba, majd a fájlokat egyesével lekértem a klienssel, végül kliens oldalon betöltöttem a RecordSet-be, és a kisebb RecordSet-eken futtattam le a konvertálást. Így egyszerre nem történt nagy méretű foglalás a memóriában, és sikerült az optimális csomagméretet (esetemben 100k) megtalálni, amivel az időtöbblet nem megy a lekérdezés rovására.

    A teljesség igénye nélkül az alábbi kódokat használtam ehhez:
    Lekérdezés/lementés:

    const SELECT_QUERY_FIRST = 'SELECT TOP(%d) * FROM Log %s ORDER BY id desc'; SELECT_QUERY_NEXT = 'SELECT TOP(%d) * FROM Log WHERE id < %d %s ORDER BY id desc'; begin RecordSet := CreateOleObject('ADODB.RecordSet'); RecordSet.Open(Format(SELECT_QUERY_FIRST, [BLOCK_SIZE, 'WHERE ' + Filter]), ConnectionString); while not RecordSet.Eof do begin FileName := TTempFiles.GetTempFileSpec(TTempFiles.GetTempFilePath, 'sql'); DeleteFile(FileName); // windows' GetTempFileNameW automatically create // the temp file to avoid reuse in other thread, // but RecordSet require non-exists file name RecordSet.Save(FileName, adPersistADTG); RecordSet.Close; // store FileName to transport to other side RecordSet.Open(Format(SELECT_QUERY_NEXT, [BLOCK_SIZE, LastrecordID, 'and ' + Filter]), ConnectionString); end; end;
    Beolvasáshoz pedig az alábbit:

    RecordSet := CreateOleObject('ADODB.RecordSet'); for i := 0 to LogFiles.Count - 1 do begin RecordSet.Open(LogFiles[i]); ExtractLogRecordFromRecordSet(RecordSet); RecordSet.Close; DeleteFile(LogFiles[i]); // remove temporary file to avoid running out of space end;
    Sajnos ez a megoldás köszönőviszonyban sincs az eredeti kéréssel, így nem tökéletes, de mivel Hack javaslata segített optimalizálni a folyamatot, így az ő válaszát fogadom el.
    Mutasd a teljes hozzászólást!
abcd