Life, Work

Changing Hats

The usual contractor game of musical chairs has come and gone, leaving me thankfully employed but with a new company. Security+ and MCITP: SQL BI Developer certification testing is on the horizon now that things have settled down. So for something technical: today I was tuning an ETL process and discovered some major performance increases by changing the import method from using OPENROWSET BULK to BULK INSERT. My testing log (1.3G) imported with BULK IMPORT in about two thirds the time of the old OPENROWSET method. Admittedly, the gain was somewhat fabricated – why in the world was I trying to use a batch-less importer (OPENROWSET) to import 1.3G files?! When run times skyrocketed, I checked the application log to find numerous errors related to excessive paging. After reading some articles about performance tuning on MSDN I decided BULK IMPORT’s batching feature was what I needed to tame the excessively large files.