[SQL] Hoe kan je stateless blokken data opvragen [NooB]

[marque1968]

Ik voel me best een noob om deze vraag te stellen, maar stateless is redelijk nieuw voor me

Ik heb een tabel met een paar honderdduizend tot milioenen records aan data. Ik wil deze niet in zijn geheel inlezen om een paar regels te tonen, dus ik heb de textgrid 12 regels groot gemaakt, met een eigen scrollbar ernaast. Deze heeft de max ingesteld op het aantal records in de tabel. Verder heb ik muis en toetsenbord opgevangen, zodat scrollen buiten deze regels, ervoor zorgt dat de scrollbalk position verplaatst wordt en het volgende blok data getoond wordt. Met mock-up data werkt dit perfect.

Nu wil ik uit de SQL alleen deze 12 regels aan data uit de DB overzenden naar de GUI (en later naar web). En als ik verder naar beneden scroll (of op web de knop [> Volgende] indruk) de volgende 12.

Het liefst zou ik zeggen:
select * from myTable
where <condition>
order by OrderDate DESC
limit from 456123 to 456134

Maar dat is een utopie.

Bij iedere stap die je zet, in de logic alles uit de database halen en dan alleen de records 456123 t/m 456134 doorsturen naar de gui of web is ook enorm vertragend en belastend, dus dat lijkt me niet de oplossing.

Aangezien ik vast niet de enige ben die stateless data wil tonen, en ook niet iedere keer een hele tabel (of database) over de lijn wil sturen, geloof ik vast dat hier een aantal slimme koppen zitten die een slimme oplossing voor weten.

De vraag is algemeen over SQL maar ter info:
- Programmeren in Lazarus / FPC
- Gebruik nu een MariaDB tabel
- Wil sowieso ook met Postgresql en MS Sql gaan communiceren
- Momenteel alleen bezig voor een proof-of-concept.

[Ed Vertijsment]

Begrijp ik goed dat je een “infinite scroller” maakt?

[Irwon]

Die limit optie die je als liefste wilt gebruiken kan je ook op de database doen? https://mariadb.com/kb/en/limit/
Hierdoor vraag je niet de complete tabel uit de database maar een kleine set.

Mocht dat niet performant zijn wellicht eens hier naar kunnen kijken https://mariadb.com/kb/en/pagination-optimization/

De term waar je denk ik naar opzoek bent heet pagination.

[gekkie]

Zo werkt de "limit" clause sowieso niet, met een offset zou het wel kunnen.
Daarnaast zul je altijd ergens "state" hebben, ergens moet bij gehouden worden wat al getoond is en wat niet.

Als je niet alle data uit de DB wilt overhalen voor een "infinte scroller" zou je wellicht (in ieder geval bij postgres) voor een serverside cursor kunnen kiezen en daar clientside over itereren waarbij steeds een deel van de resultset wordt overgehaald maar de query wel in een keer plaatsvindt aan het begin.

(select je überhaupt alleen de kolommen die je werkelijk nodig hebt ?)

[ Voor 3% gewijzigd door gekkie op 25-04-2021 13:28 ]

[juhet]

