[SQL] View/Temp table best practices

[arnem_]

Ik heb een genormaliseerde database (mssql server) waar ik best complexe informatie uithaal, zoals hierarchische structuren voor gebruik op een website. Eigenlijk gaat het allemaal als de brandweer maar ik vrees dat als de hoeveelheid data een beetje gaat toenemen, het feest maar zo eens over kan zijn. Ik wil daarom eigenlijk gebruik maken van (hulp) tabellen die een levensduur hebben van ergens tussen een dag en een paar dagen. Deze tabellen zorgen dat het aantal queries drastisch lager komt te liggen en de performance hoger. Mutaties op de database zijn niet zo'n probleem. Als de hulptabellen een week achterlopen is er geen man over boord. Het aantal mutiaries per dag is ook bes te overzien.

Nu kan ik hier van alles voor verzinnen, en dat doe dan ook, maar wat zijn de best practices? Ik zie door de typen tabellen de database niet meer... Ik kan me niet voorstellen dat hier geen algemene best practice voor is. En wat doet de server al voor je op de achtergrond?

• Het gebruik van msssql temporary tabellen. Deze leven alleen binnen de executie van een enkele procedure en zijn daarom hier volgens mij hier niet zo relevant.
• Views: versimpelen de queries wel maar elke stap wordt volgens mij telkens opnieuw uitgevoerd en gaat dus niet echt helpen bij performance issues.
• Vaste tabellen met een trigger ergens zodat deze op gezette tijden bijgewerkt worden
• ... Jij leeft in de jaren negentig. Tegenwoordig zijn de databases zo slim dat ze elke dure actie tracken en zelf opslaan. Zelfs mutaties op de database worden daarin netjes meegenomen zodat je er eigenlijk niet naar hoeft te kijken zolang er maar voldoende reserve geheugen beschikbaar is voor deze optimalisatietabellen.
• ... Anders

[Kwistnix]

Als het om complexe queries gaat met veel joins en/of aggregate functies, dan zou eens kunnen kijken of materialized views daar iets kunnen betekenen. Volgens mij noemt MS SQL dat concept een indexed view?

[Douweegbertje]

Uh ja.. de best practice is het geen wat aansluit op jouw wensen.

views
Ik ben persoonlijk absoluut geen voorstander van views. Een view is eigenlijk niets meer of minder dan een opgeslagen query waar je vervolgens alsnog een query op kan uitvoeren. Wat ik hier ongeveer mee wil zeggen is dat je functionaliteiten gaat toevoegen aan een database, terwijl ik persoonlijk vind dat je dit gewoon in je applicatie moet hebben zitten.

Naar mate je DB echt groot gaat worden moet je verdomd goed opletten wat je allemaal doet. Stel dat je een view hebt, maar ook nog andere queries op een table uitvoert die óók in die view zit. Vervolgens wil je toch de table aanpassen (want extra feature). In feite vergeet je dan de view..
Redelijk simpel te beheren met 1 view en 1 table, maar op het moment dat je met 150 views zit en 250 tables dan wordt je op een gegeven moment gek (dat is dus mijn ervaring).

Nja even mijn persoonlijke mening daargelaten: het praktisch nut is ver te zoeken voor jou. De view voert alsnog een query uit alsof je het ruw op de table doet. Je gaat dus geen moer verschil merken of je het nu via een view of een ruwe query doet. Afstrepen dus.

temporary table heb je correct, dus in feite ook niet praktisch.

Vaste tabellen met een trigger

Ja.. ja..
Ik gebruik bijvoorbeeld een trigger om via een webapplicatie een trigger te zetten voor een .NET applicatie. Derhalve kan ik ook nog op een bepaalde manier de triggers beheren. Ik gok dat jij bijvoorbeeld bij een insert, in een andere table een +1/-1 voor een waarde wilt geven. Ik vind dat niet zo heeuul handig.
Je wilt een trigger meer gebruiken voor een echte action/event dan het echt gebruiken om statistieken te updaten. Ik zeg niet perse dat het niet kan, maar je moet echt goed kijken naar wat je precies wilt voordat je een trigger gebruikt...

SP's

Wijzelf gebruiken stored procedures die 'snachts of 1x per week/maand worden aangeroepen. Deze maken rapportages, updaten diverse dingen en ga zo maar door. Dit zou je ook gewoon om de x minuten kunnen draaien maar again: helemaal afhankelijk van wat je wilt. Persoonlijk lijkt mij dit één van de betere manieren omdat je stabiele data krijgt.

Indexed views: dit zou je ook kunnen checken; https://www.simple-talk.c...indexed-views-the-basics/


Het andere is: een database is er voor gemaakt om veel data te hebben. Jij hebt het wel over een genormaliseerde database maar hoe kan die nu goed in elkaar zitten als je niet eens fatsoenlijk de data die jij wilt eruit kunt halen? Er valt soms echt veel winst te behalen als je eens goed kijkt naar hoe dingen worden uitgevoerd...


Niet voor niets heb je gewoon mensen die als titel 'database administrator' hebben. Veel bedrijven gooien een 'basic programmeur' aan de DB maar vergeten gewoon dat het fucking lastig kan zijn. Ik heb de laatste maanden relatief veel geleerd en diverse queries enorm verbeterd inclusief de server settings en dergelijke. De enige manier omdat goed te doen is veel lezen, leren en uiteindelijk enorrrmmm veel meten en proberen.

[arnem_]

Met trigger bedoel ik eigenlijk een vastgestelde voorwaarde waarbij de tabel niet meer valide is en deze dus bijgwerkt dient te worden. Het lijkt mij ook niet praktisch, al heb ik het in het verleden wel eens toegepast.

