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:
Table Schema Definition:
[ID_Cud_FK] [int] NULL,
[DataId] [nvarchar](20) NULL,
[DataIndex] [int] NULL,
[X] [nvarchar](20) NULL,
[Y] [nvarchar](20) NULL
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?
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.
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.
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!
Comments are closed.