Up until this point I've been uploading 5K and 15K records separately to a locally hosted SQL Server (same building) from an Access DB and they took around 30 and 60 seconds respectively.
Now, I've been kicked off of the local server (<5ms ping) and forced to migrate to one in the US, which has 160ms ping time.
The uploads now take 30 Minutes and 90 Min+. I'm using a very standard, run of the mill INSERT INTO with linked tables. To give some quick context. I receive a bulk file which I have to filter, select specific columns and then copy/paste into an Access DB (all text(255)), then using recordsets to copy them to a similar table with specific datatyped fields and constraints to alter/clean some fields etc...
I need to get back to more reasonable upload times, pref no more than 5 and 10 min respectively. With the Server's current ping times I can't see that happening with INSERT INTO statements as I'm guessing the lag hits the upload per record, so looking for a way to upload in bulk or any other way that you can suggest that might improve these times.