Interview QuestionsSQL Server T-SQL Performance Tuning on Bulk Load Data in SQL Server 2017 April 6, 2019720 views0 Share By IG Share 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://www.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. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0 IG Website Twitter
Interview Questions Comparing Google Workspace vs M365 Microsoft 365 Business vs Enterprise By IGMarch 20, 2021
Interview Questions How to Disable Bluetooth on Startup on Ubuntu Linux OS by Default By DasMay 17, 2020
Interview Questions How to get the ACTUAL version number of Windows 10 from command line? Windows 10 is the latest operating system from Microsoft. Ever since Windows has moved to ...
Interview Questions How to open a folder from Windows Explorer in Command Prompt under Windows 10 with a shortcut? For Windows power users, the ‘Command Prompt’ is one of the extended options you would ...
Interview Questions How to Manually Edit the Right Click Menu on Windows 10 Step by Step If you are looking to change the default settings and edit the right click menu ...
Interview Questions How to Use Windows Media Creation Tool For Windows 10 to USB The Windows Media Creation Tool is an excellent option for installing your Windows 10. You ...