Experiments / Progess / Project 702 / Project Challenges / Uncategorized

Using bcp Utility

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.

More Information
(1) http://technet.microsoft.com/en-us/library/ms162802.aspx
(2) http://sqlfool.com/2008/12/bcp-basics/
(3) https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s