0

I'm trying to use SqlBulkCopy for inserting bigger amounts of data fast. For that I populate a DataTable with the data I want to insert (up to about 100,000 rows) and call the WriteToServer method. Doing so either makes a timeout occur or, given the BulkCopyTimeout property is set to 0, just does nothing.

This is the content of the method preparing the data and sending it of to the server.

using var bulkcopy = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.TableLock)
{
    DestinationTableName = $"[{databaseConfiguration.Schema}].[{DatabaseUtil.Convert(source.Name)}]",
    BulkCopyTimeout = 0,
    BatchSize = 100,
    NotifyAfter = 50
};
bulkcopy.SqlRowsCopied += (_, _) => Log.Info("Inserted 50 rows");

var dataTable = new DataTable(bulkcopy.DestinationTableName);
foreach (var column in source.Columns)
{
    var propertyName = DatabaseUtil.ConvertPropertyName(column.Name);
    bulkcopy.ColumnMappings.Add(propertyName, propertyName);
    // ...
    // add column to dataTable with proper type
    // ...
}

foreach (var content in contents)
{
    var values = content.Items.Select(x => x.Data);

    var dataRow = dataTable.NewRow();
    dataRow.ItemArray = values.ToArray();
    dataTable.Rows.Add(dataRow);
}

bulkcopy.WriteToServer(dataTable);

The target server is SQL Server 2019 running in Docker Container on a Linux machine. Testing this with SQL Server 2022 on Windows resulted in the same outcome.

Attaching a method to the SqlRowsCopied event of the SqlBulkCopy object and setting the NotifyAfter property to 1 reveals that it never even tries to copy even one row of the given table.

