[freetds] BCP performance

Thompson, Bill D (London) ThompBil at exchange.uk.ml.com
Mon Jun 9 14:20:01 EDT 2003


Marc,

You haven't got TDSDUMP variable defined have you ?

I have used freebcp on Solaris to move 500Gb of data into a  SQL 2000
database, so I know the code ain't slow. something else is interfering....

Bill

> -----Original Message-----
> From:	Cooperman, Marc (Exchange) [SMTP:MCooperman at bear.com]
> Sent:	Monday, June 09, 2003 7:09 PM
> To:	'FreeTDS Development Group'
> Subject:	RE: [freetds] BCP performance
> 
> 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.
> 
> -Marc
> 
> 
> 
> -----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:
> 
> ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/current/freetds-pre-utf8.tgz
> 
> 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.
> 
> http://www.w3.org/Protocols/HTTP/Performance/Nagle/
> 
> 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.  
> 
> --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.
> 
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 
> 
> ****************************************************************
> 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.
> ***********************************************************************
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds




More information about the FreeTDS mailing list