[freetds] BCP performance

Thompson, Bill D (London) ThompBil at exchange.uk.ml.com
Tue Jun 10 01:58:22 EDT 2003


Marc

No ideas offhand. 
You could TRY setting TDSDUMP, and then put in a smaller number of rows, say
20 or 50.
The timestamps on the logged messages in the dump file may start to give us
a clue about where the time is going....

Send me your table def and a text file of data (just zip it up and mail to
me direct)
I'll certainly give it a try myself - just for sanity's sake 

Bill

> -----Original Message-----
> From:	Cooperman, Marc (Exchange) [SMTP:MCooperman at bear.com]
> Sent:	Monday, June 09, 2003 9:00 PM
> To:	'FreeTDS Development Group'
> Subject:	RE: [freetds] BCP performance
> 
> Bill TDSDUMP is not defined. Any ideas?
> 
> -----Original Message-----
> From: Thompson, Bill D (London) [mailto:ThompBil at exchange.uk.ml.com]
> Sent: Monday, June 09, 2003 2:20 PM
> To: 'FreeTDS Development Group'
> Subject: RE: [freetds] BCP performance
> 
> 
> 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
> 
> _______________________________________________
> 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