[SQL] Ben ik slimmer dan de optimizer?

[Varienaja]

Op ons werk hebben we een systeem gemaakt dat gebruik maakt van een database. Nu hebben we ook enkele views, en doordat we gekoppeld zijn met diverse databases van andere bedrijven zijn die views steeds verschillend.

Nu was ik met de analyzer van IBM Client Access Express (DB2) aan het spelen gisteren en er viel me iets raars op.

We hebben bijvoorbeeld de tabellen Aap, Noot en Mies.

code:

|   Aap   |  |    Noot  |  |   Mies  |
+++++++++++  ++++++++++++  +++++++++++
|VNR1,Date|  |VNR1,VNR2 |  |VNR2     |  Primaire sleutel
|rest     |  |rest      |  |rest     |

Aap en Mies hebben dus een n:m relatie met elkaar.

Wanneer ik de volgende query analyseer (zo ziet een van de views er ongeveer uit):

code:

SELECT * FROM Aap A, Noot N, Mies M
WHERE A.VNR1=N.VNR1 AND N.VNR2=M.VNR2
AND M.VNR2=63

Dan blijkt dat de primaire index wordt gebruikt om in Mies VNR2 op te zoeken (logisch)
Ook in Noot wordt de primaire index gebruikt om VNR2 op te zoeken (logisch)
Maar van Aap worden alle records bekeken. (???)

Ik zou toch zeggen dat met een blik in Noot duidelijk is welke records in Aap je moet opzoeken? Waarom kijkt dat suffe ding de hele tabel door? Ik werk nu met tabelletjes van enkele honderden records, dus 't gaat zowiezo wel snel. Maar onze klanten hebben soms meer dan een miljoen records, en dat loopt in de papieren.

Bovenstaand verhaaltje lijkt alleen te gelden voor databases die op een AS/400 draaien. In Oracle-systemen wordt wel 'BY ROWID' in tabel Aap gekeken.

Iemand tips om die AS/400 beter z'n best te laten doen?

edit:

De Analyzer meldt wel dat de primaire index van Aap wordt gebruikt (gelukkig geen full-table-scan), maar toch bekijkt ie ieder record. De kosten van Noot en Mies zijn steeds 1, maar de kosten van Aap is precies het aantal records in die tabel.

[ Voor 9% gewijzigd door Varienaja op 22-08-2003 09:12 ]

[beetle71]

Mmm, lijkt mij vrij logisch, als je een contructie doet als A.VNR1=N.VNR1 dan zal hij altijd een van beide tabellen helemaal moeten lezen. En volgens mij doet ie dat standaard met de eerste vermelding in de FROM.

Maar als ik jouw query bekijk lijkt het mij logischer om eerst alles met M.VNR=63 uit de M table te selecteren en daar de andere waarden tegenaan te joinen met een expliciete join.

[just my 2 cents ]

[Verwijderd]

Ik weet niet hoe het in DB/2 gaat, maar in Oracle worden records in blokken opgehaald. Het zou natuurlijk kunnen zijn dat de parser, omdat het maar een klein tabelletje is, besluit om de hele tabel op te halen (zit misschien wel in één block). In dat geval lijkt het inefficiënt maar is dat effect verdwenen bij grotere tabellen. Kan je voor de gein eens proberen.
Maar ik moet zeggen, het is een beetje theoretisch en speculatief omdat ik DB/2 niet echt ken.

[EfBe]

aap en mies hebben geen m:n relatie omdat aap's PK niet gelijk is aan het ene FK field in noot, er zit nog een datum in.

[Swa-baldie]

Hmm index op de foreign key?

[whoami]

Swa-baldie schreef op 22 August 2003 @ 12:42:
Hmm index op de foreign key?

Wat wil je daar nu mee zeggen?
Het is een goed idee om op foreign keys een index te leggen. Op die kolom wordt nl. gejoined, en hierdoor gaat het opzoeken sneller.

[Swa-baldie]

Dus... Precies wat ik bedoel.. Dan rammelt de optimizer dus niet de hele tabel af, maar alleen de index...

[Varienaja]

EfBe schreef op 22 augustus 2003 @ 11:19:
aap en mies hebben geen m:n relatie omdat aap's PK niet gelijk is aan het ene FK field in noot, er zit nog een datum in.

Je hebt gelijk. Het is ook een draak van een datamodel. Het lijkt wel mode om niet van te voren een weekje fatsoenlijk na te denken over dat soort dingen. We zitten er maar mooi mee. Maar daar gaat het nu niet over.

Als ik zelf een query doe op Noot en Mies, dan heb ik in 1 klap de gevraagde records binnen. Kijk ik vervolgens even in Noot welke VNR1-waarden er zijn, dan kan ik ook weer in 1 klap het record uit Aap ophalen.

Waarom doet die domme AS/400 dat nou toch niet...?