Experimenting with different values for BatchSize as well shrinking the size of the source data to less than 10 rows did nothing.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bomberman2910
  • 1
  • 1
  • Does SQL Profiler show whether any connection attempt was made? – Michael Liu Jun 01 '23 at 14:26
  • Does the DataTable contain anything? What is `contents` and where does it come from? That's a very inefficient way to bulk import anyway - the code is generating a *copy* of the `contents` in memory before sending it. That means at least 200K objects in memory instead of 100K. You can get rid of the DataTable and possible problems by using FastMember's `ObjectReader` to create an IDataReader wrapper over any `IEnumerable`. This way the data is sent to the server directly. – Panagiotis Kanavos Jun 01 '23 at 14:31
  • Even better, if the source is a lazy loaded IEnumerable, only a single batch's data will be held in memory at any time – Panagiotis Kanavos Jun 01 '23 at 14:32
  • `either makes a timeout occur or, given the BulkCopyTimeout property is set to 0, just does nothing.` a blocking operation isn't *nothing*, quite the opposite. It means that either the source or the target tables are blocked, possibly by other transactions. Don't ignore this, try to find out what's wrong. If the table is locked, you probably should import the data into a staging table first, then apply them to the live table – Panagiotis Kanavos Jun 01 '23 at 14:33
  • PS: `values.ToArray()` creates *another* copy of the data, which means there are 300K rows in memory – Panagiotis Kanavos Jun 01 '23 at 14:34
  • @MichaelLiu There's no sign of any connection when running this method. Everything before (simple queries) work just fine, it's just the BulkCopy doing nothing – bomberman2910 Jun 01 '23 at 14:35
  • @bomberman2910 SqlBulkCopy works. I use it to load far more than 100K rows, without creating multiple copies in memory. Something's wrong with the source or target. If blocking is an issue, you'll see in SQL Server Activity Monitor that your connection is blocked. If you try to import data into a live table, blocking is guaranteed. – Panagiotis Kanavos Jun 01 '23 at 14:37
  • Check the log file in the SQL Server by using SSMS and check in the explorer under Management. You should see a login at time of copy with user and possible errors. – jdweng Jun 01 '23 at 14:37
  • @PanagiotisKanavos There's nothing else going on on the server, so nothing's there to block any tables or any other resources for that matter. That's what I was thinking of at first as well, but considering there's nothing else going on I don't see what could block the table I'm trying to access. – bomberman2910 Jun 01 '23 at 14:39
  • Did you actually check in SSSM Activity Monitor? Where does the data come from? Perhaps it's your own application that's taking long-lived shared locks on that table? – Panagiotis Kanavos Jun 01 '23 at 14:40
  • There are other things you can try too. You can use the `TableLock` option to try and lock the entire table before importing. This results in the fastest import and will guarantee other applications won't be able to lock rows. You can use `UseInternalTransaction` to use a separate transaction for each batch. `50` is **way** to small and results in greater delays than inserting items one by one. 5000 or greater are more useful. If you use an IDataReader you can use `EnableStreaming` too, to send rows to the server as they arrive – Panagiotis Kanavos Jun 01 '23 at 14:42
  • `makes a timeout occur` what's the *actual* error? A connection timeout is a very different thing from a command timeout. If you can't even connect, no code change will help – Panagiotis Kanavos Jun 01 '23 at 14:43
  • @PanagiotisKanavos The values for batch size, etc. are just some testing values, I'm well aware of the recommended values (actually pulling my hair out the entire day already trying to find a solution). Guess I'll have to try your approach of providing data to see if the way the data table is build might be the problem. – bomberman2910 Jun 01 '23 at 14:46
  • @PanagiotisKanavos The data inserted into the data table is just a bunch of data imported from a proprietary file. The way it is handed to the database could definitely be improved though. – bomberman2910 Jun 01 '23 at 14:49
  • The error behind the SQLException timeout is just another timeout, just in form of a Win32Exception – bomberman2910 Jun 01 '23 at 14:53
  • Post the information instead of describing it. There's no `just`, that `just a Win32Exception` may be saying the *connection* timed out, which has nothing to do with your code. You may have forgotten to publish the correct Docker ports – Panagiotis Kanavos Jun 01 '23 at 14:57
  • The connection is fine. As I already said other queries immediately before the bulk copy attempt work just fine. I guess what you're looking for is this `Win32Exception: The wait operation timed out.` – bomberman2910 Jun 01 '23 at 15:00
  • No, I'm looking for the actual full exception text, returned by `Exception.ToString()` or by clicking on `Copy Details` in the exception popup. What you posted doesn't say what timeout that was. In [this question](https://stackoverflow.com/questions/16361667/win32exception-0x80004005-the-wait-operation-timed-out) the call stack shows the timeout came during reading. [This one actually uses SqlBulkCopy](https://stackoverflow.com/questions/27291338/the-wait-operation-timed-out-win32exception-0x80004005-the-wait-operation-tim) but once again, the problem was the reading code – Panagiotis Kanavos Jun 01 '23 at 15:03
  • You're using *Docker* which means that unless you mounted an external folder as a data volume, you're writing to virtual storage. That's a *lot* slower than physical IO. Or the table may have a ton of constraints or indexes that need to be updated. Or the container may not have enough memory, resulting in a lot of swapping. – Panagiotis Kanavos Jun 01 '23 at 15:05
  • Have you tried importing less data, eg just 100 rows? Just to prove to yourself that you can actually insert the data? What does Activity Monitor show when you insert, how much IO is going on? CPU usage? What do all those graphs, Resource Waits, Data File IO show? – Panagiotis Kanavos Jun 01 '23 at 15:07
  • Turns out I was looking at the wrong end of the stack trace. The error we are looking for says that it cannot access the destination table. The user I'm logging in on the database has sufficient permissions, already checked that. AFAIK that only leaves a block, which also seems a bit unlikely, because even after a restart of the server to make sure that table has never been touched, the error still occurs. – bomberman2910 Jun 01 '23 at 16:14
  • 1
    Does the user have the ADMINISTER BULK OPERATIONS permission? lacking this permission would explain why normal inserts/selects work, but bulk copies don't. – StrayCatDBA Jun 01 '23 at 18:35
  • Have you tried dumping `bulkcopy.DestinationTableName` to the console? You have an "interesting" way of constructing it, maybe it's invalid? – AlwaysLearning Jun 01 '23 at 21:59
  • @AlwaysLearning I already tried many versions of constructing the `DestinationTableName`. With and without schema, database name, putting escape brackets in any sensical position, but it's always the same result. – bomberman2910 Jun 02 '23 at 07:46
  • @StrayCatDBA I also checked that permission, but aside from that permission not being supported on Linux, even while testing on a Windows machine, where that permission can be set, it still gives me the same error – bomberman2910 Jun 02 '23 at 07:47

0 Answers0