MSSQL og optimalisering av store datamenger

[HDSoftware]

Heisan folkens

Vi sitter og designer en del av databasen vår som skal gruppere poster, det være seg kunder, prosjekter, dokumenter etc. etc.

 

Så har vi kommet frem til at vi kan putte alle disse kategori inndelingene inn i en tabell, men vi ser at denne tabellen vil øke i omfang relativt kjappt, i hvertfall i vår målestokk. Man kan kansje si at en av våre største kunder vil ha en database økning på den tabbelen med, la oss si 10 til 15 tusen poster daglig. Record størrelsen er ikke så veldig stor da den kun vil inneholde tre GUID felter, altså 3 strenger på 36 karakterer.

 

Det jeg lurer på er hvor effektiv dette vil være for denne tabellen vil være ganske sentral i database modellen, både for søk og nyregistreringer.

 

Jeg misstenker jo SQL for å være et veldig godt database værktøy og at en slik server er "flink" til å gjøre slike ting. Jeg vet ikke så mye om hvordan en SQL server virker, men jeg har en følelse av at fordelingen av dataene i forskjellige tabeller har liten eller ingen betydning for hvor effektiv den er. Noen tanker....

Endret 29. september 2008 av HDSoftware

[terjeelde]

Jeg misstenker jo SQL for å være et veldig godt database værktøy og at en slik server er "flink" til å gjøre slike ting. Jeg vet ikke så mye om hvordan en SQL server virker, men jeg har en følelse av at fordelingen av dataene i forskjellige tabeller har liten eller ingen betydning for hvor effektiv den er. Noen tanker....

 

SQL er egentlig bare ett standard språk for databasespørringer. Hvordan det fungerer kommer nok til å avhenge litt av hvilke spesifikke databaseserver du velger.

 

Det er også med på å avgjøre hvor bra det går å blande data på ulikt hvis i forskjellige tabeller etc. Noen databaseservere er flinke til å gjøre spørringer på smarte måter, andre er ikke fult så flinke.

 

Føler spørsmålet var litt åpent, så lettere å si noe fornuftig hvis du kan være litt mer konkret rundt hva du vil, og gjerne nevne hvilke server du har i tankene.

[HDSoftware]

Jeg bruker MSSQL2005 (express for single users)

 

Det var egentlig en generell situasjon jeg nevnte, men jeg kan godt ta et eksempel:

Sett at man har tre tabeller, kunder, prosjekter og dokumenter. Alle disse kan man kategorisere ved. f.eks. å lage kundegrupper, dokument typer og prosjekt størrelser. Dette er tre forskjellige kategoriseringer som man kommer på. Dette kunen man løst ved å ha en tabell for Kundegrupper, en for dok typer og en for prosjekt størrelser. Jeg føler at dette blir relativt statisk og utvidelse kan medføre problemstillinger man ikke ser med en gang. Jeg ser for meg å legge alt dette inn i et par tabeller som bygger opp en tre struktur. Dermed får jeg EN tabell som relaterer til seg selv ved å ha en PARENT RECORD ID. Så må jeg ha en tilleggstabell som er selve koblingen mellom tre strukturen og entiteten. Disse tabellene må se noe slik ut:

 

RelTree:

EntityID

RelLevelID

ParentLevelID

 

RelConnect:

EntityID

RelLevelID

EntityID

 

Som du ser så vil RelTree tabellen bli relativt liten da denne blir så og si statisk, men RelConnect vil øke dramatisk da denne vil inneholde menger med records. Som nevnt i innledningen så kan denne øke med 10 til 15 tusen poster daglig.

 

Det som er diskusjonen vi har her er om dette er effektivt eller ikke. Se for deg 100 brukere som hele tiden skal gjøre spørringer og legge til poster i denne tabellen.

 

Alternativet er jo å ha dette definert i egne tabeller, slik at en spørring etter kundegrupper ikke berører spørringer i Varegrupper eller dokument typer.

 

Jeg argumenterte imot dette fordi jeg misstenker at en SQL server egentlig ikke har tabeller, men at en tabell egentlig er et resultat av en spørring. Og hvis så er tilfelle så vil jo det bety at det ikke spiller noen rolle om dataene ligger i en tabell eller er fordelt mellom mange.

 

