[SQL] Foreign keys en relatie tables

[zoepercavia]

Ik heb een vraag voor de SQL experts ter plekke.

In het kader van het upgraden van een Access database naar SQL Server (Express) loop ik tegen wat problemen aan. Het voornaamste probleem is dat SQL Server voor bepaalde queries aanzienlijk trager is dan Access. Dit heeft vast te maken met mijn database ontwerp maar naar wat rond kijken heb ik wel een gevoel wat ik moet doen, maar weet ik niet helemaal of ik in de goede richting zit.

Ik heb een tabel ontwerp als volgt

• TblCustomer (Id, primary key)
• RelCustomerOrder (CustomerId, OrderId)
• TblOrder (Id, primary key)

waarbij de rijen in RelCustomerOrder de connectie leggen tussen customers en orders. Een customer heeft 0 of meer orders en een order heeft 0 of 1 customers.

Dit wijkt af van het gebruik van Foreign Keys (FK) wat ik eigenlijk had moeten doen zover ik snap wat ik tot nu toe gelezen heb. Mijn idee was om een FK te maken op RelCustomerOrder van OrderId naar TblOrder.Id, alleen moeten er dan, zover ik begrijp evenveel rijen in RelCustomerOrder als TblOrder zitten.

Nu mijn vragen

• Moet ik dit oplossen door NULL te gebruiken in RelCustomerOrder als een order (nog) geen customer heeft?
• Is er een andere (betere) oplossing?

Ik kan me voorstellen dat mensen het gebruik van tabellen om relaties te leggen wat typisch is, maar ik heb dat destijds (10 jaar geleden) zo ontworpen voor bepaalde redenen, en ik wil er liever (nog) niet vanaf.

Overigens ook typisch dat SQL Server zoveel trager is dan Acces voor bepaalde queries.

[RobIII]

zoepercavia schreef op zaterdag 23 oktober 2010 @ 16:11:
en een order heeft 0 of 1 customers.