gaat iemand echt "honderdduizend tot milioenen records aan data" doorscrollen per 12? Miscchien kun je de totale set ook nog beperken tot (bijvoorbeeld) 120 of 1200 (10 of 100 pagina's)

[Emgeebee]

juhet schreef op zondag 25 april 2021 @ 13:52:
gaat iemand echt "honderdduizend tot milioenen records aan data" doorscrollen per 12? Miscchien kun je de totale set ook nog beperken tot (bijvoorbeeld) 120 of 1200 (10 of 100 pagina's)

Klinkt inderdaad als een UX vraagstuk: welke records wil ik hoe tonen? Om daarna te gaan bepalen hoe je dit technisch op kan lossen.

[Verwijderd]

In je voorbeeld zal het dan ongeveer zo eruit zien:

code:

where <condition>
order by OrderDate DESC
limit 456123,12

[pedorus]

Verwijderd schreef op zondag 25 april 2021 @ 16:51:
In je voorbeeld zal het dan ongeveer zo eruit zien:

code:

where <condition>
order by OrderDate DESC
limit 456123,12

Het probleem daarmee is natuurlijk dat dit alleen goed gaat als die 456122 records onveranderd zijn (geen insert/delete), naast dat het traag is omdat er eerst 456122 records afgegaan moet worden. Een oplossing daarvoor is dat je een sortering hebt die uniek is, daarop sorteert, en items opvraagt die later zijn dan het laatste item dat je hebt (de "left off" in de mariadb-uitleg-link). En naar willekeurige pagina's gaan is vanuit menselijk opzicht ook niet zo logisch. Ik ken ook vrijwel geen moderne site die het toestaat om naar pagina 38010 te gaan omdat dit enorm duur en niet heel nuttig is..

Dus heb je heel veel records, dan kun je meestal wel naar beneden scrollen en dan wordt er on-the-fly aangevuld, maar zie je niet het volledige bereik waarin je willekeurig kan scrollen. En intern geeft ieder api request dan ook iets om een volgende pagina op te vragen als die er is.

[DataGhost]

pedorus schreef op maandag 26 april 2021 @ 01:01:
[...]

Het probleem daarmee is natuurlijk dat dit alleen goed gaat als die 456122 records onveranderd zijn (geen insert/delete), naast dat het traag is omdat er eerst 456122 records afgegaan moet worden.

Ik denk dat TS het nog niet eens geprobeerd heeft, in een normaal datamodel is dit helemaal niet langzaam. Wat je zegt is niet zonder meer waar, alleen voor de meest naïeve tabellen misschien.

Een oplossing daarvoor is dat je een sortering hebt die uniek is, daarop sorteert, en items opvraagt die later zijn dan het laatste item dat je hebt (de "left off" in de mariadb-uitleg-link).

Ja! Dat klinkt goed! Misschien kunnen we dat een "index" noemen

Vanuit UX-oogpunt is het misschien inderdaad niet logisch om in een scroller op die plek terecht te komen, maar zeker niet ondenkbaar (jump to X, en dan naar boven of beneden scrollen). Maar technisch hoeft dit helemaal geen issue te zijn. Als RTTs nog een noemenswaardige impact hebben, en bijv. het uitvoeren van de query zelf, kan er iets gedaan worden met een groter limit-window en lokale caching op de client. Ik denk dat je dan alle cases wel hebt. Maar op een tabel zonder indexen gaat dit in ieder geval niet performen. Met de juiste indexen zou het bloedsnel moeten zijn.

Edit: vaak is een goed startpunt het EXPLAINen van de queries die uitgevoerd moeten worden om te kijken of de boel op de juiste manier uitgevoerd wordt en waar eventueel winst te behalen valt. Dat is dan in de vorm van betere indexen plaatsen, beter nadenken over het datamodel en aanpassen van de queries adhv logischere criteria.

[ Voor 10% gewijzigd door DataGhost op 26-04-2021 03:24 ]

[marque1968]

Irwon schreef op zondag 25 april 2021 @ 13:24:
De term waar je denk ik naar opzoek bent heet pagination.

Inderdaad. Met die term vind ik de voorbeelden die ik zocht, en jouw voorbeeld klopte dus ook. Die zag ik niet toen ik eerder zocht. Tnx!

[marque1968]

@juhet : ik ben nu eerst met een proof of concept bezig, die een andere programmeertaal met een andere db en een andere benaderingswijze moet bewijzen of ontkrachten. Het huidige systeem is met data aware velden en grids waar je op zoekterm inkomt en verder kan scrollen. Dit moet ik nabouwen om de poc te kunnen verdedigen. Een andere werkwijze komt pas als je het echt op gaat pakken.

@DataGhost : Dank voor je toegevoegde uitleg.

[pedorus]

DataGhost schreef op maandag 26 april 2021 @ 03:16:
[...]

Ik denk dat TS het nog niet eens geprobeerd heeft, in een normaal datamodel is dit helemaal niet langzaam. Wat je zegt is niet zonder meer waar, alleen voor de meest naïeve tabellen misschien.

Heb je het zelf wel geprobeerd? Ik heb het net voor de grap nog even getest, laatste stable MariaBD(10.5.9), tabel met ~10M rijen onder veranderingen. De goede query doet er 0.000s over, dit voorstel ~0.410s (redelijk consistent)..

Misschien als je geen veranderingen hebt dat een 2e opvraging snel gaat vanwege caching. Of is er een magische non-naïeve setting die even om moet?

Dit is echt typisch iets dat het misschien goed doet op in een kleine setting zonder veranderingen aan de data en dan op productie gaat de boel "opeens" onderuit. Zie nogmaals de waarschuwing op https://mariadb.com/kb/en/pagination-optimization/

Ja! Dat klinkt goed! Misschien kunnen we dat een "index" noemen

Dat moet ook op een index ja, anders werkt het alsnog heel traag. Maar een index is meestal een BTREE, of het nu Mysql, Mariadb, Prostgres of SQL Server betreft maakt niet zo veel uit. Een normale b+/-tree kan een query "where a > x order by a limit 10" prima aan. Een query van het type " order by a limit 500000,10" is beroerd, want in zo'n tree zie je niet even snel waar element nummer 500000 zit.

Maar technisch hoeft dit helemaal geen issue te zijn.

Met een array als index kan het snel zijn ja, maar de meeste databases bieden dat niet eens aan (omdat inserts/deletes dan heel erg duur zijn).

[DataGhost]

pedorus schreef op maandag 26 april 2021 @ 22:28:
[...]

Heb je het zelf wel geprobeerd? Ik heb het net voor de grap nog even getest, laatste stable MariaBD(10.5.9), tabel met ~10M rijen onder veranderingen. De goede query doet er 0.000s over, dit voorstel ~0.410s (redelijk consistent)..

Nee Ik denk dat ik ook even iets anders in m'n hoofd had zitten, waarschijnlijk dat er een volledige table scan gedaan moest worden voor de condities. Voor de pagination zal het inderdaad kloppen dat de LIMIT/OFFSET daar een bottleneck gaat zijn. Toch is dit soms niet te voorkomen. Bijv. topics op Tweakers staan je ook toe om naar pagina X te gaan (door de URL aan te passen) dus zolang je IDs niet doorlopen zal je toch zo nu en dan met een LIMIT/OFFSET aan de gang moeten, of een hulptabel/view met een mapping pagina/post, wat overigens alleen werkt bij een fixed paginagrootte dus niet arbitrair zoals op T.net. Volgens mij gaan topics daarom ook zo nu en dan dicht voor een nieuw deel Maar als er geen use case is voor jumps hoeft dat niet en kan je idd filteren op < id of > id.