Insert lots of records into a table : Access vs. SQL

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.

About this post

Posted: 2019-09-11
By: stickleprojects
Viewed: 224 times

Special Instructions

This code originally appeared on AndreaVB.com, and has been republished here with the permission of Andrea Tincani.

Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.