07. februar 2013 - 19:33Der er
15 kommentarer og 2 løsninger
Hurtigere måde at lægge data op i MS SQL Database
Hej,
Jeg skal have lagt mange data op i en SQL database og de kan jeg selvfølgelig gøre via den klassiske SQL Statement "Insert Into .." - men er der en hurtigere måde ??
Rent praktisk er det realtidsdata der skal lægges op. Data indhentes fra noget måleudstyr og der skal så lægges målepunkter op hvert sekund. Jeg regner med at samle data i en TstringList og via et Timer Event så lægge en samling af data op f.eks. hvert minut...
insert into .... values..... ; insert into .... values..... ; insert into .... values..... ; insert into .... values..... ;
Sådan at det bliver en lang string.
Men altså... MSSQL klarer nemt et par tusind inserts i sekundet, selvfølgelig afhængig af datastørrelsen og index på tabellen, men en tabel uden index og med et par simple int's og varchar's i nogenlunde størrelse, der ræser den altså derudaf.
En måde du kan opnå en god hastighed på er at holde din connection åben når du laver din batch hvert minut. Jeg holder nemlig mere på at en insert er at foretrække fremfor en stor fil, hvis nu noget går galt er det uhyre vanskeligt at håndtere det.
OK - man ville det så ikke være en ide at definere en række variable a la SQL ("@Variabel") og så lægge data op på den måde i stedet? er det ikke hurtigere??
Du bør også holde øje med log-filen. Sæt recovery mode til simple eller bulk før du begynder. Derefter tilbage til full igen. (måske lige trimme logsilen først - hvis du allerede har indlæst mange filer, så er loggen stor).
Der er stor gevinst ved at genbruge scriptet. Forbavsende stort.
Lav dit script i forvejen og fyld det ud. Husk at sætte alle felter så du ikke får kopieret forrige inserts værdier over i den næste
insert into dbo.test (dato ,navn ,adresse) (:dato ,:navn ,:adresse)
Endelig, så er effekten af at proppe insert-sætningen ned i en stored procedure, prikken over i'et.
create stored procedure dbo.SetTest @dato datetime, @navn varchar(20), @adresse varchar(20) as insert into dbo.test (dato ,navn ,adresse) (@dato ,@navn ,@adresse) end
Disse tip giver rigtig meget (jeg har benchmarket mig frem til det og indlæser dagligt 10-20GB på få minutter (på en sindssyg kraftig server)) - hvilket i øvrigt også er hvorfor jeg er her i dag.
Hvis du sætter recoverymode til simple, laver en procedure, og bruger Arnes råd om en bacth 0på 50-100 records pr. transaktion, så kan det ikke blive meget hurtigere.
... vil lige forklare hvorfor en sproc er hurtig. Delphi kan ikke køre processer parallelt, men det kan sql-server, så hvis maskinen har 4 kerner, så kan den indsætte flere gange hurtigere end hvis det var afviklet i Delphi.
Arne: Det er muligt du har ret for man kan som regel altid stole på hvad du skriver. Jeg kan se, at tingene går meget hurtigere (en faktor 3 eller 4) når der indsættes med SP. Desværre kan jeg ikke finde tallene fra min eksperimenter (brugte en 2008R2 SQLExpress på min PC).
Jeg indsatte samme datamængde (ca. 200.000 records) på 3 forskellige måder og varierede parametrene.
1. Opret TADOQuery lav insert-script, udfør og frigiv. Gentag. Meget langsomt 2. Opret TADOQuery, lav insert-script, udfør, gentag og frigiv. Meget bedre. 3. Lav SP, kald, gentag. Helt klart bedst.
Prøvede det bagefter i transaktioner a klumper af 200'ish records. Det hjalp alle på alle forsøgene.
Et eller andet fungerer altså bedre med SP og transaktioner i klumper (SQLServerens recovery mode=simple). Den anden ting der fortæller, at der er noget om det er, at alle processorer er pænt belastede, mens Delphi kun belaster 1.
Derfor vil jeg, med din påstand om, at SP'ere kun kan være synkrone, med al respekt, stille mig skeptisk an og stole på mit eksperiment.
Desuden kan man vælge asynkron eksekvering i TADOStoredProc'ens properties. Ikke at jeg har gjort det i mit program.
Jeg måtte skrive mig et testprogram og resultatet overraskede mig. Jeg testede på min lokale maskine og der er ydelsen noget anderledes end postuleret:
1. Opret Query, Indsæt, Frigiv og Gentag (CPU hhv. gns. 10%, 13%) - u. transaktion: Inserting 200000 records in 2.28 (1351/s) - m. transaktion: Inserting 200000 records in 1.35 (2105/s)
2. Opret Query, Indsæt, Gentag og Frigiv (CPU hhv. gns. 9%, 12%) - u. transaktion: Inserting 200000 records in 1.46 (1886/s) - m. transaktion: Inserting 200000 records in 0.52 (3846/s)
3. Opret SP, Indsæt, Gentag og Frigiv (CPU hhv. gns. 10%, 14%) - u. transaktion: Inserting 200000 records in 2.02 (1639/s) - m. transaktion: Inserting 200000 records in 1.11 (2816/s)
ExecuteOptions = []
1. Opret Query, Indsæt, Frigiv og Gentag (CPU hhv. gns. 10%, 13%) - u. transaktion: Inserting 200000 records in 2.31 (1324/s) - m. transaktion: Inserting 200000 records in 1.40 (2000/s)
2. Opret Query, Indsæt, Gentag og Frigiv (CPU hhv. gns. 9%, 12%) - u. transaktion: Inserting 200000 records in 1.47 (1869/s) - m. transaktion: Inserting 200000 records in 0.58 (3448/s)
3. Opret SP, Indsæt, Gentag og Frigiv (CPU hhv. gns. 10%, 14%) - u. transaktion: Inserting 200000 records in 2.1 (1652/s) - m. transaktion: Inserting 200000 records in 1.12 (2777/s)
Jeg sletter tabellen efter hver test og har kun primærnøglen at indelsere. Det lader til at det der virker bedst, er test2 med transaktioner.
Jeg prøvede at oprette 8 mdf-filer (en til hver kerne på PC'en), men det gav ikke noget. Sql-serveren skulle kunne dedikere en kerne til hver fil - groft sagt, men jeg ser ikke den store forbedring.
.. men hos kundens <i>ved ved gud ikke hvor mange CPU'er og hvor meget ram den indeholder</i> server, der får jeg bedst ydelse med SP'en.
Jeg er sikker på jeg i min oprindelige test opnåede bedst resultat med SP'en - men min nyeste test dokumenterer det ikke. Æv. Forresten så er test1 kun med fordi jeg har set det nogle steder. Det er tungt og dumt at oprette udføre og frigive SQL-objektet i hvert iterering.
... I stand corrected.
Synes godt om
Ny brugerNybegynder
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.