Want een order kan geplaatst zijn door, of geleverd worden naar, niemand
Geef een order gewoon een customerid veld (en hell, als je dit wil doorzetten maak je 'm nullable). Een koppeltabel is helemaal niet nodig.

zoepercavia schreef op zaterdag 23 oktober 2010 @ 16:11:
Overigens ook typisch dat SQL Server zoveel trager is dan Acces voor bepaalde queries.

Hoewel in sommige uitzonderlijke situaties misschien wel eens kan voorkomen dat Access een pietsie beter performt (ik sluit het dus nietuit) heb ik er een heel, héél hard hoofd in te geloven dat dit hier aan de orde is. Heb je je indexes uberhaupt goed staan bijvoorbeeld?

zoepercavia schreef op zaterdag 23 oktober 2010 @ 16:11:
Dit wijkt af van het gebruik van Foreign Keys (FK) wat ik eigenlijk had moeten doen zover ik snap wat ik tot nu toe gelezen heb. Mijn idee was om een FK te maken op RelCustomerOrder van OrderId naar TblOrder.Id,

En op RelCustomerOrder naar Customer dan?

zoepercavia schreef op zaterdag 23 oktober 2010 @ 16:11:
alleen moeten er dan, zover ik begrijp evenveel rijen in RelCustomerOrder als TblOrder zitten.

Want
Ik denk dat je het principe FK nog niet helemaal begrijpt Zoals ik al zei: een koppeltabel is hier helemaal niet nodig; ik zou niet te snel conclusies gaan trekken over de performance van Access en/of SQL Server zolang je DB niet in orde is.

[ Voor 72% gewijzigd door RobIII op 23-10-2010 16:21 ]

[zoepercavia]

Hrmz.. ik schaam mij diep. Dit topic kan gesloten worden.

Ik had bij het omzetten van de code die de SQL bouwt alle '=' vervangen door LIKE (omdat ntext in Sql Server geen '=' ondersteunt). En dus ook bv WHERE huppeldepup.id LIKE blaat.id Maar dat moet ik dan natuurlijk alleen maar doen voor kolommen die werkelijk van het type ntext zijn.

Nu dat gefixed is is Sql Server ca. 10x sneller.

[RobIII]

Woa, woa, woa... Even stapje terug: ik neem aan dat je wel (n)varchars hebt gebruikt waar van toepassing? Of zijn alle textvelden van 't type ntext?

Een slotje is niet nodig op een topic als je je oplossing hebt. Zie daarvoor ook onze faq betreffende topiceinde.

[Lethalis]

zoepercavia schreef op zaterdag 23 oktober 2010 @ 16:11:
Ik kan me voorstellen dat mensen het gebruik van tabellen om relaties te leggen wat typisch is, maar ik heb dat destijds (10 jaar geleden) zo ontworpen voor bepaalde redenen, en ik wil er liever (nog) niet vanaf.

Tabellen om relaties te leggen is niet vreemd, maar dan moeten het wel veel-op-veel relaties zijn (een patient komt bij meerdere specialisten en specialisten hebben meerdere patienten). Zodra je 1-op-veel relaties (een klant heeft meerdere orders, maar een order behoort altijd tot 1 klant) krijgt, moet je met een foreign key werken.

Veel-op-veel relatie impliceren vaak ook een nieuwe entiteit. "Een patient komt bij meerdere specialisten en specialisten hebben meerdere patienten" zou opgeslagen kunnen worden in een tabel Behandeling, waar dus naast het patientID en specialistID ook gegevens over de aard van de relatie staan.

Het gebruik van ntext valt af te raden, omdat je deze niet goed kunt indexen. Nvarchar daarentegen wel.

Staat op de relatie tabel RelCustomerOrder eigenlijk wel een composite index voor beide id's?

Ik raad je sowieso aan om eens te zoeken naar Entity Relationship Modeling en database normalisatie, om zo meer te leren over een goed database ontwerp.

[zoepercavia]

Wat ik gedaan heb destijds is een object-relational mapper implementeren naar het voorbeeld van Martin Fowler's Enterprise Application Architecture patterns (http://martinfowler.com/eaaCatalog/). Erg goed boek moet ik zeggen, wat volgens mij nu nog steeds relevant is.

Ik heb toen besloten voor de eenvoud van implementatie om alle relaties, niet alleen n-n maar ook 1-n, te implementeren met koppeltabellen (http://martinfowler.com/e...ociationTableMapping.html). Uiteindelijk liep het project voor mij zo goed dat ik het tot nu toe in al mijn projecten gebruik. Ik weet dat er imiddels vele andere ORM frameworks zijn (LLBLGen, Entity Framework, etc), maar ik heb niet uitgezocht of die alle specifieke dingen ondersteunen die ik er zelf ingemaakt heb.

Een van de voordelen van koppeltabellen is dat je eventueel nog metadata kan toevoegen aan de relatie, bv. de naam van de relatie. Dit is handig als er tussen twee klassen twee relaties bestaan die iets anders betekenen, bv. een werknemer die n affiliaties heeft, maar 1 hoofd affiliatie. Door de naam 'hoofd' toe te voegen aan de koppeltabel kan de mapper onderscheid te maken tussen die twee typen relaties. Ik ben het met de vorige post eens dat in sommige gevallen het dan handig is om een nieuw koppel object aan te maken, HoofdAffiliatie, bijvoorbeeld als je ook nog de functie van de werknemer in die affiliatie wil opslaan.

Waar mijn 1e post overging is de vraag of het zinnig is om FK constraints te maken op koppeltabellen zodat queries sneller uitgevoerd kunnen worden. Dat was ingegeven door dat alles enorm traag was geworden door dat ik alle = door LIKE had vervangen. Dat probleem is er nu niet meer, maar de vraag blijft open staan: zou het zinnig zijn om voor 1-n relaties dit te doen?

[cariolive23]

zoepercavia schreef op zaterdag 06 november 2010 @ 12:56:
Ik heb toen besloten voor de eenvoud van implementatie om alle relaties, niet alleen n-n maar ook 1-n, te implementeren met koppeltabellen (http://martinfowler.com/e...ociationTableMapping.html).

Waarom? Er is geen enkele reden te bedenken waarom een 1-n relatie een koppeltabel nodig zou hebben. Dit voegt niets toe, behalve dat de database langzamer wordt.

Waar mijn 1e post overging is de vraag of het zinnig is om FK constraints te maken op koppeltabellen zodat queries sneller uitgevoerd kunnen worden.

Een constraint heeft niets te maken met performance maar alles met data integriteit. Zonder een FK kan de database geen enkele integriteit garanderen, iets wat een RDBMS wél zou moeten doen, zelfs voor is uitgevonden.

Dat was ingegeven door dat alles enorm traag was geworden door dat ik alle = door LIKE had vervangen. Dat probleem is er nu niet meer, maar de vraag blijft open staan: zou het zinnig zijn om voor 1-n relaties dit te doen?

Waarom een LIKE gebruiken voor een vergelijking in een koppeltabel? Hier kan ik me echt niks bij voorstellen, tenzij er flinke fouten in het datamodel zitten.

Tip: Ga netjes normaliseren en gebruik bewezen methodes. Overal koppeltabellen gebruiken is gewoon fout en zorgt automatisch voor performance verlies, dat kan niet anders. LIKE gebruiken daar waar je = nodig hebt, gaat ook voor problemen zorgen: met een LIKE '%128%' kunnen er bv. geen indexen meer worden gebruikt en ga je hele rare resultaten krijgen.

[JaQ]

cariolive23 schreef op zaterdag 06 november 2010 @ 13:58:

Een constraint heeft niets te maken met performance maar alles met data integriteit.

Je bent altijd zo lekker stellig....

Nu wist ik zeker dat Oracle constraints gebruikt in de optimizer, maar sql-server blijkbaar ook: klik.

[cariolive23]

Dat constraints ook bruikbaar zijn voor optimalisaties, dat is weer wat anders. Maar ze worden in de eerste plaats aangemaakt voor dataintegriteit. Performance kun je ook verbeteren zonder constraints, betere queries en indexen doen ook al wonderen.

[JaQ]

cariolive23 schreef op zaterdag 06 november 2010 @ 17:15:


Dat constraints ook bruikbaar zijn voor optimalisaties, dat is weer wat anders. Maar ze worden in de eerste plaats aangemaakt voor dataintegriteit. Performance kun je ook verbeteren zonder constraints, betere queries en indexen doen ook al wonderen.

Je stelde dat constraints niets met performance te maken hebben, dat is niet waar. Dat er andere zaken zijn die meer invloed hebben is een tweede

stellig zijn zal wel een DBA-dingetje zijn