T-SQL Performance Tuning on Bulk Load Data in SQL Server 2017

In this article, we are going to talk about a specific scenario where T-SQL Performance Tuning is required for bulk load operations. This scenario aims to improve SQL Performance without scaling-up the hardware configurations.

So, let us consider the following scenario:

Hardware and Software Information:

• SQL Server Express 2017
• SQL Server Build: 14.0.2002 – xxxx
• OS: Windows 10 Pro – Build 1803 – 17134.590
• Hardware: Processor i7-4790 with RAM 16 GB
• SQL Server is local on the laptop.
• Dev Environment: .NET 4.5
• C# Studio 2017 development system

Table Schema Definition:

TABLE [dbo].[DataValue]

(

[ID_Cud_FK] [int] NULL,

[DataId] [nvarchar](20) NULL,

[DataIndex] [int] NULL,

[X] [nvarchar](20) NULL,

[Y] [nvarchar](20) NULL

)

Bulk Loading the table:

String-Numbers are written in X and Y. The length is max. 20 characters. There are three nested loops. The last one here has about 10,000 entries (DataValue). The time to do so is about 8 seconds.

The loop above it has 130 cycles. The first has 50 cycles.

Thus, there are 50 * 130 * 10,000 lines in the table “DataValue” = 65,000,000 lines.

It takes more than 14 hours for the process to complete.

So, the real question is, How to make this process at least a factor of 50 – 100  faster?

The Solution

In this scenario, the easiest way to improve performance is by ensuring whether ‘nvarchar‘ is really required?

Do not use nvarchar until unless if you are expecting UNICODE data. If not, we recommend trying with ‘VARCHAR‘ to avoid the implicit conversions and also nvarchar is a (highly) costly operation.

Other questions that you need to ask is as follows:

• Where is the data coming from?
• Does the C# app calculate it?

Assuming that the primary objective is to load the data quickly into SQL, make sure you are using SqlBulkCopy to efficiently load data into SQL, specifying large batch sizes (100000 or more).

public sealed class SqlBulkCopy : IDisposable

You have to consider a way of batching this data in C# – you can use a DataTable or such to construct the “buffer” in C#, and then use that with SqlBulkCopy to efficiently ingest that data into SQL.

One key consideration for loading data is the recovery model for your SQL database. See the screenshot below:

If you are working on a development computer, you can set the recovery model to Simple. For production systems, it is highly recommended to set the recovery model to “Bulk Logged” to avoid any performance issues or index fragmentation.

Conclusion:

To summarize, when you are trying to bulk load data into SQL, following some of these simple steps mentioned about will go a long way to improve the overall performance.

We hope that the post was helpful. Feel free to drop us your suggestions in the comment box down below. Thanks for visiting!

Disclaimer: The Questions and Answers provided on https://gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.