Generally, the final stage (Load) loads the data into a data lake or an SQL database, typically Microsoft SQL Server<\/strong>. This data-loading stage often takes up a significant portion of the ETL process because the insert<\/strong> operation\u2014which inserts rows into the database\u2014is relatively slow.<\/p>\n That is why there is a way to insert data in bulk called Bulk Insert<\/strong>, which allows the SQL server to read a data file and insert it directly into the database.<\/p>\n Today we are going to compare the performance of these two techniques (Insert and Bulk Insert) by writing from R to SQL; we will compare the time it takes to write databases or tables of different sizes.<\/p>\n The experiment will consist of inserting 3 datasets with 100,000, 1,000,000, and 10,000,000 rows, each with 10 columns of 20 random characters.<\/p>\n To perform these tests, we will use the official Microsoft ODBC driver on Linux with the odbc package for R. Additionally, we will generate the CSV file for bulk insert using the data.table library, which is the fastest one I know of (if you know of another one, let me know). In both cases, we\u2019ll measure the total load time\u2014that is, from when the data is ready until it\u2019s inserted\u2014including creating and uploading the data files to the server.<\/p>\n The system configuration is quite simple: the SQL server and R are in the same data center, and file transfer is handled via SAMBA (a Windows shared folder).<\/p>\n On the Y-axis we have time and on the X-axis the number of rows. We can see that the normal insert is considerably slower and that we don\u2019t have data for 10 million rows\u2014this is because the load failed.<\/p>\n The difference is staggering, but according to SQL Server documentation, bulk insert can run in parallel, so we\u2019ll compare the speeds for 1, 4, and 8 threads<\/p>\n I want to point out that the programming required to achieve parallel insertion is somewhat complex, but here are the results:<\/p>\n We can see that for large datasets, using 8 threads is beneficial, while for small datasets it can be problematic. This is because splitting the dataset and creating the threads takes time (the bulk insert call was made in Python, because creating a thread in R consumes a lot of resources; the rericulate package was used to facilitate this interaction between R and Python).<\/p>\n Finally, columnar databases are very popular these days; they are designed more for data processing than for transactional purposes.<\/p>\n We can see that the columnstore is slightly faster than the classic rowstore. This is relevant because the greatest benefit of the columnstore comes from data retrieval rather than insertion, so it is highly recommended.<\/p>\n Bulk Insert is approximately 10 times faster for inserting data into a database.<\/p>\n Parallel insertion offers significant benefits, but the programming is complex; if anyone needs guidance, please don\u2019t hesitate to contact me.<\/p>\n Finally, the columnstore has some positive impact, but it is recommended because it improves read speed and is easy to configure.<\/p>\n Lastly: I\u2019m not sharing the code this time because I developed it for the Anasac analytics environment where I currently work, so it\u2019s not generic. However, if anyone needs help, please don\u2019t hesitate to contact me.<\/p>\n Translated with DeepL.com (free version)<\/p>\n","protected":false},"excerpt":{"rendered":"Bulk Insert vs. Insert<\/h3>\n
![\"\"](\"https:\/\/i0.wp.com\/www.geekosas.com\/wp-content\/uploads\/2022\/01\/1-1024x768.png?resize=1024%2C768&ssl=1\")
<\/p>\nParallel Insertion.<\/h3>\n
![\"\"](\"https:\/\/i0.wp.com\/www.geekosas.com\/wp-content\/uploads\/2022\/01\/2-1024x768.png?resize=1024%2C768&ssl=1\")
<\/p>\nTypes of Storage<\/h3>\n
![\"\"](\"https:\/\/i0.wp.com\/www.geekosas.com\/wp-content\/uploads\/2022\/01\/3-1024x768.png?resize=1024%2C768&ssl=1\")
<\/p>\nConclusions<\/h3>\n