Thread: Slow insert into remote server
09-01-11, 23:01 #1Registered User
- Join Date
- Mar 2009
Unanswered: Slow insert into remote server
We have a development server in house where we compile a lot of our data for shipping out to our production servers which sits in a datacentre on the other side of the country.
We have an extremely fast link to said datacentre, ping response is under 50ms and I can transfer gigabytes of data in a few minutes via any method I've used.
I have some processes which load data from third party sources - spreadsheets, text files etc - that come to us centrally and need to be sent to our production servers in which I am experiencing severe slowness with a remote insert.
I have a spreasheet we get from a manufacturer with between 2000 and 2500 lines, 3 columns - text width 10, text width 32, text width 20.
I load the spreadsheet into a load table on our local server - no keys, indexes, constraints, triggers - basic table defined with the three columns as above. This insert takes about 800 milliseconds.
I then do a delete from <remoteserver>.<database>.<schema>.<table> which is set up identically to the local table - again with no keys, indexes, constraints, triggers. This delete takes between around 2 and 11 seconds depending on which server - even when the tables are empty to begin with.
The next step is insert into <remoteserver>.<database>.<schema>.<table> select data from <localtable>
This step is phenomenally slow in my opinion - taking up to 8 minutes on one of our remote servers.
Procedure log below to give you an idea of times.
2011-09-02 09:28:23.867 - Distributing data to linked servers. 2011-09-02 09:28:23.867 - Distributing data to <SERVER1>. 2011-09-02 09:28:23.870 - Deleting data from load tables. 2011-09-02 09:28:34.613 - 0 records deleted from load table. 2011-09-02 09:28:34.613 - Copying data from base server. 2011-09-02 09:36:11.200 - 2218 records inserted into load table. 2011-09-02 09:36:11.200 - Finished distributing data to <SERVER1>. 2011-09-02 09:36:11.200 - Distributing data to <SERVER2>. 2011-09-02 09:36:11.200 - Deleting data from load tables. 2011-09-02 09:36:13.357 - 0 records deleted from load table. 2011-09-02 09:36:13.357 - Copying data from base server. 2011-09-02 09:39:05.667 - 2218 records inserted into load table. 2011-09-02 09:39:05.667 - Finished distributing data to <SERVER2>. 2011-09-02 09:39:05.670 - Distributing data to <SERVER3>. 2011-09-02 09:39:05.670 - Deleting data from load tables. 2011-09-02 09:39:08.210 - 0 records deleted from load table. 2011-09-02 09:39:08.210 - Copying data from base server. 2011-09-02 09:42:17.460 - 2218 records inserted into load table. 2011-09-02 09:42:17.460 - Finished distributing data to <SERVER3>. 2011-09-02 09:42:17.460 - Finished distributing data to linked servers.
The tables I am inserting/selecting from are in a staging database that users cannot access so there's no locking issues, I'm the only one that can touch those tables.
As you can see, the above took around 14 minutes to send 2218 records to 3 servers. Some of my loads go up to 30-40 thousand records which take hours. I accept that I probably can't do much to speed it up other than switching to use SSIS or sqlcmd which I don't really want to do since the shipping/loading of data in its present form is centrally controlled with a single stored proce that I execute and I'd rather keep it that way - and the list of servers the data is to be sent to is dynamic depending on the data in the file and some data in a remote server configuration table. I'm more just curious as to why an insert remotely is so slow. Any insights would be welcome.
09-02-11, 04:41 #2Registered User
Provided Answers: 2
- Join Date
- Apr 2008
- Iasi, Romania
Try to send batches of 100 rows followed by a COMMIT.Florin Aparaschivei
DB2 9.7, 10.5 on Windows