I got like 25,000 JSON files whose size (in average) is 1.2 MB (also, the average of records per JSON is 5,000), and the total size is like 34 GB. I have been reviewed the BCP utility from SQL Server, especially the SqlBulkCopy class in C# (I'm going forward to use C# because these files are actually dumped in a Azure Blob Storage), and thinking the approach that I'd take for process these files: I'd iterate over files, and download 200 files in each iteration, then merge them into a collection, so according to the estimation above I'd have 1,000,000 records to insert in each iteration (1 file has in average 5,000 records, so 200 x 5000 = 1,000,000)... now, once I got the records to be inserted, I'd use the SqlBulkCopy class for insert these records... my doubt comes here: for improve the bulk insertion performance, I could hold a table lock during the insertion by passing some parameter when instantiating the SqlBulkCopy class (which is legal in my scenario, due to this program would be used for ETL purpose), and I could send the records in multiple batches if I set the BatchSize property... but what would be the best size for the batch? Send all the records in a single batch would cause any sort of overhead.

Note: the JSON schema is actually simple: an array of objects (it doesn't have nested objects), and each object has like 7 - 8 fields.

  • I would shoot for about 10K records per batch. Don't forget to up-scale your AzureDB during the insert or you will spend an eternity hitting the IO limits during the bulk load. – Jonathan Fite Aug 5 '19 at 3:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.