[MySQL]Datamatrix opslaan in database

[Paultje3181]

Voor een project dien ik in een database voor ieder doosje van een product een GS1-Datamatrix Wikipedia: Datamatrix op te slaan. De vraag is nu hoe dit het beste te doen is, aangezien deze data minstens 5 jaar na vervaldatum (gemiddeld dus een jaar op bewaard moet worden.

Een datamatrix is ongeveer 2kb aan data. Maar wat voor type veld kan ik hier dan het beste voor gebruiken? VARCHAR, TEXT of BLOB zijn volgens mij de opties die ik heb, maar je kan ook het plaatje als base64 opslaan.

Wat zou hier de beste keuze voor zijn? Wie heeft er ervaring met dergelijke hoeveelheden data in een tabel en kan de voor- en nadelen aangeven hiervan?

De data zal weggeschreven worden via een php-frontend.

[rappie_nl]

Ieder doosje heeft een (uniek) nummer?

Heeft dat nummer altijd even veel cijfers?
Ja: veldtype CHAR gebruiken
Nee: VARCHAR gebruiken

[Henkje.doc]

Wat hierboven is aangegeven. Denk ook aan wat je met de data wilt gaan doen. Met een plaatje kun je andere dingen in een database dan met een varchar veld.

[Paultje3181]

De lengte kan enigszins variabel zijn, maar altijd groter dan 255. CHAR valt dus af. Blijft VARCHAR of TEXT over. Blob en text = Binary VS plaintext. De datamatrix bevat plaintext, dus blob valt dan ook af, lijkt me...

Hetgeen er met de database gedaan wordt is het 1 op 1 vergelijken met een ccd-scanner en de mutaties opslaan en loggen. Toekomstbestendigheid is hierbij essentieel. Maar ik vind het lastig om te vergelijken wat hierbij nu het handigst is.

[ Voor 35% gewijzigd door Paultje3181 op 04-05-2018 07:33 ]

[GarBaGe]

Wil je kunnen zoeken in je datamatrices? Bv met like?
Dan varchar.

Ik ben geen voorstander van grote varchars, omdat varchars in de database pages worden geschreven.
Een typische database page is bijvoorbeeld 4kB of 8kB.
Als alle datavelden in 1 database record meer ruimte innemen dan de page grootte, kan het record simpelweg niet worden bewaard in de database.

Als het bij jou gaat om 1 varchar van 2kB in 1 database record, dan loop je hiervoor geen risico.
Als je in een database record meerdere van deze grote varchars (datamatrices) wilt opslaan, dan kan het wel fout gaan.

Zie ook: https://dev.mysql.com/doc.../innodb-restrictions.html

[ Voor 81% gewijzigd door GarBaGe op 04-05-2018 08:24 ]

[Paultje3181]

En waarom dan geen text? Als ik naar https://stackoverflow.com...sql-large-varchar-vs-text kijk lijkt text de betere optie.

[CAPSLOCK2000]

Paultje3181 schreef op vrijdag 4 mei 2018 @ 07:25:
De lengte kan enigszins variabel zijn, maar altijd groter dan 255. CHAR valt dus af. Blijft VARCHAR of TEXT over. Blob en text = Binary VS plaintext. De datamatrix bevat plaintext, dus blob valt dan ook af, lijkt me...

Hoe is je data opgeslagen en wat voor bewerkingen wil je er mee doen? Je stelt dat een datamatrix plaintext bevat, maar volgens mij is een datatmatrix een plaatje. Een plaatje is typisch een bitveld. Mogelijk heb jij een representatie van de data in plaintext, maar dat is niet vanzelfsprekend.
Als geen bewerkingen wil doen op de data in die matrix dan zou ik voor een blob gaan.
Als je wel bewerkingen wil doen dan moet je je afvragen of je die hele matrix wel in één veld wil stoppen, of dat je de data beter over meerdere velden kan verdelen, zoals de bedoeling is in een database. Anders ga je dadelijk een database in een database bouwen.
Trouwens, om hoeveel miljard doosjes gaat het en hoe snel moeten ze verwerkt worden? Als het slechts om een paar miljoen van die doosjes gaat zou ik er niet te lang over nadenken.

[Paultje3181]

Een grove schatting levert me dat het om +- een miljoen per jaar gaat. De datamatrix zal via een formulier gescand worden (met een CCD-scanner) en op die manier in de database komen. Er zullen per doosje hooguit 5 wijzigingen komen. (binnenboeken, interne leveringen, levering aan klant). Daarnaast zullen er sporadisch opvragingen gedaan worden voor een gedeelte van de datamatrix (er is o.a. serienummer, chargenummer, vervaldatum e.d. opgeslagen). Denk hierbij aan een recall van een batch oid.
De data zal enkele jaren bewaard moeten worden (ik ga er nu vanuit gemiddeld 10 jaar). We praten dus over 10 miljoen * 5 = 50 miijoen records in de database.
Vooraf een extractie van bijvoorbeeld chargenummer en vervaldatum lijkt me wel zeker een goede optie. Maar dit zou evt. ook overnight batchgewijs kunnen, zodat de performanceverlies minimaal is.

[DataGhost]

Het is sowieso al ontzettend veel efficienter om de data in de datamatrix daadwerkelijk als data op te slaan, het plaatje is maar een representatie ervan die je zelf makkelijk kan reproduceren (mocht dit later nog een eis zijn) en bovendien is de plaatjes-versie veel groter. Als de data een soort key-value array is dan is het logisch om de data op te slaan in een kolom per key, aangenomen dat deze kolommen (grotendeels) hetzelfde zijn voor alle datamatrices die je binnenkrijgt. Met de juiste indexen op die kolommen kan je vervolgens doodsimpel queries doen op de data die niet 2 jaar duren om een antwoord te geven.
50 miljoen records * 2 kB is ongeveer 100 GB, daar wordt een fatsoenlijk opgezet databasesysteem niet warm of koud van. Belangrijk is dus wel dat je je probleem volledig voor je ziet, "deze datamatrix moet in een database" is iets heel anders dan "deze datamatrix bevat metadata waar we op moeten kunnen zoeken" en verandert je oplossingrichting behoorlijk.
Extractie van de velden kan je trouwens prima tijdens het inlezen doen, computers zijn daar de afgelopen 20 jaar snel zat voor. Voor het renderen van dit forumtopic verwerkt je browser een veelvoud van de 2 kB per datamatrix, en daar zie je ook geen vertraging in.

[Paultje3181]

Top! Duidelijke uitleg. Ik denk dat ik mijn probleem duidelijk heb, maar vind het lastig om de snelheid van een database in te schatten bij dergelijke groottes. Maar als ik het goed begrijp, heeft een database met 100 velden van 20 tekens de voorkeur qua performance t.o.v. 1 veld van 2000 tekens bij minimale zoekacties? Select a,b,c,d where bla is sneller dan select substring(a, 100, 13) where bla?

[CAPSLOCK2000]

Ja, dat is op zich waar. Het gaat eigenlijk meer om het WHERE-gedeelte.

SELECT a WHERE substring(b, 10, 20) LIKE 'abc';

SELECT a WHERE def LIKE 'abc';

De eerste query is langzaam, iedere keer dat je iets met de data wil moet je die substring opnieuw uitvoeren.
In het tweede geval splits je de data eenmalig bij het importeren. De databasesoftware kan het opslaan en zoeken dan efficient doen omdat precies vast ligt hoe je data er uit ziet.

Overigens, alles is een afweging. Wat precies efficient ligt kan nogal afhangen van de context. Een database waar vooral heel veel in wordt geschreven en maar af en toe wordt gezocht is heel anders dan een database met vaste, onveranderlijke informatie waar heel veel in wordt gezocht.

Van wat ik zo van jouw project mee krijg denk ik dat het wel goed komt, ik krijg de indruk dat performance niet zo heel belangrijk is aangezien je het over nachtelijke batchbewerking hebt.
Ik denk dat je beter een beetje kan experimenteren om er gevoel voor te krijgen.

Je geeft aan dat het om 'minimale zoekacties' gaat. Dan is het verschil in WHERE perfomance misschien niet zo interessant.

Overigens heeft nu opsplitsen nog een voordeel, als is dat wat meer theoretisch. Op het moment van inlezen heb je het meeste informatie beschikbaar over de data. Daarna wordt het nooit meer, er gaat hooguit informatie verloren. Je kan bij het opslaan dus maar beter zo veel mogelijk detail vastleggen.
Of dat in praktijk ook een nuttige overweging is voor jouw probleem weet ik natuurlijk niet echt.

[DataGhost]

CAPSLOCK2000 schreef op zaterdag 5 mei 2018 @ 14:53:
Ja, dat is op zich waar. Het gaat eigenlijk meer om het WHERE-gedeelte.

SELECT a WHERE substring(b, 10, 20) LIKE 'abc';

SELECT a WHERE def LIKE 'abc';

De eerste query is langzaam, iedere keer dat je iets met de data wil moet je die substring opnieuw uitvoeren.
In het tweede geval splits je de data eenmalig bij het importeren. De databasesoftware kan het opslaan en zoeken dan efficient doen omdat precies vast ligt hoe je data er uit ziet.

Verduidelijking: de tweede query kan gebruik maken van een index die je op het betreffende veld zet waardoor er gezocht kan worden zoals je dat in een woordenboek zou doen. Je hoeft dus niet het hele woordenboek te lezen maar je kan gewoon zoeken naar P, Pa, Pau, Paul, Pault, Paultj, ... wat veel sneller is en in O(log n) gedaan kan worden.

Van wat ik zo van jouw project mee krijg denk ik dat het wel goed komt, ik krijg de indruk dat performance niet zo heel belangrijk is aangezien je het over nachtelijke batchbewerking hebt.
Ik denk dat je beter een beetje kan experimenteren om er gevoel voor te krijgen.

Je geeft aan dat het om 'minimale zoekacties' gaat. Dan is het verschil in WHERE perfomance misschien niet zo interessant.

Ik zou deze simpele performance-winst altijd pakken, al gaat er maar 1 keer in de dataset gezocht worden. Als je 50 miljoen records van 2kB hebt (dus 100GB) en dat staat op een SSD die 200 MB/s kan lezen, duurt je query gewoon eventjes 10 minuten, terwijl het met de juiste indices millisecondenwerk zal zijn.