[SQL/Access] waarom selectie uit meerdere tabellen zo traag?

[TromboneFreakus]

Voor de JFV heb ik een database ontworpen en die heeft nu drie tabellen: ledenlijst, oud-leden en reunisten. Om snel contact met leden te kunnen zoeken heb ik al een interface geprogrammeerd om op basis van allerlei criteria de mensen te kunnen filteren.

Nu wilde ik dit aanpassen om dit filteren ook op basis van andere tabellen te kunnen doen, maar vooral ook: liefst cumulatief. Waarom is dat nu zo traag? Het gaat bijv. om deze query:

code:

SELECT * FROM `ledenlijst`, `oud-leden`, `reunisten` WHERE ((len(Email) > 3))

Aantal records: zo'n 2500. Zo'n aantal records in een klap wijzigen is in een paar miliseconden gedaan. Bovenstaande query uitvoeren duurt minuten en leidt tot een enorme belasting van Access.

Waarom?? Of kan dit ook efficienter?

[P_de_B]

Je creeert een 'carthesian product', oftwel een kruiscombinatie tussen alle records omdat je geen join criteria opgeeft.

Je moet in de WHERE de join criteria opgeven of (liever) een andere manier van joinen gebruiken

SQL:

SELECT 
FROM Ledenlijst
INNER JOIN OudLeden ON Ledenlijst.LidId = Oudleden.LidId
INNER JOIN Reunisten ON Ledenlijst.LidId = Reunisten.LidId
WHERE ...

Overigens vraag ik me af of het wel correct is om hier verschillende tabellen voor te hebben, er staat overal min of meer hetzelfde in volgens mij. Dat gaat echter buiten de scope van deze vraag.

[TromboneFreakus]

P_de_B schreef op vrijdag 17 maart 2006 @ 13:33:
Je creeert een 'carthesian product', oftwel een kruiscombinatie tussen alle records omdat je geen join criteria opgeeft.

Je moet in de WHERE de join criteria opgeven of (liever) een andere manier van joinen gebruiken

Maar dan krijg ik alleen de leden met dezelfde ID's, dus niemand. Of snap ik nu eits niet?

Overigens vraag ik me af of het wel correct is om hier verschillende tabellen voor te hebben, er staat overal min of meer hetzelfde in volgens mij. Dat gaat echter buiten de scope van deze vraag.

Je zou liever een tabel erbij maken met de status van een lid en een tabel met een een-op-een relatie statusID en lidID??

[Dido]

Je zou liever een tabel erbij maken met de status van een lid en een tabel met een een-op-een relatie statusID en lidID??

Waarom een tabel erbij? Eerder twee tabellen minder.

Wat als je alles in 1 tabel zet, met een veld Status, dat "lid", "oudlid", "reunist" kan zijn?

(Dat is nog steeds sub-optimaal, maar wel een handiger richting)

Met twee boolean velden ben je er ook, trouwens (Actief lid Ja/Nee en Reunist Ja/Nee).

[ Voor 46% gewijzigd door Dido op 17-03-2006 13:56 ]

[P_de_B]

TromboneFreakus schreef op vrijdag 17 maart 2006 @ 13:48:
[...]


Maar dan krijg ik alleen de leden met dezelfde ID's, dus niemand. Of snap ik nu eits niet?

Je snapt wel het principe van joinen? Als er geen relatie tussen de tabellen zit kun je ze niet op deze manier doorzoeken, wat je dan kan doen is een UNION gebruiken.

SQL:

SELECT * FROM tabel1 WHERE
UNION
SELECT * FROM tabel2 WHERE
etc.

Je zou liever een tabel erbij maken met de status van een lid en een tabel met een een-op-een relatie statusID en lidID??

Zoiets, uit het oogpunt van normaliseren lijkt me dit wel wenselijk. Om het exact aan te kunnen geven is echter wel wat meer informatie nodig .

[Dido]

P_de_B schreef op vrijdag 17 maart 2006 @ 13:55:
Zoiets, uit het oogpunt van normaliseren lijkt me dit wel wenselijk. Om het exact aan te kunnen geven is echter wel wat meer informatie nodig .

Lijkt me allesbehalve wenselijk. Als status een property van een persoon (lid/oudlid/reunist) is, heb je geen extra tabel nodig. Of een tabel met status_id en status_beschrijving, dat heeft wel voordelen.

Ik vraag me trouwens af wat er in de TS' model gebeurt als er ooit ereleden komen.

(Het antwoord is: een nieuwe tabel, en alle queries herschrijven )

[bazkar]

Wat jij wilt is niet joinen maar unionen (niet achter elkaar plakken, maar onder elkaar)

SELECT * FROM ledenlijst WHERE LEN(Email> 3)
UNION
SELECT * FROM oud-leden WHERE LEN(Email> 3)
UNION
SELECT * FROM reunisten WHERE LEN(Email> 3)
.

Hiervoor moeten de kolommen wel gelijk zijn van de 3 tabellen, anders moet je een subset nemen (SELECT Naam FROM.... of zoiets).

Edit: te laat.

Wat nog het mooist is (als er inderdaad afwijkende kolommen zijn tussen de 3 tabellen), is 1 tabel leden (of personen for that matter) en dan foreign-key links naar de 3 subtabellen.

