SQL Server vs. Access insert performance, in particular when using GUID

Answer 1

You realize at least part of the decreasing performance is the log filling up, and that a GUID id what, 40 bytes longer than an int?

But I'm not quibbling; it's good to see someone taking actual metrics rather than just handwaving. Modded up.

Answer 2

Where are you getting the data from?

Does it change the numbers if you use the Access Export menu options rather than record-at-a-time-in-a-loop?

VBA is really sensitive to the connection paramters too, and there are lots of options that aren't necessarily intuitive.

If an identity column is acceptable, why are you even considering a sequential GUID (which is something of a tacked-on facility in MSSQL last I checked).

---

EDIT: Looking at your code and briefly reviewing the Recordset docs on MSDN, I see you may be able to use more efficient parameters. E.g. your dbSeeChanges and dbOpenDynaset, which are appropriate if you are trying to allow for other users messing with the same rows (or needing to get back the inserted IDENTITY value or probably GUID), but I don't think you need those. In essence, after every INSERT or UPDATE, you're reading the record back from the database into VBA. I'd read through those connection config settings carefully, and I bet you'll come up with something a lot more satisfactory.

Answer 3

The last time I saw something like that (really slow insertion with GUID PK) was because of the log-file filling up. Insertion performance was dropping like a stone, pretty fast (no hard measurement, just looking at live traces, but it sure looked like it was kinda logarithmic). This was pre-loading of historical data. Moved over to identity PK, took care of actually cleaning up the log file, and everything went much better afterwards (a couple of hours where the first version took several hours and was not finished).

Also, just a thought, are there any transactions involved? Maybe SQL Server transactions create a big performance hit that access does not have (given that access is not really geared towards concurrent access).

Answer 4

My question is whether your test setup represents the reality of your application or not. In short, are you testing the right thing?

Is your app going to be appending large numbers of records one at a time?

Or is it going to be appending batches of records based on a SQL SELECT?

If the latter, you might look at trying to do it all server-side, particularly if the source table(s) in the SELECT are on the server. It's important to realize that with ODBC, a batch append is going to be sent to the SQL Server as a single insert for every single row (every similar to the recordset-based approach in your test code). If you move the same process entirely server-side, it can be done as a batch operation.

Also, you should test again using ADO instead of DAO. It may optimize the operation completely differently.

Last of all, someone brought to my attention just this past week this fascinating article by Andy Baron:

Optimizing Microsoft Office Access Applications Linked to SQL Server

I'm still absorbing the contents of that very useful article, and it discusses several issues in regard to non-GUID-specific topics that may help you optimize your process for maximum efficiency.