Det å ha dataene i en og samme tabell vil jo medføre at den blir dynamisk og tillater nye grupper veldig enkelt. Å definere egne tabeller medfører jo endring i database samt endring i program.

 

Håper jeg var litt klarere.....

[terjeelde]

Det som er diskusjonen vi har her er om dette er effektivt eller ikke. Se for deg 100 brukere som hele tiden skal gjøre spørringer og legge til poster i denne tabellen.

 

Alternativet er jo å ha dette definert i egne tabeller, slik at en spørring etter kundegrupper ikke berører spørringer i Varegrupper eller dokument typer.

 

Jeg argumenterte imot dette fordi jeg misstenker at en SQL server egentlig ikke har tabeller, men at en tabell egentlig er et resultat av en spørring. Og hvis så er tilfelle så vil jo det bety at det ikke spiller noen rolle om dataene ligger i en tabell eller er fordelt mellom mange.

 

Det å ha dataene i en og samme tabell vil jo medføre at den blir dynamisk og tillater nye grupper veldig enkelt. Å definere egne tabeller medfører jo endring i database samt endring i program.

 

Håper jeg var litt klarere.....

 

Mye klarere.

 

Lyder fornuftig med en tabell.

 

Vil bare tilføye at dersom det er veldig store datamengder det er snakk om, og du har en type data som brukes veldig ofte, og en type data som brukes sjeldent, så er det kanskje lurt å skille dem.

 

Årsaken er blant annet indexer. Data som brukes ofte vil dra nytte av at indexene ligger mest mulig i minne, mens hvis du blander med data som brukes sjeldent, så risikerer du at du ender med å bruke masse minne på data du ikke trenger å ha i minne.

[roac]

Strengt og kort foralt: Lær deg SQL Server først.

 

For det første, GUID skal ikke være en streng på 36 tegn, men datatypen uniqueidentifer som tar 16 bytes. Videre skal skal GUID ikke brukes til identifisering av rader unntatt i de tilfeller man IKKE kan bruke en int av en eller annen lengde. Begge delene har med lengde på nøkler/felter å gjøre. Det som også er lett å glemme i denne sammenhengen er at primærnøkkelen også lagres i sin fulle lengde i fremmednøkler og indekser. Således kan man risikere at en nøkkel som her beskrives som 36 bytes i praksis tar opp flere hundre bytes i databasen, for hver eneste rad. Et konkret eksempel: En streng på 36 tegn er fremmednøkkel. Den tilhørende primærnøkkelen er med i ytterligere en indeks. Vi har da:

 

36 byte i fremmednøkkel

36 byte i primærnøkkel

36 byte i data (som primærnøkkelen peker på)

36 byte i indeks

 

Totalt: 144 bytes

 

Mot f eks:

 

4 byte i fremmednøkkel

4 byte i primærnøkkel

4 byte i data (som primærnøkkelen peker på)

4 byte i indeks

 

Totalt: 16 bytes

 

 

Spart: 128 bytes.

 

For 10000 rader: 1,2MB (per dag)

 

 

Kort fortalt: Dette kan være forskjellen på om serveren som skal hoste dette får minneproblemer eller ikke, f eks i forbindelse med store joins, sorteringer, reindeksering og så videre.

 

Min dom vil være: Tilbake til tegnebrettet. Sikkert litt strengt dog...

[Wabby]

I tillegg så er det veldig viktig hvordan dere skriver spørringene. Finnes mange operatorer som kan kjøre en SQL server i dass.

 

På spørringer som brukes ofte så prøv å unngå negative operatorer og spørringer som har like med % i starten.

Videre så er det stor kostverdi dersom du slår sammen kolonner i en spørring.

 

Anta firstname, lastname. også vil du ha dem ut som firstname + ' ' + lastname. Dette koster... Om du har tenkt slikt, så foreslår jeg en trigger som på INSERT som da fyller ut en egen kolonne for slike formål som heter CustomerFullName for eksempel

 

Det er så mangt her at det er vanskelig å si noe om performance og hva som er lurt for men skjønner hele tankegangen.

 

På jobb f.eks så har vi en tabell som har akuratt nå.... 11788519 rader med over 30 kolonner, den er rask som juling og er et viktig knyttepunkt for alt som er rundt.

 