(Er zijn meer queries, ook op Oracle, waarbij ik me afvraag waarom het toch zo vreselijk lang duurt. Soms is het printen van de volledige tabel en dan met de hand zoeken nog sneller. )

[EfBe]

Varienaja schreef op 22 August 2003 @ 13:21:
Je hebt gelijk. Het is ook een draak van een datamodel. Het lijkt wel mode om niet van te voren een weekje fatsoenlijk na te denken over dat soort dingen. We zitten er maar mooi mee. Maar daar gaat het nu niet over.
Als ik zelf een query doe op Noot en Mies, dan heb ik in 1 klap de gevraagde records binnen. Kijk ik vervolgens even in Noot welke VNR1-waarden er zijn, dan kan ik ook weer in 1 klap het record uit Aap ophalen.

Dat kan alleen als VNR1 al uniek is binnen aap (en die datum dus uit je PK kan (sowieso dom om een datum in een PK op te nemen)). Is VNR1 niet uniek binnen aap, dan krijg je niet 1 maar vele records terug.

[Brothar]

In wezen hebben aap en noot al een N:M relatie, door die datum in de pk van aap.
Blijkbaar kan de "engine" die samengestelde pk niet afzoeken.
Kun je dan een index laten maken op alleen VNR1 in aap, en die gebruiken bij deze query ?

[ Voor 4% gewijzigd door Brothar op 22-08-2003 13:38 ]

[EfBe]

Brothar schreef op 22 augustus 2003 @ 13:38:
In wezen hebben aap en noot al een N:M relatie, door die datum in de pk van aap.
Blijkbaar kan de "engine" die samengestelde pk niet afzoeken.
Kun je dan een index laten maken op alleen VNR1 in aap, en die gebruiken bij deze query ?

Nee aap en noot hebben _GEEN_ relatie, er is geen FK te definieren vanuit noot die een unieke row aanwijst als PK element van de FK, want de datum in aap zit niet in noot. Dat toevallig een onderdeel van de PK in aap gebruikt wordt in noot doet niet ter zake.

[Varienaja]

Brothar schreef op 22 August 2003 @ 13:38:
Kun je dan een index laten maken op alleen VNR1 in aap, en die gebruiken bij deze query ?

Heb ik gedaan; helpt niet.

EfBe: voor ieder record in Noot zijn er meestal 4 in Mies.
voor ieder record in Mies zit er doorgaans ook 1 in Noot, *heel* soms meer dan1.

(Voor de duidelijkheid: het gaat over verkopen van huizen. In Aap staan huizen, in Mies staan verkopen. Soms worden in 1 verkoop meerdere huizen verkocht, vandaar de n:m relatie.

De huizen zijn in (hele vieze, ik weet het) datum-gebieden ingedeeld. Er zijn steeds vakken met een lengte van 4 jaar waarin gegevens worden opgeslagen. Zo kan je per 4 jaar kijken hoe de kenmerken (met name de waarde) van het huis zijn gewijzigd.)

[Brothar]

Kun je redesignen (tijd, geld, 24*7) ? (zul je wel de hele database moeten gaan converteren).
Dan zou je kunnen overwegen de query te laten zoals die is. En aan het redesign te gaan werken. Dat zou je dan kunnen implementeren op het moment dat je traagheidsproblemen gaat krijgen met die query.

[Varienaja]

Brothar schreef op 22 August 2003 @ 14:07:
Kun je redesignen (tijd, geld, 24*7) ? (zul je wel de hele database moeten gaan converteren).
Dan zou je kunnen overwegen de query te laten zoals die is. En aan het redesign te gaan werken. Dat zou je dan kunnen implementeren op het moment dat je traagheidsproblemen gaat krijgen met die query.

Ik wil al 5 jaar redesignen. In plaats daarvan wordt het alleen maar ingewikkelder gemaakt

[Brothar]

Dan kom je dus nu (steeds dichter) op een punt dat onderhoud en aanpassing (de toekomstige kosten contant gemaakt) veel kostbaarder is dan redesign.

[ Voor 98% gewijzigd door Brothar op 22-08-2003 14:20 . Reden: on-topic blijven ]

[HansMij]

In dat datamodel hebben zowel Aap als Noot een gecombineerde sleutel. Dat betekend dat als je op de helft van de sleutel zoekt, kan het best gebeuren dat er meerdere records aan de voorwaarden voldoen. Ik denk dat je je datamodel eens na moet kijken. Als er namelijk binnen Noot meerdere records zijn met dezelfde VNR2, dan levert dit dus meerdere records als resultaat op.

[Varienaja]

HansMij schreef op 22 augustus 2003 @ 15:30:
...kan het best gebeuren dat er meerdere records aan de voorwaarden voldoen.

Ja natuurlijk!
Eén huis kan best meerdere keren verkocht zijn. En andersom kan bij 1 verkoop meer dan 1 huis betrokken zijn.