Dus Persoon tabel met kolommen (Naam, Email Adres, etc.)
ledenlijst met zaken als (Contributie betaald op) ik noem maar wat
Oud-leden hebben dan kolommen als (Lidmaatschap beeindigd op)
etc.

Ga alsjeblieft geen statusveld gebruiken met nummertjes of dat soort onzin, dat loopt spaak. Altijd. Ook kolommen met zaken als Reunist Ja/nee is niet aan te raden, tenzij je geen extra informatie bij wilt houden van reunisten die je niet van andere leden hebt.

[ Voor 52% gewijzigd door bazkar op 17-03-2006 14:05 ]

[whoami]

-> PRG

[P_de_B]

Dido schreef op vrijdag 17 maart 2006 @ 14:00:
[...]

Lijkt me allesbehalve wenselijk. Als status een property van een persoon (lid/oudlid/reunist) is, heb je geen extra tabel nodig. Of een tabel met status_id en status_beschrijving, dat heeft wel voordelen.

Tuurlijk, wat TS precies beschrijft is nu niet handig, daarom zei ik dat er wat meer info nodig was om echt een goed advies te geven. Als iemand een van de 3 mogelijke statussen heeft is een veld 'Status' prima, het kan ook zijn dat iemand nog lid is, en ook op de reunie komt.

[TromboneFreakus]

bazkar schreef op vrijdag 17 maart 2006 @ 14:00:
Wat jij wilt is niet joinen maar unionen (niet achter elkaar plakken, maar onder elkaar)

SELECT * FROM ledenlijst WHERE LEN(Email> 3)
UNION
SELECT * FROM oud-leden WHERE LEN(Email> 3)
UNION
SELECT * FROM reunisten WHERE LEN(Email> 3)
.
Hiervoor moeten de kolommen wel gelijk zijn van de 3 tabellen, anders moet je een subset nemen (SELECT Naam FROM.... of zoiets).

Yup, merci, dit werkt fantastisch.

Ga alsjeblieft geen statusveld gebruiken met nummertjes of dat soort onzin, dat loopt spaak. Altijd. Ook kolommen met zaken als Reunist Ja/nee is niet aan te raden, tenzij je geen extra informatie bij wilt houden van reunisten die je niet van andere leden hebt.

Hierover verschillen de meningen hier duidelijk op dit forum. Het lijkt mij min of meer structuurmatig om het even of ik de hele database herschrijf om een extra tabel toe te voegen voor 'type lidmaatschap' en een veld in de tabel 'ledenlijst' om de koppeling te leggen, of dat ik zoals nu meerdere tabellen met dezelfde structuur heb. Ik weet alleen wel dat het laatste, gezien de bestaande opzet, me nu veel minder werk kost.

[P_de_B]

Maar als je nu nog een soort leden erbij krijgt? Daarna is het performance technisch niet aan te raden het op deze manier te doen. Vroeger of later zul je spijt krijgen als je nu niet even de moeite neemt de boel goed aan te passen.

[NMe]

TromboneFreakus schreef op zondag 19 maart 2006 @ 15:47:
Hierover verschillen de meningen hier duidelijk op dit forum. Het lijkt mij min of meer structuurmatig om het even of ik de hele database herschrijf om een extra tabel toe te voegen voor 'type lidmaatschap' en een veld in de tabel 'ledenlijst' om de koppeling te leggen, of dat ik zoals nu meerdere tabellen met dezelfde structuur heb. Ik weet alleen wel dat het laatste, gezien de bestaande opzet, me nu veel minder werk kost.

Wanneer je je database moet aanpassen om een nieuwe tabel toe te voegen moet je alle queries aan gaan passen die data uit deze nieuwe tabel moet gaan halen. Wanneer je een statusveld hebt, en een losse statustabel waarin je alleen maar een record hoeft toe te voegen, hoef je aan je queries in principe niets te veranderen. Dan mag je mij eens vertellen wat beter ontwerp is.

[TromboneFreakus]

-NMe- schreef op zondag 19 maart 2006 @ 16:09:
[...]

Wanneer je je database moet aanpassen om een nieuwe tabel toe te voegen moet je alle queries aan gaan passen die data uit deze nieuwe tabel moet gaan halen. Wanneer je een statusveld hebt, en een losse statustabel waarin je alleen maar een record hoeft toe te voegen, hoef je aan je queries in principe niets te veranderen. Dan mag je mij eens vertellen wat beter ontwerp is.

Ik zie je punt. Enige nuance is wel dat de queries die de gegevens uit de tabellen halen toch geprogrammeerd worden (alles gaat via user interface als form, er is geen gebruiker die zelf SQL kan) en dat het programmeertechnisch vrij simpel is om in een loopje het woordje ' UNION ' te plakken tussen een herhaling van de reeds programmeertechnisch opgebouwde query (en evt. tabelnaam vervangen).

Juist voor zaken als commissies binnen de club heb ik al het veel flexibelere ontwerp van een tabel met 'commissies' en een tabel met 'lid aan commissie' gemaakt. Want ik zie het verschil en het voordeel wel. Maar ik verwacht in dit verband niet nog een soort lidmaatschap. Het huidige onderscheid is juist al een beetje omstreden....

[P_de_B]

Als het onderscheid omstreden is, zou ik _zeker_ zeggen dat ze niet een eigen tabel verdienen.