Skip to Content.
Sympa Menu

freetds - [freetds] BCP performance

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Lowden, James K" <LowdenJK AT bernstein.com>
  • To: "'TDS Development Group'" <freetds AT lists.ibiblio.org>
  • Subject: [freetds] BCP performance
  • Date: Tue, 4 Feb 2003 17:19:47 -0500

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.






Archive powered by MHonArc 2.6.24.

Top of Page