Multi-insert batch executed remotely 5x slower than locally

Question

I am inserting 30.000 rows into a table in one batch using INSERT .. VALUES statement for each row.

See the testing environment:

Table creation:

CREATE TABLE [dbo].[TestInsert](
    [Col1] [int] NOT NULL,
    [Col2] [varchar](16) NOT NULL,
    [Col3] [varchar](15) NOT NULL,
    [Col4] [int] NULL,
    [Col5] [datetime] NOT NULL,
    [Col6] [nvarchar](128) NOT NULL
)

Batch inserting rows:

INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491073,N'D0058A79AE',N'OCIP',51849,'20100823 10:02:04.683',N'TUVWXYZabcdefgh')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491074,N'D00559B4C4',N'OCIP',62488,'20100823 10:02:04.710',N'CDEFGHIJKLMNOPQ')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491075,N'D005AB75B6',N'OCIP',52836,'20100823 10:05:17.070',N'BCDEFGHIJKLMNOP')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491076,N'D0070B9F25',N'OCIP',62554,'20100825 08:03:08.260',N'BCDEFGHIJKLMNOP')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491077,N'D00753F2D7',N'OCIP',62554,'20100825 08:03:58.733',N'UVWXYZabcdefghi')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491078,N'D0070B979A',N'OCIP',62554,'20100825 08:04:09.917',N'STUVWXYZabcdefg')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491079,N'D0070B6F37',N'OCIP',62554,'20100825 08:04:21.043',N'CDEFGHIJKLMNOPQ')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491080,N'D0070B86F3',N'OCIP',62554,'20100825 08:05:28.460',N'GHIJKLMNOPQRSTU')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491081,N'D00708D1E1',N'OCIP',62554,'20100825 08:06:50.030',N'CDEFGHIJKLMNOPQ')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491082,N'D0070B7DFA',N'OCIP',62554,'20100825 08:11:13.507',N'VWXYZabcdefghij')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491083,N'D0070B7FCE',N'PRON_OCIP',62555,'20100825 09:13:26.563',N'XYZabcdefghijka')
...
more (30.000 rows)

You can download the whole batch here: https://filebin.net/zg499h4iv1m44z6v

The table has no indexes, constraints, foreign keys, triggers…nothing.

When I run the batch in SSMS locally, it takes 20 seconds and produces these wait stats:

But when I run it in SSMS from a remote computer over LAN, it takes 100 seconds (5x slower) with almost the same wait stats:

Please, mention there is no ASYNC_NETWORK_IO wait type.

SET NOCOUNT ON is not set intentionally!

Testing in other environment it works just fine with no big differences in time.

What could be the real source of the slowness? Why it's not captured by wait statistics? Please be more specific than just saying „network infrastructure“.

Answer 1

So there's only one batch (SqlBatchCompleted) involved here, as I understand. Assuming that:

My guess is that the difference is because of (the lack of) the network stack.

The client network library default to try Shared Memory first. This doesn't "dip down" in the network stack. This is likely the one you end up with from the local SSMS.

But Shared Memory will of course not work from the remote machine. The next tried is TCP, which is likely the one you end up with from the remote SSMS (unless you either re-configured that client or re-configured SQL server - both pretty unlikely).

So you compare using a netlib which uses a shared memory area to one that travels the code path of the network stack in both directions and also has bandwidth and latency because of the network in between.

Things you can play with includes:

• Force the TCP netlib when running locally (option in SSMS client connect dialog).
• Play with various network packet sizes (also available in SSMS connection dialog).
• Talk to your network people and try with jumbo frames and such things.

Answer 2

I converted that file into a dynamic SQL batch, and it takes me 34sec just to send the batch to Azure SQL Database, and there's no wait stat that tracks that time. eg

    declare @sql nvarchar(max) = N'
    
    INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491073,N''D0058A79AE'',N''OCIP'',51849,''20100823 10:02:04.683'',N''TUVWXYZabcdefgh'')
    INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491074,N''D00559B4C4'',N''OCIP'',62488,''20100823 10:02:04.710'',N''CDEFGHIJKLMNOPQ'')
    INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491075,N''D005AB75B6'',N''OCIP'',52836,''20100823 10:05:17.070'',N''BCDEFGHIJKLMNOP'')
    . . .

But it only takes 2sec for me to download the batch script from a temp table.

It also could take more CPU time than in the local environment (especially if it's a VM, has TDE on, or has slower CPUs), but it looks like the upload probably accounts for most of the difference.

Answer 3

Given that you are inserting from app into db, there is no reason for async_io_wait as your db is not waiting for app to signal back to DB.

The question is what the enviroment looks like. I get you have no other option, but sending 30000 insets per row is going to be slow :/

With 80 seconds of overhead, that's 2ms per row extra overhead. Which by itself actually looks ok, but it adds up.

Questions:

1. is the app sending each insert by itself or the whole file at once, i assume per row
2. what is the enviroment like is the app on same environment as db or is there distance? Is the same layout for other enviroments

Suggestion: Start profiler, and monitor the commands as they come in. My bet would be delays there

Answer 4

I don't know about remote problem for this question. but I've other solution for give good feedback

Try this one method:

1. Save your data by .CSV or .XLM

2. Use this bulk insert like below

BULK INSERT [TableName]
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH ( FORMAT='CSV');

3. if you don't like number-2 using pipes to import data from a file

both of the document (number 2-3) for use other file type and etc in micro-soft document