Ken,

I do quite a bit of BCPing data in and out. The batchsize can pretty drastically affect the performance. In general for narrow tables I usually use something on the order of 40-50k batches. For wide tables I go down to about 1k. But these processes I run are typically dealing with 100k to 10 million rows.

The size of the table in SQL also affects how long a count(*) is going to take, if the table is several million rows it can take a few seconds for that answer to come back, so slamming the server to do read i/o on one thread while another thread is doing write i/o may really affect things more than your test cases are running into.

>David -- I found that two factors control the speed of the process: the polling interval and the record count in each transaction. The factor that affected the process the most is the number of records in each transaction of the bcp event.
>The test set of data has 29000 records: it's rather small. The SQL table getting the data has 109 fields of mixed data types. It works fine for testing the engine, but a substantially larger set is needed for serious load testing of the process. Given that, these are the results of my tests:
>1. Baseline - Doing a "non-interupted" import takes 1 minute. By non-interupted, I mean one in which there is no progress polling and just one transaction.
>2. Changing Transaction size only - Importing the same data with 2000 record transactions, with the keyword "BATCHSIZE = 2000" and no progress polling, the time to import dropped to 15 seconds. Time reduced by a factor of 4!
>3. Changing Transaction size with polling - Importing the data with 2000 record transactions and an interval of .1 seconds resulted in an import time of 17 seconds. With no polling interval, the import time increased to 23 seconds. Changing to greater polling intervals did not appreciably affect performance. Two second penalty for polling, or 13% increase in the time to perform the BCP.
>I can't tell from the size of my data if changing the polling interval will have a significant effect on the speed of the BCP. I was surprised at how much a difference the BATCHSIZE keyword had on the process.