Our German office recently upgraded an application from MSAccess to SQL Server, and noted some horrifying performance drops when adding many records to a table (ie. parsing a text file and inserting records).
We scratched around and came up with the attached solution. We tried 5 methods of adding data, and finally settled on the converting the data to add into an XML document and use SQL2000 XML capabilities to bulk-insert the data.
Try the tests for yourself and see the difference it made (approx. 500% faster than other methods). Thought you might find it useful.
Note You will have to register the kwconnectionstring.dll – it just constructs an ADO connection string for you, and you’ll need MSXML4+.
Usage:
- Run the SQL script to create the SQL database, and alter the connection string to point to your sql server.
- Run the app.
- Click Command1, this will run the inserts into SQL using ADO recordset and time it.
- Change Frame1 to Access lokal and click again. You see the speed difference and issue!
- Change Frame1 to SQL Server again and
- select “Use SQL XML” and click Command1 again.
Multi-test will average out the tick count for a number of tests for you.
Attachments
| File | Uploaded | Size |
|---|---|---|
| 979-20190911-031503-crautest.zip | 9/11/2019 3:15:03 AM | 19232 |