FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

I'm experiencing a significant bcp performance differential between FreeTDS
and Microsoft's libraries.  

On Windows NT, using Microsoft's BCP, I get:

$ bcp MSCI..vEQSecurityNames out vEQSecurityNames.txt -c -T 
...
63744 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total     3875 Avg        0 (16450.06 rows per sec.)

Under 4 seconds.  

With freebcp, I have to use TDS 7.0 and a slower machine running NetBSD:

$ time freebcp MSCI..vEQSecurityNames out vEQSecurityNames.txt -c -U$U -P$P
-S $S

Starting copy...

firstrow = 0 row_of_query = 63744 rows written 63744
63744 rows copied.

real    1m20.868s
user    0m38.845s
sys     0m0.348s

A little over 80 seconds, a little under 800 rows/second.  :-(

Logging is turned off; the network connections share the same wire; I
repeated the test a few times.  It's not a fluke.  

The table looks like this:

ColumnName     Type                      Length Nulls
-------------- ------------------------- ------ -----
SecurityID     int                            4   N
MonthStartDate datetime                       8   N
Sedol          varchar                        6   Y
Cusip          varchar                       10   Y
Capitalization money                          8   Y
Name           varchar                       32   Y

and the conversions are particularly expensive afaict.  I turned on logging
and added up the time to convert the first 5000 fields; it came to quite a
bit:

$ grep dbconvert dump |grep -E 'calling|outputting' | sed 's/^2003-02-04
16:11://' |head -10000 | perl -e'while(<>){ ($a)=split; if (/calling/)
{$start=$a; next} exit unless /outputting (\d+)/; printf "$start $a:  %f ms
for %d bytes\n", ($a-$start), $1; $fields++; $total += $a-$start; } END{
printf "%d fields in %f seconds\n", $fields, $total}' >ms
LowdenJK@ntc5003$ tail ms
31.171715 31.171820:  0.000105 ms for 13 bytes
31.204985 31.205113:  0.000128 ms for 3 bytes
31.205405 31.205514:  0.000109 ms for 6 bytes
31.205890 31.206706:  0.000816 ms for 16 bytes
31.206959 31.207066:  0.000107 ms for 13 bytes
31.209465 31.209587:  0.000122 ms for 3 bytes
31.209940 31.210054:  0.000114 ms for 6 bytes
31.210434 31.211244:  0.000810 ms for 16 bytes
31.211495 31.211618:  0.000123 ms for 13 bytes

5000 fields in 2.687157 seconds

Bill, Nick, anyone else:  Have you measured bcp throughput?  If you have the
same kinds of results, I don't see how FreeTDS can be used for serious bcp
work.  

--jkl



The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s)
named above.  If you are not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient, any
review, dissemination, distribution or duplication of this communication is
strictly prohibited. If you are not the intended recipient, please contact
the sender immediately by reply e-mail and destroy all copies of the
original message. Please note that we do not accept account orders and/or
instructions by e-mail, and therefore will not be responsible for carrying
out such orders and/or instructions.