Annen ting, har du muligheten for å normalisere, gjør det.

[kaffenils]

Anta firstname, lastname. også vil du ha dem ut som firstname + ' ' + lastname. Dette koster... Om du har tenkt slikt, så foreslår jeg en trigger som på INSERT som da fyller ut en egen kolonne for slike formål som heter CustomerFullName for eksempel

Eller enda bedre, en kalkulert kolonne. Triggere medfører masse ekstra overhead.

 

Annen ting, har du muligheten for å normalisere, gjør det.

 

For all del, jeg er helt enig i at du tjener mye på å normalisere, men det er ikke slik at alle type spørringer går raskere hvis en database er normalisert.

 

Enkelte av rapportene vi har bruker denormaliserte data, som genereres basert på en normalisert modell.

 

 

Nå kommer snart cyclo og moderere vekk offtopic-innlegget mitt

[Wabby]

Dersom du tenker datavarehus så ja. De er jo ikke normalisert på samme måte. Men da kommer vi litt ut på et annet tema

 

Ja, de kan jo kalkuleres ved Insert istedenfor. Men triggere skaper ikke så mye overhead at det gjør noe. Uansett så bør man lære seg triggere, for de kan gjøre hverdagen lettere

[Manfred]

Vi har litt både-og. Noen tabeller og strukturer er normalisert, andre ikke. Det har rett og slett vært avgjørelser på ytelse vs. datamengde og slikt. Så normalisering er bra i teorien, men det er ikke nødvendigvis det kjappeste og mest effektive i alle sammenhenger.

 

Men siden en tråd ikke får lov til å utvikle seg her i forumet, så sitter vi vel bare og venter på at disse innleggens skal slettes, siden de ikke er nøyaktig svar på spørsmålet.

[Frank2004]

Sett at man har tre tabeller, kunder, prosjekter og dokumenter.

[...]

 

Så må jeg ha en tilleggstabell som er selve koblingen mellom tre strukturen og entiteten. Disse tabellene må se noe slik ut:

[...]

 

RelConnect:

EntityID

RelLevelID

EntityID

Når det gjelder dette designet; hvor bra virker det å ha nøkler som kan referere til en av flere mulige tabeller?

 

Jeg pleier legge inn en egen kolonne for hver tabell det kan kobles opp mot, i.e. kunde_id, prosjekt_id og dokument_id, slik at jeg får lagt på FK constraints, men er kanskje ikke optimalt det heller..?

[roac]

Vi har litt både-og. Noen tabeller og strukturer er normalisert, andre ikke. Det har rett og slett vært avgjørelser på ytelse vs. datamengde og slikt. Så normalisering er bra i teorien, men det er ikke nødvendigvis det kjappeste og mest effektive i alle sammenhenger.

 

Men siden en tråd ikke får lov til å utvikle seg her i forumet, så sitter vi vel bare og venter på at disse innleggens skal slettes, siden de ikke er nøyaktig svar på spørsmålet.

Normalisering er i bra i teorien, for å forenkle innsettinger/oppdateringer og ivareta integritet i forbindelse med dette. Utover dette vil jeg faktisk påstå at normalisering som oftest forverrer ytelsen, da det er flere objekter involvert, som tabeller og indekser. Dette gjør at flere objekter må holdes oppdatert (som gir mer I/O), og som gjør at flere objekter brukes ved uthenting av data (som gir mer I/O). I de aller fleste tilfeller finner jeg at det er I/O som er flaskehalsen med tanke på ytelse, og ergo fører normalisering i slike tilfeller ofte til forverring av ytelsen.

 

Unntakene til dette er når det er et relativt begrenset subsett med data med mange referanser til hver av forekomstene. F eks vildet kunne forsvares å trekke ut poststed med tilhørende postnummer av ytelsesmessige hensyn.

 

Edit: Det som er så forferdelig synd er at det ikke virker som om utdanningsinstitusjoner lærer elevene denne måten å tenke på...

Endret 1. oktober 2008 av roac

[Manfred]

Vi ble jo lært opp til at alt som kan normaliseres skal normaliseres uansett. Da går du kjapt på en smell når du skal lage litt ytelseskritiske systemer senere