Avatar billede nielsbrinch Nybegynder
12. oktober 2008 - 10:51 Der 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.

Mange tak!
Avatar billede martinlind Nybegynder
12. oktober 2008 - 11:04 #1
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 :-)
Avatar billede nielsbrinch Nybegynder
12. oktober 2008 - 11:18 #2
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.
Avatar billede martinlind Nybegynder
12. oktober 2008 - 11:41 #3
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.
Avatar billede martinlind Nybegynder
12. oktober 2008 - 11:43 #4
Pps. hvilken db bruger du ? access er nok ikke så velegnet :-)
Avatar billede nielsbrinch Nybegynder
12. oktober 2008 - 14:32 #5
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.
Avatar billede martinlind Nybegynder
12. oktober 2008 - 17:11 #6
:-)
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.
Avatar billede arne_v Ekspert
12. oktober 2008 - 20:32 #7
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.
Avatar billede nielsbrinch Nybegynder
13. oktober 2008 - 11:59 #8
Tak for jeres kommentarer.

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.
Avatar billede martinlind Nybegynder
13. oktober 2008 - 12:42 #9
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 :-)
Avatar billede nielsbrinch Nybegynder
13. oktober 2008 - 13:41 #10
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.
Avatar billede martinlind Nybegynder
13. oktober 2008 - 13:50 #11
er bare nysgerig.... hvordan bærer du dig ad med at få så mange data, er det vejrinfo du gemmer eller aktiekurser ??
Avatar billede nielsbrinch Nybegynder
13. oktober 2008 - 14:01 #12
Utroligt nok er der faktisk tale om indtastede data.
Avatar billede martinlind Nybegynder
13. oktober 2008 - 14:08 #13
Kinesiske madopskrifter velsagtens :-) ( for der findes da ikke så mange danskere )
Avatar billede arne_v Ekspert
13. oktober 2008 - 21:52 #14
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.
Avatar billede nielsbrinch Nybegynder
14. oktober 2008 - 09:21 #15
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å)
Avatar billede arne_v Ekspert
15. oktober 2008 - 04:04 #16
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.
Avatar billede nielsbrinch Nybegynder
15. oktober 2008 - 09:10 #17
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.

Mange tak for din tid. Læg svar.
Avatar billede arne_v Ekspert
15. oktober 2008 - 21:43 #18
svar
Avatar billede Ny bruger Nybegynder

Din løsning...

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.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester