Hurtigste måde at indsætte data ind i SQL Server tabel.

Jeg har brug for den hurtigste måde at indsætte data i SQL server tabel.

Forudsætninger:
- Client Server Løsning. Program kører på en anden fysisk server end SQL serveren.
- Data kommer fra en Access Database med 6000 besvarelser med 300 spørgsmål pr besvarelse.
- Svar ligger ikke relationelt i Access (i note felt), men skal på SQL serveren ligge som relation imellem spørgsmål og svar.

Jeg har konstrueret et program der laver de 6000 poster om til 1.8 million relaterede poster.

Tid Access database 20 min
Tid SQL server: > 6 timer!!!! (der stoppede jeg den).

Jeg har nu løst problemet på en anden måde (kopieret Access database til SQL Server, og brugt import data wizard), men det irriterer mig at jeg ikke kunne få det til at spille.

Har i dag lavet et testprogram der indsætter dummy data i en test tabel med 5 forskellige datatyper:

Eksempel :
  For i = 1 To Iterations
    rs.AddNew
    rs!TestInt = i
    rs!TestChar = "Hello world" & i
    rs!TestDate = Date
    rs!TestFloat = i * 3.1415926
    rs!TestText = String(i, "P")
    rs.Update
  Next i

Jeg har så foretaget målinger på hvor hurtigt det kan udføres på forskellige måder:

1.    INSERT statements som udføres med ADO.connections Execute metode
2.    ADO Recordset baseret på "SELECT * FROM Table Where Key Is Null", og så benytte AddNew og Update.
3.    Parameter INSERT statement igennem ADO command object.
4.    Stored Procedure som udfører insert igennem ADO command object.

Metode 1 er klart den langsomste, metode 4 den hurtigste, men forskellen på 2-4 er kun få procent.

Har prøvet med ODBC og OLEDB connection string: OLEDB er hurtigere (~5%), og metode 3 og 4 er kun understøttet under OLEDB.

Har prøvet med eller uden transaktion: Med er hurtigere i størretsordnen 1-2%

Har prøvet med eller uden primær index på TestInt. Ikke målbar forskel.

Men performance ligger stadig på niveau med det oprindelige program jeg skrev (Benyttede metode 2).

Hvordan kan man få sådan en batch insert til at performe bedre?

OBS! Det er "snyd" at lave en stored procedure som indeholder løkken, for det kan ikke bruges i mit praktiske scenario.