Bulk Upload Performance

Question

Bulk Upload Performance

Luca P 1

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?

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2020-10-10T10:45:58.283+00:00

What exactly does "bulk upload" mean here? BULK INSERT? Something else?

What about the recovery model of the databases? Are they the same?
Yitzhak Khabinsky 27,196 Reputation points

2020-10-12T00:33:17.927+00:00
Hi @Luca P ,

Is it possible that the following is true:

Speedy computers have an SSD drive with the input file as a source.

Slow computers have a mechanical drive with the input file as a source.

4 answers

Your answer

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2020-10-10T10:45:58.283+00:00

What exactly does "bulk upload" mean here? BULK INSERT? Something else?

What about the recovery model of the databases? Are they the same?
Yitzhak Khabinsky 27,196 Reputation points

2020-10-12T00:33:17.927+00:00

Hi @Luca P ,

Is it possible that the following is true:

Speedy computers have an SSD drive with the input file as a source.

Slow computers have a mechanical drive with the input file as a source.

Answer 1

m 4,286

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

0 comments

Answer 2

Luca P 1

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

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2020-10-11T12:05:07.353+00:00

I may be asking the same thing too many times, but just to get things completely clear:

the target DB is on the same virtual server, the same istance, the same DB, the same table, with the same fields.

So the application uses the same connection string on both client computers and when you say the same DB and table, that is really physically the same, and not two physical instances of the same logical schema?

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.

And when you have completed these steps there are around two million more rows in the table? Did you verify this by running a SELECT COUNT(*) before and after?
Ronen Ariely 15,221 Reputation points

2020-10-11T12:09:05.17+00:00

hi,

3) the target DB is on the same virtual server, the same instance, the same DB, the same table, with the same fields.

I am not sure that I understand you surrectly. When you say the same table do you really mean the same table or a different table which looks the same structure....

If this is actually THE SAME TABLE, then In this case, please re-read what I wrote about the client session's properties.

Note, in this case you can ignore the options related to the CDC and table structure for example, since you are talking about loading to the same table and not to two similar tables, but is this really the same table?!?

Please provide the information about the client session's properties, as I explained above using SELECT @@OPTIONS and as Erland added also using DBCC USEROPTIONS

Answer 3

Ronen Ariely 15,221

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'

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2020-10-10T17:03:21.437+00:00

In addition to Ronen's suggestion, also run DBCC USEROPTIONS. There is some overlap with @@Sirdancelot , but there are also some more settings that may be relevant.
Ronen Ariely 15,221 Reputation points

2020-10-11T03:15:34.477+00:00

(1) And one more point I just thought about when I answered a question about CDC :-)

Remember that when CDC is enabled, then operations that would normally be minimally logged are fully logged! make sure that the slow instances do not have CDC enable on the table while the fast instance does not.

In general compare the DDL of the tables again

(2) will come soon...
Ronen Ariely 15,221 Reputation points

2020-10-11T03:15:42.747+00:00

(2) And here is a rare option:

Maybe the physical tables structure under the scenes is not the same!

I have many posts and a lecture which I presented in Hebrew and in English, where I demonstrate this exact case :-)

For example order of columns can lead to different size of tables...

You might think that you have the same tables when in regality under the scene this is totally wrong and each row in the one table cost 10 times than in the other table. You might need to rebuild the tables in order to reclaim storage and fix the physical structure of the rows.

Check the size of the tables on the disk...
Ronen Ariely 15,221 Reputation points

2020-10-11T03:16:56.807+00:00

In short... we are playing "the forums guess game" :-)

probably it could be very simple to solve if we could check the servers
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2020-10-11T10:03:22.69+00:00

Ronen, as I understand Luca, the SQL Server instance is the same in both cases, and so is the database and the table. The only difference is with the client. Provided that is correct your ideas about CDC etc are not applicable.
Ronen Ariely 15,221 Reputation points

2020-10-11T10:55:00.783+00:00

Hi,

the SQL Server instance is the same in both cases, and so is the database and the table. The only difference is with the client. Provided that is correct your ideas about CDC etc are not applicable.

This is my point that maybe the OP missed something and this is not true "The only difference is with the client."

It is very difficult for someone who do not understand the internals of the physical tables to make sure that the tables are the same. Even if you see in the logical layer that the tables are the same (same columns, indexes, constraint, and so on), the table might be totally different behind the scenes!

For example (and I can find many other samples), if he built table A in database X with column c1,c2,c3 and then removed a column c2, and in database B he have the same table with column c1,c3 then these table might NOT be the same under the scenes

...comment length limitation...
Ronen Ariely 15,221 Reputation points

2020-10-11T11:04:52.287+00:00

In order to find the real physical structure of the table you need to use un-documented entities. This is not something a common DBA will know to do

getting the table structure using the official options like using sp_help or sys.columns or simply SELECT * FROM T shows only the logical layer and does not reveal the true physical structure.

To get the physical structure we need to use system_internals_partitions and system_internals_partition_columns and how many users know about these?!?

These are not documented :-)
Ronen Ariely 15,221 Reputation points

2020-10-11T11:12:17.32+00:00

Tip to all that work with clients! Never take their word about what is the real scenario for granted.

Too many times a client told me "but the tables are the same" which I found out it is not true!

Too many times a client told me "I did not change anything and it stop working" and I found out at the end that one of the employees (or even the owner who claim to be expert DBA and learned in courses) actually changed something like the type of a column which led the entire application fail

Too many times...

Always use the client information with "a grain of salt" :-)

Answer 4

Luca P 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

David Browne - msft 3,856 Reputation points

2020-10-10T13:47:24.25+00:00
After loading have the client query the session wait stats for the connection. eg

select * from sys.dm_exec_session_wait_stats where session_id = @@spid and wait_time_ms > 0 order by wait_time_ms desc

That should give you a rough allocation of the elapsed time. You can also examine the Client Statistics, although they are typically less informative.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2020-10-11T10:07:29.047+00:00

Even if the clients are the same, I would suggest that your perform some other sort of test that does not involve SQL Server. For instance, write a silly app that pegs the CPU and see how long time it runs on both machines. Also try copying a large file to/from the same Windows share.

The purpose of these tests would be to narrow down where the problem might be. If you get a difference in any of these tests, it may be some issue in the VM configuration. On the other hand, if these tests do not show any difference, they may be a relation to SQL Server.

Share via

Bulk Upload Performance

4 answers

Your answer