Hurtigere måde at lægge data op i MS SQL Database

En INSERT per sekund burde slet ikke vaere et problem.

Hvis du bundler 60 INSERT i en enkelt transaktion vil det vaere lidt hurtigere.

OK - og hvad betyder "Bundler"??

Jeg er klar over at det betyder at jeg knytter de 60 INSERT samme i een klynge og lægger den klynge samlet op i SQL databasen - men hvordan??

Du adskiller med ;

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.

med bundle mener jeg:

auto commit off
begin transaction
60 x insert
commit

OK

Arne-V & Janus_007: Smid et svar

Arne-V: Hvad mener du med "Auto Commit off" - kan du give et mere eksplicit eksempel??


Jeg har nogen gange set at man placere en @ inde i en SQL staement - hvad er det til og hvilken funktion har det??

@ er når man deklarerer en variabel.

http://msdn.microsoft.com/en-us/library/ms188927.aspx

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??

Arne_V: Smid et svar ;-)

De fleste databaser har en:

SET AUTO_COMMIT OFF;

for at undgaa at statements bliver automatisk committet.

I SQLServer hedder det saa:

SET IMPLICIT_TRANSACTIONS ON;

Jeg tror absolut ikke at det bliver hurtigere med at erklaere variable.

og et svar fra mig

De fleste database API'er har kald til at saette auto commit off, start og commit af transaktioner.

Men jeg ved ikke hvordan man goer det i Delphi.

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.

Jeg vil forvente at gevindsten ved at kalde en SP med en enkelt INSERT er saa lille at den reelt ikke kan maales.

Det er rigtigt at SQLServer er multi-threaded, men da SP koeres synkront ikke asynkront, saa giver det ingen fordel.

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.

Hej Arne.

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:

ExecuteOptions = [eoAsyncExecute,eoExecuteNoRecords]

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.