Ik vind zelf een genormaliseerde database als uitgangspunt wel prettig. Ik kan me voostellen dat er situaties zijn waarin het onnodig complex wordt, maar zo complex is het nu in ieder geval ook weer niet. En een boomstructuur als een boomstructuur in een tabel opslaan, het is geen excel.

@Douweegbertje. Ja ok, sp's. Maar wat werken ze dan bij. Ze werken vaste niet genormaliseerde tabellen bij?

Overigens slaat mssql zelf dure acties wel op als er geheugen beschikbaar is, maar hoe dat precies in z'n werk gaat is mij niet duidelijk.

De indexed views lijken wel waar ik naar op zoek was, dank.

[ Voor 17% gewijzigd door arnem_ op 25-03-2016 00:05 ]

[Face_-_LeSS]

Veel hangt af van jouw definitie van 'een beetje toenemen'. Waar hebben we het over? dDuizenden, miljoenen of miljarden records?

Hoe waarschijnlijk is het dat de groei van data een probleem gaat vormen in de toekomst? Heb je dat gemeten/berekend? Vaak is de snelste en goedkoopste oplossing meer hardware inzetten dus als je die optie hebt en daarmee het probleem kunt uitstellen dan zou ik daar voor gaan. Het is zonde om vroegtijdig te gaan optimaliseren.

Ik mis in je lijstje de optie om aan de applicatie kant te cachen. Heb je daar de mogelijkheid van onderzocht?
Ook mis ik de optie cloud.

Als het probleem toch op database niveau opgelost moeten worden dan is indexed views de meest voor de hand liggende oplossing. Mits, zoals Kwistnix al aangaf, het vooral gaat om veel joins / aggregates.
Indexed views worden real-time bijgewerkt als de tabel wordt bijgewerkt dus bij mutaties betaal je die prijs. Als dit een probleem wordt dan kun je de indexed view later eventueel vervangen door een gedenormaliseerde tabel, met de zelfde structuur als de view, die je batchgewijs bijgewerkt. Let daarbij wel op dat de tabel tijdens het bijwerken gedeeltelijk of volledig gelockt kan zijn en dat kan voor timeouts zorgen bij het gelijktijdig bevragen. Kies dus een goed window of gebruik partition swapping (Enterprise edition only).

Het probleem dat Douweegbertje schetst m.b.t. het vergeten van de views is een zwak argument want via schema binding kun je dat eenvoudig voorkomen.

[sig69]

Het gaat nu als de brandweer, dus who cares? Goed natuurlijk dat je het alvast in achterhoofd hebt natuurlijk, en wat vooronderzoek kan ook geen kwaad, maar ik zou er niet veel tijd in steken.
Verder hangt het af van je tabelstructuur, requirements, aantal records etc wat wel of niet een oplossing zou kunnen zijn dus daar kan ik niks over zeggen.

Ik ben ook geen fan van views overigens. Mijn voorganger wel.. Wat krijg je dan? Stored procedures die drie stored procedures aanroepen met een query op een view die vier andere views gebruikt die op hun beurt weer... Je snapt 'm wel denk ik.

[ Voor 26% gewijzigd door sig69 op 25-03-2016 01:04 ]

[Gomez12]

Als je het voor je website zou willen doen, wat voor informatie zou je dan eigenlijk op die website willen hebben?

Want wij hebben bijv een periodieke batch draaien die vanuit ons rdbms (waar het onderhoud op gebeurt op een relationele manier) json documenten maakt die 95% pagina's voor onze website zijn en die dan een documentendatabase ingaan, de website haalt dan heel vaak maar 1 document voor 1 pagina uit de documentendatabase.

Wat ik hiermee probeer aan te geven is dat je je moet afvragen of je op je website wel rdbms info wilt hebben of dat je het inherent niet relationeel wilt gebruiken maar meer richting een document.

Want als je allerlei trucs in je rdbms moet bouwen en dan alsnog xxx query's moet afvuren op je rdbms ben je dan wel handig bezig?

Maar als allereerste geld : Laat eerst de data maar komen zodat het er echt naar uitziet dat het problematisch gaat worden. Want als je wilt gaan caches (wat je daadwerkelijk bedoelt te gaan doen) dan krijg je een hele zooi complexiteit erbij en dat wil je echt pas hebben als het noodzakelijk is, je wilt geen complexiteit hebben als het niet nodig is.

[arnem_]

Het gaat om data voor grafiekjes van gebruikers. Een gebruiker kan in verschillende categorieën vallen. Als ik het zo bereken, afhankelijk van wat de gebruiker opvraagt, kunnen er wel paar honderd duizend verschillende grafiekjes gemaakt worden. Het klaarzetten van de data voor de grafiekjes is dus geen optie.

Ik vind een paar views wel prettig werken en werk niet zo graag met stored procedures omdat code bijhouden in databases wat mij betreft niet zo inzichtelijk is. Alles gaat vanuit sql code geplaatst in services.

Maar ik kom er zo wel uit, dank.

[Merethil]

Is er niet een mogelijkheid om aan de site-kant te cachen? Dat je een 24-uurs cache neerzet voor elke klant die 's nachts refreshed?
Op die manier heb je op zich weinig database-interactie, heb je wel de "nieuwste 24 uur" en hebben klanten een snel overzicht van hun grafieken.
Zou je altijd nog een "recalculate"-button kunnen hebben die de cache refreshed voor de klant wanneer deze het zelf wil, om de nieuwste data te zien.