[SQL] 1-op-1 relatie tabellen bij kolom met veel data

[Jan_V]

Gisteren was ik op de SQL Zaterdag en hoorde een van de sprekers iets zeggen waar ik nog nooit over had nagedacht. Namelijk dat het in sommige situaties misschien handig kan zijn om kolommen met veel data (varchar(8000), varchar(max), varbinary, etc) op te slaan in een andere tabel welke een 1:1 relatie heeft met de originele tabel.
Dit zou als voordeel bieden dat een table-scan veel sneller uitgevoerd zou kunnen worden, omdat de kolom met veel data dan niet hoeft worden bekeken en er dus meer records in een page kunnen worden geplaatst, wat de performance ten goede zou kunnen komen. Hier zit natuurlijk wat in.

Wanneer ik zelf een database moet ontwerpen plaats ik momenteel vaak de grote kolommen gewoon in dezelfde tabel als de overige velden. Dit hoeft natuurlijk niet, want vaak hoef je de kolommen met veel data alleen te tonen/op te vragen op de detail pagina van een item. In alle overige gevallen ben je vaak alleen geïnteresseerd in de kleinere kolommen welke FK's, id's, etc. bevatten.
Het opslaan van een grote kolom in een andere tabel kost natuurlijk wel iets meer bij het ophalen van de data (extra join noodzakelijk).

Zijn er hier mensen die een dergelijke opzet gebruiken en is dat dan om performance redenen? Het komt de duidelijkheid van je database vaak niet ten goede denk ik.

[Janoz]

Wanneer je ipv varchar(8000) colomtypes als BLOB, TEXT of CBLOB gebruikt gebeurt dit onder de motorkap al vanzelf.

[Jan_V]

Zoiets dacht ik ook. Wanneer je een varchar(max) (text is depricated voor zover ik weet) gebruikt, dat je dan gewoon een pointer terug krijgt naar de tekst die pas wordt opgehaald wanneer je die nodig hebt.
Vandaar dat ik het ook een aparte stelling vond.

[JaQ]

Janoz schreef op zondag 24 oktober 2010 @ 15:01:
Wanneer je ipv varchar(8000) colomtypes als BLOB, TEXT of CBLOB gebruikt gebeurt dit onder de motorkap al vanzelf.

Je kan een tabel zo modificeren dat de blob (etc) wel in de rij wordt opgeslagen. Je beschrijft enkel het default gedrag van SQL-Server. In andere RDBMS-en kan dit gedrag anders zijn, bijvoorbeeld in Oracle wordt een lob default wel in de rij opgeslagen (en moet je derhalve expliciet aangeven of je de lob ergens anders wilt opslaan).

Misschien was de spreker iemand die gek is op database onafhankelijke programmatuur (of zo)

[Wolfboy]

Het verschilt nogal per database type of dit wel/geen effect heeft.

Bij Oracle moet je expliciet opgeven waar je het spul wil opslaan, anders komt het gewoon de tabel terecht.
Bij Postgres heb je TOAST die het sowieso al ergens opslaat (kan je ook opgeven waar het terecht moet komen).
Bij SQL server gaat dit automatisch bij Text types en niet bij varchar kolommen.
Bij MySQL gaat dit afaik hetzelfde als bij SQL server

Dus... afhankelijk van je database server kan dit belangrijk zijn ja

[GlowMouse]

Wolfboy schreef op zondag 24 oktober 2010 @ 20:34:
Bij MySQL gaat dit afaik hetzelfde als bij SQL server

Het is sterk afhankelijk van de storage engine, en zelfs binnen storage engines is het erg verschillend, zie hier en hier voor innodb.

Het zal alleen weinig voorkomen dat je full table scans op zulke tabellen uitvoert.

[Anoniem: 164019]

En doet niet elke database zo'n beetje wat de bedoeling is als je (zoals Janoz aangeeft) een large object datatype (BLOB, CLOB, NCLOB) gebruikt? Waar wil Oracle dat je het aangeeft, bijvoorbeeld? Toch hopelijk niet in het schema zelf?

[ Voor 27% gewijzigd door Anoniem: 164019 op 25-10-2010 08:27 ]

