05. september 2012 - 10:30Der er
38 kommentarer og 1 løsning
CVS-fil til SQL Server - best practice
Hej,
Jeg har nogle data som jeg får leveret i en CSV-fil hver halve time. De data læser jeg ind vi noget System.IO, og skal så have dem videre ind i mere strukturerede tabeller i en SQL Server.
Hvad er best practice, her? Man kan enten vælge at at "rengøre", og slette tabellerne, umiddelbart før indlæsningen af de nye data, og så fylde op påny - men det virker ikke helt som den optimale løsning. Det vil jo give problemer i forhold til relaterede tabeller.
Der vil være både samme data, nye data og ændringer til eksisterende data i CSV-filen. Men vil det performance-mæssigt ikke tage længere tid at tjekke op på ændringer, køre en update på disse, og herefter en insert på nye og slette dem som ikke længere er gældende.
Det er ikke noget jeg har prøvet at fedte med, men jeg tænker du måske kunne generere en MD5-kode til hver række ud fra data, som gemmes sammen med rækken.
Når der så skal opdateres, kan du nøjes med at udlæse MD5-koderne og rækkens nøgle og sammenligne med den MD5-kode du beregner for de nye data. De rækker hvor MD5-koden ikke matcher, opdaterer du.
En anden tanke kunne være, at oprette en ny tabel hvori du smider data og til sidst bytter den nye tabel med den gamle (omdøber tabellerne)... Jeg ved ikke om dette er muligt i dit setup, så det er mest brainstorming pt. :-)
Jeg synes din løsning nr. 1 lyder interessant. Kan man MD5-kode en hel række på én gang på en nem måde uden det koster perfomance? Det kan jo bare være et enkelt ord i en kolonne der er ændret, hvilket så afgør om der skal opdateres.
Der vil være ca. 20.000+ rækker der skal indlæses hver gang.
Da det er en CSV tekstfil, burde det være et spørgsmål om at tage hver linje og kværne igennem en MD5.
Umiddelbart vil du få færre databaseoperationer (men mere CPU-forbrug) med MD5-metoden, men den er mere kompleks at kode end en simpel "insert-swap-drop", som jeg foreslog i #2.
Desuden genererer MD5-metoden ikke garanteret unikke checksum-koder, men du kan muligvis leve med den (antageligt lille) risiko for sammenfald, der måtte være. Det er jo kun pr. række du skal være bekymret for sammenfald, da du identificerer rækkerne på nogle andre nøgleværdier end MD5 checksum-koden.
Det er oplagt at bruge databasens værktøjer, som runesoft foreslår, så det ville jeg også gøre, hvis det var mig (jeg vidste bare ikke den feature eksisterede :-))
Ok tak. MERGE er godt nok helt nyt for mig, så jeg vil i første omgang give mig i kast med MD5-teknikken. At encrypte hver linie lyder som en løsning. Vil jeg ikke ikke uden problemer kunne bruge denne funktion til dette formål:
Det er meningen at der skal tjekkes op på CSV-filen hver halve time via et cron-job på serveren. Dvs. der skal indlæses 20.000+ rækker og indsættes i databasen hvis der er ændringer i dataene, samtidig med at der er brugere der anvender siden. Så målet er at sikre hurtigst mulig logistik med mindst mulig CPU-forbrug og databaseoperationer.
janus >> det var egentlig også dét der var tanken med det jeg foreslog...
Jeg forestillede mig dog, at det blot var et id (hvis det er dét der danner nøgle for en række) og så den gemte hash-værdi for rækken der skulle læses og så foretages resten i hukommelsen indtil man ved hvad der skal oprettes/opdateres/slettes (hvorefter man så gør hvad der skal gøres i databasen)... Jeg vil mene det skalerer bedre end at udlæse hele datasættet, men i princippet det samme som du siger.
Ja, groft sagt. Jeg ville måske selv undlade at læse en ekstra gang ifm. oprydningen, men bare starte forfra i det der allerede er læst... Hvis det er et problem med en datareader, ville jeg nok bruge en anden datastruktur til midlertidig lager for det udlæste, f.eks. Dictionary og så tjekke for eksistens med ContainsKey.
Hvis man var lidt snedig (måske), kunne man slette de elementer fra Dictionary, der blev opdateret ifm. med første gennemløb af de udlæste, for så ender du med en liste af dem der skal slettes. Så burde det være trivielt at slette disse fra databasen... :-)
Hej igen, jeg har arbejdet videre på projektet gennem de sidste par uger, og det virker super godt med dictionary-metoden :-)
Eksempelvis tager allerførste INSERT på omkring 8.000 poster ca. 60 sekunder. Næste tjek, hvor eksisterende indhold hentes ud i hukommelsen og der tjekkes på ContainsKey og updates hvor der er evt. ændringer i hash-værdier tager ca. 4 sekunder. Det er nice performance :-D
Opgaven kører vel som en baggrundsopgave (noget med en timer, eller en ekstern service) og dermed vil jeg ikke mene, at den styres af disse parametre. Desuden er der vel ikke nogen grund til at justere på disse, da dine optimeringer ser ud til at have bragt tidsforbruget væsentlig ned...?
Hej softspot. De 8000 rækker er kun for én tabel, derefter kører en ny funktion som smider 30000 rækker ind i næste tabel osv. Jeg har det til at køre via min localhost mod en SQL server ved mit hostingfirma, men når jeg lægger det samme testscript derop, er det som om det "hænger" og til sidst går den i "siden kan ikke vises"-mode. Så det kunne være at man kunne stille på nogle timeout-parametre eller lignende.
Hvis du kører det hele som én transaktion, er det muligt, at du løber i en timeout dér. I så fald skal timeout nok styres på selve transaktionen, alternativt command-objektet, hvis du da overhovedet kan få lov til dette på en shared database server (hvilket jeg antager du kører på... eller hvad?).
For at undgå request-timeout kan du evt. sætte en timer op i din global.asax, som trigger i det ønskede interval og dermed køre opdateringen i baggrunden. Det er dog stadig muligt, at du løber ind i databasetimeout eller en decideret lockout fra databasen, fordi du bruger for mange resurser (hvis databasen er shared).
Jeg ville faktisk have forventet, at det kørte bedre, når det kom over på din ISP's webserver, da webserveren og SQL Serveren formegentlig ligger på samme netværk. Du er nok nød til at finde ud af hvad der helt konkret går galt (få fat i fejlinformationerne, så du har noget at forholde dig til).
Jeg kører ikke transaction, jeg kører Try.. Catch og herimellem laver jeg executenonquery() med min INSERT for at fange eventuelle fejl i de data jeg sætter ind, ellers vil min transaction vel bare gå i stå ved første fejl?
Du har ret i at det kører meget hurtigere når det eksekveres på serveren sammen med databasen. Det virker også fint på serverenmed tekstfiler på færre linjer, ligesom det også virker på min localhost.
Det mærkelige er, at når det kører på serveren bliver den bare ved med at indsætte i den tabel med de ca. 30000 poster selvom den burde stoppe ved de 27000 som rent faktisk skal sættes ind. Pludselig er den oppe 100000, og siden kommer med fejlmeddelelsen "er ikke tilgængelig lige nu. Den kan enten være overbelastet eller under vedligeholdelse." men jeg skal faktisk ind i Plesk'en på serveren og genstarte IIS'en før den stopper med at indsætte i databasen. Det er som om den kører helt i selvsving, starter forfra eller noget i den stil.. Meget mystisk
Jeg kan nemlig godt gennemføre opdateringen/insert'en lokalt, så jeg kan vel egentlig ikke blive smidt ud af databasen? Og så er det bare underligt at selv samme kode kan ende i en uendelig løkke, så snart den køres fra serveren, hvor den egentlig har bedre forhold.
Jeg laver et par ekstra kald til DB i hver loop inden INSERT, for at hente et ID ud fra databasens primære tabel via en ExecuteScalar - Jeg har lidt på fornemmelsen at det er her det hele bliver belastet.
Jeg har smidt et eksempel her med den metode jeg er nået frem til, som henter tabellen ud i hukommelsen først, dernæst tekstfilen, sammenligner og insert'er.
Her skal det siges at tabellen som udgangspunkt er tom, så den vil hoppe til INSERT med det samme. Men det kan den åbenbart ikke helt tåle på serveren...
Jeg forstår ikke helt behovet for det opslag du laver i linje 83, da du jo allerede har læst disse data én gang. Du må kunne cache dine data, i et mere søgbart format. Jeg tror det er her dine problemer opstår, da du jo foretager dette opslag for hver eneste gyldige række i CSV-filen.
Umiddelbart ville jeg lægge en lille datastruktur i en liste (ArrayList eller typestærk liste, f.eks. List<ProdPrices>, hvor ProdPrices er en type/klasse med de relevante felter), som jeg kunne søge på vha. LINQ. Jeg er ikke inde i syntaksen for LINQ i VB.NET, men i C# kunne linje 83 og 86 samles til - og formuleres som flg.:
hvor checkCode er den kode du genererer til de nye data.
Wupti! Så har du lige sparet 30.000 kald til databasen... det må da tælle for noget :-)
Du kunne evt. overveje også at cache produkter og så slå op i cachen hver gang du skal se om et produkt findes. Her skal du så også kun cache de oplysninger som er relevante for koden (dvs. productid og productnum - versionering behøves ikke, da din forspørgsel ser ud til at filtrere på et statisk versionsid). Dette vil igen spare dig for en hel bunke kald til databasen.
Der kan givetvis laves andre optimeringer. Du kunne f.eks. undlade at instantiere en WebImportVarePriser for hver iteration, men blot instantiere én inden hovedløkken og så genbruge den i hver iteration. Alternativt helt undlade at bruge en klasse i denne sammehæng, da den virker lidt overflødig... Dette vil dog nok ikke betyde det helt stor ifht. de andre optimering jeg har ævlet om ovenfor :-)
Tusind tak for din feedback på koden. Det er super at få en vurdering af det, og hvad der kan optimeres. Jeg vil lige prøve at gennemgå dine forslag til optimering.
Men alt andet end lige er det lidt bemærkelsesværdigt at det fejler når det eksekveres fra serveren, end når det køres via localhost. Når jeg måler på det (via localhost) går det sløvt med inserts'ne lokalt, men trods alt gennemføres de med 30.000 poster.
Men det mest mærkelige er, at når det eksekveres på serveren, kører iteratioen løbsk og fortsætter med at indsætte, som om den får en besked om timeout og starter påny..? Hvorefter jeg er nødt til at genstarte IIS'en på serveren, for at databasen ikke bliver sprængt.
Du ignorerer evt. unhåndterede fejl fra parseren og databasen og kværner videre, hvilket kan være OK. Men da du jo har lidt problemer med at få det til at fungere på serveren, ville det måske være formålstjeneligt, at du loggede evt. fejl og måske endda stoppede eksekveringen af koden ifm. disse uhåndterede fejl - om ikke andet indtil du har fundet årsagen til at det fejler på serveren...
Er det nøjagtig samme database du kører op mod, når du tester koden lokalt, eller er det to forskellige databaser? Hvis det er to forskellige, så skal du sikre dig, at de er 100% ens i setup.
Databasen jeg tester imod er den samme, den ligger på serveren. Derfor går det også hurtigere når det køres fra serveren, hvorpå den så desværre fejler.
Men mht. til cache, mener du så at jeg skal prøve med en generic list i stedet for dictionary? Som jeg forstår det giver dictionary mig kun mulighed for Key/Value, men ved en list vil jeg så kunne hente alle informationer ud ved én SELECT, og så søge de informationer i den jeg skal bruge når jeg skal bruge dem. Så vil jeg spare de der 30.000 ekstra selects.
Det kan egentlig godt være, du med dictionary, også kan udføre LINQ-forespørgsler på både key og value, så måske du ikke behøver at ændre på din struktur, men blot måden der slåes op. I så fald skal du nok bare benytte de mere generiske atributter på iterator-objektet for at hente værdierne. Noget i stil med:
Tak for kode. Jeg er helt grøn mht. LINQ, så jeg valgte dit forslag med 2 typestærke hjælpe-lister over klasser med relevante felter, som jeg så kan slå op i.
Det gør at jeg ikke laver de 2 ekstra kald til databasen, således at:
pKey = db.ExecuteScalar(CommandType.Text, "SELECT ID FROM Shop_ProductPrices WHERE CheckCode = '" & .CheckCode & "'")
Bliver fundet ved:
For Each pris As WebImportVarePriserCheckListe In CheckList If pris.CheckCode = .CheckCode Then pKey = pris.ID End If Next
Og
fKey = db.ExecuteScalar(CommandType.Text, "SELECT ProductID FROM Shop_Products WHERE ProductNum = '" & .ProductNum & "' AND Versionering = " & VersionID & "")
Bliver fundet ved:
For Each vare As WebImportVareCheckListe In VareCheckList If (vare.ProductNum = .ProductNum) AndAlso (vare.VersionID = VersionID) Then fKey = vare.ProductID End If Next
Det giver det allerede et enormt boost. Ca. 30.000 linjer bliver sat ind første gang på ca. 200 sek. og efterfølgende gennemløb/tjek sker på ca. 56 sek.
Dette gøres fint lokalt mod databasen på serveren. Når jeg så kører det samme på serveren sætter den selv samme antal rækker ind, men siden der kører det "hænger"/bliver aldrig kørt færdig. - Som om den bare ender i en timeout eller går i selvsving.
Når jeg kører en count på antal rækker direkte i databasen, mens den sætter den ind, tæller den op, når op over 30.000 (selvom der ikke er så mange der skal sættes ind), står stille, og efter et minut tæller den ned til det korrekte antal på ca. 27.000. Hvis jeg prøver næste gennemløb, hvor der så ikke skal sættes noget ind, hænger den bare igen uden at blive færdig.
Hmmm.. det kan være jeg skal snakke med min hosting-udbyder igen, for der er da et eller andet der går galt der.
Jeg kan kun anbefale dig, at logge evt. fejl fra dine catch-sektioner og standse eksekveringen af koden ved første fejl. Du kan gætte fra nu af og til jul, hvis du ikke få vished for om og i så fald hvilke fejl der opstår. Det behøver ikke være så fancy, bare udskriv fejlen til browseren og stop udførslen med det samme.
Læg resultatet her i tråden, hvis du ikke selv kan gennemskue, hvad der går galt... det kan være der er andre der kan.
Det der, kort fortalt, sker er, at du definerer en lokal funktion som kaldes for hvert element med henblik på at afgøre om elementet skal medtages i optællingen eller ej (er Value-egenskaben på elementet = CheckCode, så skal den med ellers ikke). Andre funktioner har lignende funktion (men ikke alle), bla. Single, SingleOrDefault, First, FirstOrDefault.
Med denne viden kan du omformulere dit tjek for CheckCode:
'Find primærnøglen i Shop_ProductPrices pKey = db.ExecuteScalar(CommandType.Text, _ "SELECT ID FROM Shop_ProductPrices WHERE CheckCode = '" & .CheckCode & "'")
'Tjek om listen fra DB-tabellen indeholder tilsvarende produktnummer If KeyList.ContainsKey(pKey.ToString()) Then 'Der er ikke sket nogen ændring. 'Fjern elementet fra listen så vi ender up med en liste med 'overflødige som til sidst skal slettes fra DB KeyList.Remove(pKey.ToString()) tal += 1 Else ...
kan laves om til:
pKey = KeyList.FirstOrDefault(Function(i) i.Value = .CheckCode).Key If pKey <> "" Then KeyList.Remove(pKey) tal += 1 Else ...
FirstOrDefault returnerer det først fundne element med den givne checkcode eller returnerer en KeyValuePair-struktur med tomme værdier (derfor tjekket på pKey <> ""). Det er fordi KeyValuePair er en struktur (Type?) i VB og ikke en klasse, at der returneres en tom struktur. Havde listen indeholdt klasser, ville Default returnere Nothing i stedet...
Hej Softspot - jeg skal helt klar have kigget på LINQ og LINQtoSql. Det må være i stil med at gå fra xslt til Razor :)
Men der er kommet en update i sagen:
Da jeg ikke mente det kunne passe at det udmærket kunne køre fra min localhost mod SqlServeren på nettet, valgte jeg at prøve at købe et billigt UNOeuro-hotel og teste selv samme script på. Det viste sig at det eksiveredes med det samme, uden problemer og med god performance-tid (!)
Jeg gik til mit nuværende hostingfirma for at høre om man evt. kunne være lagt i en lavere rangeret pool (eller noget i den stil) mht. performance og båndbredde så længe man testede sit webhotel - Og det var præcis det man var. Jeg fik dem til at ændre mit hotel til værende aktivt, og herefter kunne min side med scriptet eksikveres uden at hænge/gå i selvsving - dog med en noget højere render time, end på det billige UNOeuro-hotel. Dette på trods af at SQLserveren ligger hos dem selv, inden for samme netværk, som vi har været inde på tidligere i denne tråd.
Der kan være flere årsager til at UNOeuro performer bedre, f.eks. at du er alene om - eller kun deler webserver og SQL Server med få andre. Alternativt har UNOeuro ikke samme grad af throtteling på deres netværk/database/webserver, som din anden udbyder. Eller helt andre faktorer.
Det er svært at styre sådan noget, når man kører i et shared miljø, så der må man enten tage sig til takke med det man kan få, eller skifte til noget dedikeret :-)
Nu ved jeg ikke hvordan du rent faktisk indlæser data fra din CSV-fil, men der kunne måske også være noget med hukommelsesforbruget i den forbindelse, som gjorde at du fik problemer med en begrænset server...?
SQL-serveren ligger ved begge tests på den danske server. Det er kun webserveren der gør forskellen men med samme connectionstring. Men ja - der kan være mange årsager. Jeg har skrevet til mit danske hosting-selskab om det er muligt at opgradere til en bedre ydende server.
Jeg indlæser CSV-filen som i eksemplet med en browser-request, men det kan da godt være den kommer til at køre bedre når den bliver sat op som et cronjob.
Jeg har læst flere på nettet klager over at den TextFieldParser jeg anvender er langsom og bruger hukommelse. De fleste anbefaler System.IO.Filestream til den slags, men den har bare ikke så mange properties dedikereret til det anvendelsesformål.
Men jeg vil også slutte af her, og sige rigtig mange gange tak for hjælpen, og den ekstra hjælp i denne tråd. Det endte ud med en løsning der blev effektiviseret en hel del, i forhold til det jeg havde som udgangspunkt :-)
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.
