Bulk Upload Performance

Question

Hello, I have more computers in the same local network that perform the same bulk upload into a table of sql server 2014 (the same input file about 500.000 rows).
One group of computers perform the bulk in 20 seconds, the others in 15 minutes.
The sql server is installed on a virtual machine locally.
One the computer that perform in 15 minutes is connected to the same switch of the sql server.
What should be the problem?

Answer 1

It's a vb.net application,

Here the code

Using bulkcopy As New SqlBulkCopy(sqlconnection)
bulkcopy.DestinationTableName = strTable
bulkcopy.BulkCopyTimeout = 0
If dt.Rows.Count > 0 Then
righeImportate = dt.Rows.Count
bulkcopy.WriteToServer(dt)
End If
End Using

The dt (data table) is the same (about 500000 rows). The target table is the same, in the same database, on the same Sql Server.

Always is the same.

The only difference is that I run the application from two different client

Also the client are done from the same image (the client that perform worst has 16gb ram, the client the perform better has 8gb ram.

The same network local network

Answer 2

Good day,

We will probably need more information but...

The only difference is that I run the application from two different client

This suggest the option that the issue related to the client session.

As first step after I see this, I would check the client session properties

Execute the following query in all sessions and check if the number is the same: SELECT @@OPTIONS

If the number is not the same then your sessions do not use the same properties. You can get more details by unexecute the following

DECLARE @options INT
SELECT @options = @@OPTIONS

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT'
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER'
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT'
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON'
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF'
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL'
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT'
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

Answer 3

To better explain: 

1) only one app that runs the bulk upload (the same, the app is load from the server, from the same server)
2) the file that I try to upload is the same
3) the target DB is on the same virtual server, the same istance, the same DB, the same table, with the same fields.

I run the upload from the client A: 20 seconds.
After that I run the upload from the client B: 15 minutes.
After that I rerun the upload from the client A: 20 seconds.
After that I rerun the upload from the client B: 15 minutes.

Always the same happens.

In the meantime no one have changed the table on SQL Server or some settings on SQL environment.

I think that there are some settings that slow down the upload, but I not an expert

Answer 4

Hi @Luca P ,

Please make sure whether there are other apps or programs are running on ClientB when you are doing bulk upload;
And do you try with other clients? I mean you can use one Client C to test whether the time is 20s, or 15mins.

More information: options-to-improve-sql-server-bulk-load-performance

BR,
Mia