[freetds] BCP performance

Cooperman, Marc (Exchange) MCooperman at bear.com
Mon Jun 9 14:08:48 EDT 2003

I've confirmed a slow bcp w/ released freetds 0.61.

I have not been able to build a snapshot of 0.62 due to problems with
'iconv' - I think I am seeing others with prolems there.

This is an ASCII file containing data going into Datetime, Int and Float(24)
table columns.

time src/apps/freebcp ategexdb.dbo.TICKMIN in tickagg.txt -c -b 1000
-Uauthid -Ppassword -Smyserver

gives me 

2291 rows copied.
42.76u 1.68s 1:14.39 59.7%

It's slower than molasses in January ;)

If someone wants to try and replicate it I could send you a table definition
and a sample data file. Just speak up.


-----Original Message-----
From: Lowden, James K [mailto:LowdenJK at bernstein.com]
Sent: Friday, June 06, 2003 1:26 PM
To: 'FreeTDS Development Group'
Subject: RE: [freetds] BCP performance

> From: Cooperman, Marc (Exchange) [mailto:MCooperman at bear.com]
> Sent: June 6, 2003 11:39 AM
> I've seen some postings that freebcp is slow.
> I can testify to this for Solaris -> Windows.

What sort of problem are seeing?  Is it affected by datatype?  

Raw protocol performance hasn't been a problem since we increased the packet
size to 4096 from 512.  Since 19 March 2003, freebcp has sported the -A flag
to adjust the packet size.  Native mode (-n) transfers are comparable in
speed to Microsoft's.  

In February, I identified our character->numeric conversion as a bottleneck
in bcp loads.  Frediano fixes that in March.  When I tested freebcp's
performance after that, I found we were within 150% of Microsoft's time.
Further improvements can be had in datetime conversions (using 64-bit
integers) and reducing the number of buffer copies.  

These changes all went into effect since the 0.61 release, afaict from
ChangeLog.  Normally I'd advise you just to grab the latest snapshot, but
unfortunately freebcp is a little broken at the moment and might not load
your table.  (The problem seems to be related to table width; sorry I can't
be more specific.)  Still, you might want to give it a try.  I don't expect
it to stay broken for long, and I suspect that the problem we recently
uncovered is in fact longstanding.  You could at least use it to confirm
what I'm saying about performance being a (largely) solved problem.  

An alternative:


That's the state of the project before I introduced design changes intended
to support UTF-8 clients.  It definitely includes the changes cited above.
The project wasn't in any special state at that time; it was just it's
typical, pretty reliable self.  If it doesn't work for you, your only
upgrade path is current CVS, and to bear with us while we work out the
kinks.  nn

> Perhaps turning off
> Nagle at the sender/client might help.


I don't think that will turn out to be related.  One thing TDS does not do
is send small packets, especially not small data packets.  

Hope this helps.  

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.

FreeTDS mailing list
FreeTDS at lists.ibiblio.org

Bear Stearns is not responsible for any recommendation, solicitation, 
offer or agreement or any information about any transaction, customer 
account or account activity contained in this communication.

More information about the FreeTDS mailing list