12. oktober 2008 - 10:51Der er
17 kommentarer og 1 løsning
Erfaringer med tabeller til dynamiske data
Jeg har en applikation hvor brugerne i princippet kan oprette hvad der svarer til deres egne tabeller og derefter fylde en masse data i dem. Det er pt. implementeret med én stor tabel med kolonnerne "navn", "værdi" og så en række for hver gang der forekommer data. Tabellen har nu ikke så få milliarder rækker, hvilket giver performance-problemer, specielt ved søgninger.
Derfor pønser jeg på at oprette en FAKTISK tabel i databasen for hver gang med de dynamisk oprettede kolonner og så fylde rækker i den for hver gang. Det vil ende med 4.000 tabeller, men kun max 100.000 rækker i hver tabel.
Nu vil jeg høre fra nogen der her ERFARING med at oprette tabeller dynamisk. Hvad skal man være opmærksom på? Nogle problemer? Gik det helt fint? Jeg er interesseret i at høre fra ALLE der har erfaring med det, også selvom det gik uden problemer.
Det vil næppe løse særlig meget en DB med 4000 tbl. den for helt sikkert også performence problemer.
Har du tænkt på om du kan lave nogle indekser, evt. et felt med en type Navn, værdi, type
Så kan du jo sikkert nøjes med at select på type og så ende med nogle mindre udtræk og derved bedre hastighed
Og så skal du lige huske når du har milliarder af rækker, så skal du gange alt hvad der tager bare en lille bitte smule tid med en milliard = det er lig med lang tid. Mange data tage bare noget tid at behandle :-)
Jeg glemte måske at understrege, at de kald og søgninger der laves, skal 99% af tiden kun ske i én af de dynamiske tabeller. Det vil sige man kun vil behøve at søge i én af de 4.000 tabeller ad gangen.
Men som sagt: Jeg vil gerne høre fra folk der har erfaring med dynamisk oprettede tabeller.
Prøv at tænk lidt over det du vil, læs evt. i spec. til den db du vil køre det på, jeg tvivler på at der er ret mange db'er der kører hurtigere af at du flytter data fra en tbl. til 4.000 tbl........
Så er det lige gyldigt og du kun kigger i en tbl adgangen, samme efekt får du med et type/tblnr felt og et index, og sandsynligvis en bedre performence end hvis du "byder en db at køre med 4.000 tbl" den er lavet til den anden måde, en tbl. med mange data og nogle index.
PS. min første kommentar var ikke et forsøg på at fornærme dig.
Martin, mange tak for dine input, jeg er ikke fornærmet. Jeg sætter pris på du sætter dig ind i min problemstilling, men jeg ville bare gerne have input fra nogen som har erfaring med dynamisk oprettelse af tabeller.
:-) Lige en bemærkning mere, jeg tror du vil komme til at bruge db'en på en måde den ikke er tiltænkt hvis du "fylder 4000 tabeller i.
Nu ved jeg ikke hvilken db du vil bruge men dem jeg kender har en datafil hvor den laver sine tabeller og hvis den skal ligge og holde styr på 4000, så tror jeg du får de samme hastighed problemmer som du har nu ! Jeg vil helt klart foreslå at du evt. laver en extra felt med en tblnr som så går fra 1-4000, så laver du index også : sselect * where tblnr=xxx det vil gi et rasultat der svarer til en tabel, og det vil være i "db'ens ånd" hvis du forstår.
Det bliver nok svært at finde nogen mer erfaring i dynamisk oprettelse af tabeller.
Det er nemlig ikke en god ide.
Vi er oppe i nogle data størrelser hvor det ikke er nok at kigge på det logiske database design, men er nødt til at kigge på det fysiske database design.
Nu ved vi ikke hvor store dine rækker er, men hvis vi siger gennemsnitligt 100 bytes og 4 milliarder rækker og et stort index på navn, så er vi oppe i 0.5-1 TB. Det kan de seriøse databaser sagtens håndtere. Men den skal altså designes rigtigt. Af en som har erfaring med den pågældende database.
At oprette tabeller dynamisk af applikationen er ikke en god løsning, fordi det vil forhindre enhver for for design af den fysiske database struktur (DBA kan ikke gøre en snus hvis din app futter rundt og opretter tabeller).
Det hjælper heller ikke at oprette et ekstra felt med et index. Hvis man skal slå op på navn, så bidrager det ikke med noget som helst.
Data skal organiseres. Nu ved vi jo ikke hvilken database og hvilken edition det er, så følgende bliver lidt generelt.
Undersøg hvilke partitionerings muligheder der er i den database i bruger. Hvis der ikke er nogle muligheder, så overvej at lade din DBA lave noget manuelt.
Og så skal tingene organiseres. Data og index skal spredes på diske, således at der er jævn IO load. Data og deres tilhørende index skal placeres på forskellige diske. Indexet skal optimeres (hvis f.eks. altid det er lookup med navn= og aldrig noget < > LIKE etc. så kan et hash index være bedre end et tree index).
Forskellige databaser har forskellige muligheder for partitionering, forskellige måder at placere ting (data eller index) på fysiske placering or forskellige muligeher for indexes og forskellige muligheder for memory allokering.
Men selvom du fortalte hvilken database det var, så var jeg næppe den rette til at guide jer igennem.
Men pointen er at dit problem skal ikke løses af software udvikleren via ændringer i applikationen men ved at DBA'en for den database optimeret.
Arne_v, jeg plejer at bukke mig i støvet for næsten hvad som helst du siger, men prøv lige at høre en gang:
Hvis der er 10 'objekter' i en applikation, anvender man typisk 10 tabeller, eller lidt mere hvis der er mange-til-mange forbindelser mellem dem. Man lægger ikke alle data i én stor generisk tabel.
Forestil dig en applikation med 10 gange så mange forskellige typer objekter, det vil sige over 100 tabeller - den er ikke anderledes opbygget, men er bare et større system og omfatter derfor flere tabeller. Den vil man heller ikke lave som en stor tabel.
Forestil dig så en endnu større applikation. Tænk komplette virksomhedssystemer for hele lande, militæret eller Mærsk. Så er vi oppe på 4.000 tabeller. Hvorfor mener I, at man ved så store systemer, børlægge alle data i én stor tabel, i stedet for at opdele data logisk i de 4.000 tabeller hvor de hører hjemme?
Jeg er i princippet i sidstnævnte situation og ved selvfølgelig det er umuligt for en databaseadministrator eller udvikler at styre 4.000 tabeller, men det gøres praktisk muligt ved hjælp af applikationen. Det kan godt være løsningen er at dele databasen ud på 10 forskellige database-servere, men jeg nægter at tro den rigtige løsning er at lægge alle data i én tabel.
selvfølgelig skal du ikke lægge ALT i en tbl. men 4000 tbl er stadig temmelig mange og som arne skriver, så er det i høj grad dine hardware der skal afgøre tingene når du snakker så store datamænger.
Spørg hellere en expert for meget, det ville jeg gøre i dit sted, håber du kommer frem til den bedste løsning :-)
Jeg skylder i øvrigt at fortælle jeg anvender MS SQL Server 2005, men ville ikke inkludere det i spørgsmålet, da det jo er et generelt database-spørgsmål, hvor svaret formentlig gælder for de fleste større database-systemer.
Normalt (sådan er en helt standard ORM løsning) vil man have X klasser - X tabeller. Dem designer man når applikationen udvikles. Ja da ja da - vi kender alle den model.
Du har et problem med at din data struktur er dynamisk og du ikke ved hvilke felter du vil have på det tidspunkt hvor applikation og database designes.
Det er ikke normal situationen. Men du er ikke den første som har haft det problem.
Der er en rimelig meget brugt løsning på det problem som du bruger idag: A) Man har en tabel med et navn og et værdi felt.
Du omtaler ikke hvorvidt du har overvejet den næstmest brugte løsning: B) Have et stor VARCHAR eller CLOB felt hvor du gemmer noget struktueret XML.
(det er sådan set også uinteressant for dit konkrete spørgsmål)
Den løsning du overvejer er: C) Lade din applikation oprette tabeller efter behov når den runtime ved hvilke felter der er brug for.
På trods af at det ligner en helt standard ORM løsning, så er det ikke en sådan.
Jeg ser ikke nogen grund til udfra det logiske database design at det skulle forbedre performance nævneværdigt. Ihvertfald ikke med simple lookups. Hvis du har bruge for at "joine", så ville det måske hjælpe.
Koden bliver noget rod. Enten skal du dynamiske generere kode eller så bliver det noget super slemt dynamisk SQL fordi tabel navne og felt navne kan ikke parameteriseres.
Men det største problem jeg ser er med det fysiske database design.
Hvis du laver en database i 5-25 GB størrelsen, så er der ikke noget problem. Du får DBA til at oprette en database med 1 data og 1 log. Du opretter det logiske database design i den lige ud af landevejen. Og performance er OK (forudsat at du laver et fornuftigt logisk database design og at serveren er sat fornuftigt op).
Men nu snakker vi 0.5-1 TB størrelsen. Og så er vi ovre i en anden boldgade. Samme metode anvendt på noget i den størrelsesorden vil ikke performe godt. Der skal en erfaren DBA til at lave det design.
Hvis det var en standard ORM løsning fik DBA'en dit logiske database design (medmindre vedkommende insisterede på at være med til at lave det ...) og lavede et fysisk design for det.
Jeg tror at du vil kunne forbedre performance markant ved at få dit nuværende design fintunet ved brug af de muligheder der findes i SQLServer.
Hvis din applikation begynder at oprette tabeller, så vil det være umuligt at tune det design. Jeg antager at du ikke har tænkt dig at skrive kode som monitorerer disk aktiviteten og udfra den fordeler tabeller og index på de opptimale devices.
Jeg kan godt se pointen, særligt i forhold til at tune designet og den sql-kode som anvender databasen ville bestå af en frygtelig masse streng-konkatineringer, slet ikke pænt. Som du tilsyneladende har regnet ud, er jeg softwareudvikler, ikke DBA - så lad mig lige skitsere performance-spørgsmålet som udvikler.
Forestil jer to objekter:
1. Unit - en samling af data som er oprettet af en bruger på et bestemt tidspunkt 2. Content - en forekomst for hvert stykke data der er i en unit, typisk mellem 10 og 100.
Nu vil jeg finde alle Unit's af typen Personer hvor den Content som hedder 'FirstName' indeholder 'arne'
SQL, hvis man forestillede sig der var tale om dynamisk oprettet tabel:
SELECT UnitID, UnitDateTime, FirstName, LastName, Street, PostalCode FROM Unit_Personer WHERE FirstName LIKE '%arne%'
SQL i tabel med navn-værdi opbygning:
SELECT UnitID, UnitDateTime FROM Unit INNER JOIN Content ON Unit.UnitID = Content.Unit_ID WHERE (Content.Key = N'FirstName') AND (Content.Value LIKE '%arne%')
Hvis man vil have alle værdier med ud, skal man lave en inner select (eller noget tilsvarende).
Vil man søge på mere end én værdi er det endnu et opslag for hver gang.
Det er i den situation jeg forestiller mig den dynamisk oprettede tabel er meget hurtigere og bliver forholdsvis hurtigere jo flere data der er tale om. Tager jeg fejl?
(der ville i øvrigt være indexes på den dynamisk oprettede tabel, da brugerne skal oprette dem på de kolonner de vil kunne søge på)
Du skal ganske vist lave mere logisk set men til gengæld vil du ramme de samme index igen og igen, hvilket kan give flere cache hits og færre IO's. Jeg tror stadig på navn-værdi og en DBA optimering af den fysiske database struktur. Men du kan jo prøve og måle på det.
Ja, men jeg tror også lige jeg vil spørge lidt mere omkring mig. Jeg kan få mine egne dyrtkøbte erfaringer, men vil jo gerne høre andres erfaringer først.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
