Writing new climate data to the database is one of the biggest bottlenecks in the project. Following my new strategy of “make it work first, and optimise later” I have been wallowing with write speeds of ~ 4 – 5 minutes per year of data. Functionally, this has worked fine, but I have been experimenting with bcp utility to see if we can speed this up.
“The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format”(1). Using this means I don’t have to iterate across the data array with individual INSERT statements. Instead, the data arrays are written into a csv file (~ 30 seconds, and potential to improve here as well) and the csv file is bulk loaded (~ 7 seconds). Total cost is now only < 40 seconds, down from ~ 5 min.