0

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.

aSystemOverload
  • 2,697
  • 18
  • 43
  • 68

2 Answers2

1

Since you are on SQL Server, have you tried the IMPORT functionality instead of using Linked tables? I think you will get serious performance improvements.

In SSMS, right click on the database name, go to TASKS / IMPORT

This uses SSIS instead of Linked tables.

If you like the speed that the import shows you (and I have found it to be blazingly fast), then you can create a SSIS package and use that to upload the data, and execute it from the command prompt.

Raj More
  • 44,313
  • 31
  • 123
  • 191
  • Thanks Raj, I'll check this out later and see how fast/easy it is etc... Will upvote/accept once I've had a look. – aSystemOverload Dec 13 '12 at 15:33
  • It works and quickly, but it doesn't activate the ON INSERT triggers... Any ideas? – aSystemOverload Dec 15 '12 at 10:24
  • 1
    There are ways of firing a trigger, but more importantly, a trigger will seriously slow you down on Bulk uploads. What are you doing to the data to merit a trigger? – Raj More Dec 17 '12 at 16:17
0

If you don't mind dipping into C#/VB.NET, you can load the Access data into a DataTable, then use SqlBulkCopy to upload the data to SQL Server.

  1. Open a connection to the Access DB via OleDb. MSDN has an example. You can skip everything past the loading.

  2. Set up your table mappings, then upload the data. MSDN has a good, terse, example.

This approach is very fast - I have a bulk upload process that uploads 100k rows in less than 3 seconds. My rows are fairly small, and the data/server are on the same machine, so it is in optimal conditions, but I have found it very fast in most cases. see note below

If you run into problems, I can sketch a quick code sample.

Erick

Edit

The performance is from the use of Table Valued Parameters. Using it from a program is my preference, but you can use it from within SQL. It has attributes of both a bulk insert and a traditional insert. It's in all editions of SQL post 2008.

Erick T
  • 6,313
  • 6
  • 38
  • 78
  • This is clearly an ETL Task, and SQL Server comes with an ETL tool. Custom writing the whole functionality is not a solution I would recommend especially when there is an off-the-shelf product available at hand. – Raj More Dec 13 '12 at 18:30
  • Erick. Firstly, I can't use VB.net or C#... Secondly, the problem as I mentioned is that the server is in US with a Ping of 160ms+ (and not local with a Ping of <5ms), so your example would probably not take as short a time as we'd like... – aSystemOverload Dec 13 '12 at 19:18
  • The main performance improve is not the location, it's that the records are inserted in bulk. It is a table value parameter which makes the difference. I don't believe that SSIS can use TVPs. If perf is the primary issue, you will be very hard to beat using a TVP, be it from C# or anything else. – Erick T Dec 14 '12 at 06:29
  • Erick, I beg to differ, but if 5/15K records upload in 30 / 90 seconds when it's in the same building, but take 30 / 90+ minutes when the server is in US with a much higher ping time, logic suggests location (or at least the ping time caused by the location or other factors) is an issue, does it not? – aSystemOverload Dec 14 '12 at 17:02
  • I am not sure that I follow. Ping time doesn't really make a difference unless you are making a new connection for every row. The throughput depends on: 1) how fast you can push data over the Internet connection (TCP, etc) and 2) how fast the SQL server on the other end can load those rows. You cannot do anything about 1 except buying faster hardware. For 2, it's all about what the server needs to do. Check out this related answer to see how TVP fares http://stackoverflow.com/questions/2149897/performance-of-bcp-bulk-insert-vs-table-valued-parameters. – Erick T Dec 15 '12 at 19:13