Currently, the climate chip package has two major bottlenecks: the calculation of WBGT and the database write. Both of these processes take 4 – 5 min per year of data. At the moment the database write is a simple iteration with individual insert operations. While this is working, there is plenty of room for improvement. In this post I investigate the performance of SQL Server’s bcp Utility, which
[bcp] copies data between an instance of Microsoft SQL Server and a data file in a user-specified format.
bcp is a command line utility which can be called using the following command:
bcp <database.owner.tableName> in <inputFile> -T -S <serverInstance> -c -r\n -t,
I had a number of errors initially. Firstly, the serverInstance must be included. Secondly (and more difficult to find a solution for), bcp was returning an Unexpected EOF encountered in BCP data-file exception. A quick google showed that this was a common error, but many of the solutions didn’t work for me. Finally I came upon this post which related this exception to IDENTITY columns within the table. A quick test on a table without IDENTITY columns confirmed that this was the problem all along. I tried some of the suggestions for by-passing the IDENTITY issue (SET IDENTITY_INSERT tableName ON, using the -E flag in bcp) but none worked. Thankfully, the climate database doesn’t contain any IDENTITY columns, so this shouldn’t be a problem in the live package.