[Voutloos]

Jan_V schreef op zondag 24 oktober 2010 @ 14:28:
Dit zou als voordeel bieden dat een table-scan veel sneller uitgevoerd zou kunnen worden...

De echte optimalisatie is natuurlijk het weghalen van de noodzaak voor een tablescan.

[Wolfboy]

Voutloos schreef op maandag 25 oktober 2010 @ 08:59:
[...]
De echte optimalisatie is natuurlijk het weghalen van de noodzaak voor een tablescan.

Maar... laten we nu even uitgaan van een tabel waar je maar 25 van de 26 velden nodig hebt en dus niet dat veld met die enorme varchar kolom.

Daarnaast is de tabel geclusterd op datum en sorteer je op datum.

In dat geval moet je ook bij een simpele "haal de laatste 1000 rijen op" nog steeds over die varchar kolom heenspringen terwijl je anders alles in 1 sequentiele read zou kunnen lezen.

[Janoz]

Default setting of niet. De storage engine/tabel configuratie is het niveau om dit soort optimalisaties op te pakken, niet door je eigen datamodel te verminken.

[Anoniem: 164019]

Wolfboy schreef op maandag 25 oktober 2010 @ 10:48:
[...]
enorme varchar kolom

Nogmaals: gebruik BLOB (BINARY LARGE OBJECT), CLOB (CHARACTER LARGE OBJECT) of NCLOB (NATIONAL CHARACTER LARGE OBJECT) als je al per definitie weet dat het een kolom met "enorme lappen tekst" kan zijn. Waarom? Omdat je model zo in elkaar zit en dit dus het juiste datatype is. Een beetje DBMS moet wel snappen dat 'ie daar voorzichtig mee moet omspringen wanneer performance belangrijk is.

[ Voor 12% gewijzigd door Anoniem: 164019 op 25-10-2010 12:54 ]

[YopY]

Janoz schreef op maandag 25 oktober 2010 @ 10:51:
Default setting of niet. De storage engine/tabel configuratie is het niveau om dit soort optimalisaties op te pakken, niet door je eigen datamodel te verminken.

+1. Doe gewoon een goed datamodel (op basis van een goeie analyse) en haal alles uit je database op basis van gewone indexes en instellingen, ga dan pas grijpen naar dit soort 'optimalisaties'.

[tazzman]

YopY schreef op maandag 25 oktober 2010 @ 12:59:
[...]


+1. Doe gewoon een goed datamodel (op basis van een goeie analyse) en haal alles uit je database op basis van gewone indexes en instellingen, ga dan pas grijpen naar dit soort 'optimalisaties'.

Oneens - je moet de scheiding treffen tussen je concept datamodel, je logische datamodel en je physieke datamodel.

Wanneer je echt grote databases (Enterprise Data Warehousing) ontwerpt op bijvoorbeeld Teradata of HP Neoview dan is het zeker belangrijk om rekening te houden met je physieke model en hoe je bepaalde nodes wel of niet wilt belasten. Ik kom regelmatig bij klanten waar juist geen rekening gehouden is met het physieke model en te veel berust is op het vermogen de interne storage engine van Teradata of Neoview .. met als gevolg dat na 15-20TB de omgeving compleet vastloopt.

[RobIII]

tazzman schreef op donderdag 18 november 2010 @ 01:26:
Wanneer je echt grote databases ... met als gevolg dat na 15-20TB de omgeving compleet vastloopt.

Is dit nou een verkapte "kijk mij eens een grote piemel hebben"-post? Komaan; we hebben het hier over "doorsnee" situaties. Over databases in die orde van grootte, noch Teradata / Neoview heeft niemand iets gezegd. Daarbij bevestig je alleen maar YopY's post; als je verwacht dusdanig veel data te gaan verstouwen had je daar eerder bij stil moeten staan (goede analyse).

[ Voor 15% gewijzigd door RobIII op 18-11-2010 01:44 ]

[defcon84]

RobIII schreef op donderdag 18 november 2010 @ 01:42:
"kijk mij eens een grote piemel hebben"

ja, ik weet het, onnodige reply, maar hier moest ik even voor op de grond slaan