PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Stephen dot Sprague at morganstanley dot com
Subject: RE: dbi and bulk insert
Date: Jun 27 2001 2:52PM

If I may pipe in (no pun intended) the real beauty of  the  bulk  insert
mechanism is to blast data into Sybase *without* logging. If you have  a
million rows you can forget about using the standard sql insert,  in  my
humble opinion.
 
set the bulk insert db option, drop the  indices,  blast  the  data  in,
recreate the indices and things work pretty darn good and fast!!
 
In other words, I contend the transformation of your 3gb  the  data  (in
memory, and using the Perl BCP API) and the use of the  non-logged  bulk
insert will be faster than the sql inserts any day  of  the  week  hands
down! (if the data is sorted first you might even save time on the 
create index step)
 
My 2 cents. :]
 
Cheers,
Steve
PS.
You might want to look at Sybase::BCP or Sybase::Xfer as alternatives to 
munging your data into another file and using Sybase's bcp command. These
modules allow you to do that in memory.



On Wed, 27 Jun 2001 @ 7:07am, an entity claiming to be Michael Peppler...:

mpeppl :Peter.Collard@barclayscapital.com writes:
mpeppl : > The main argument for implementing a bulk insert module is that bcp only
mpeppl : > takes data from files.
mpeppl : > 
mpeppl : > With large amounts of data (3gb) needing transposition before bcp'ing, I
mpeppl : > found that the I/O costs were quite high as the data had to be 1)read
mpeppl : > 2)written 3)read and 4)sent to ASE, which even with careful disk placement
mpeppl : > took some time (20 mins for steps 1& 2) and added complexity to the error
mpeppl : > handling,control and reporting.
mpeppl :
mpeppl :Agreed.
mpeppl :
mpeppl :But on most systems you can used a named pipe to avoid additional file
mpeppl :system space, and also to avoid a lot of IO (the IO becomes Unix type
mpeppl :sockets instead of file IO).
mpeppl :
mpeppl :Michael
mpeppl :
mpeppl : > -----Original Message-----
mpeppl : > From: Michael Peppler [mailto:mpeppler@peppler.org]
mpeppl : > Sent: 25 June 2001 17:55
mpeppl : > To: SybPerl Discussion List
mpeppl : > Subject: Re: dbi and bulk insert
mpeppl : > 
mpeppl : > 
mpeppl : > Stephen.Sprague@morganstanley.com writes:
mpeppl : >  > Do you know of any plans to augment DBI with a 'bulk insert' mechansism?
mpeppl : >  > I would think this would fall under this module, no?
mpeppl : > 
mpeppl : > I've not heard of any plans for any bulk insert mechanism in DBI. I
mpeppl : > could add something for the DBD::Sybase module specifically (i.e. in a
mpeppl : > non-portable way), but I've found that doing BCP style operations in
mpeppl : > perl is mostly a waste of time, and there are usually ways of
mpeppl : > achieving the same results by combining perl and Sybase's bcp utility.
mpeppl : > 
mpeppl : > However, my opinion could be changed by a good argument :-)
mpeppl : > 
mpeppl : > Michael
mpeppl : > -- 
mpeppl : > Michael Peppler - Data Migrations Inc. - mpeppler@peppler.org
mpeppl : > http://www.mbay.net/~mpeppler - mpeppler@mbay.net
mpeppl : > International Sybase User Group - http://www.isug.com
mpeppl : > *Looking for new project to tackle starting 8/1/01*
mpeppl : > 
mpeppl : > 
mpeppl : > ------------------------------------------------------------------------
mpeppl : > For more information about Barclays Capital, please
mpeppl : > visit our web site at http://www.barcap.com.
mpeppl : > 
mpeppl : > 
mpeppl : > Internet communications are not secure and therefore the Barclays 
mpeppl : > Group does not accept legal responsibility for the contents of this 
mpeppl : > message.  Although the Barclays Group operates anti-virus programmes, 
mpeppl : > it does not accept responsibility for any damage whatsoever that is 
mpeppl : > caused by viruses being passed.  Any views or opinions presented are 
mpeppl : > solely those of the author and do not necessarily represent those of the 
mpeppl : > Barclays Group.  Replies to this email may be monitored by the Barclays 
mpeppl : > Group for operational or business reasons.
mpeppl : > 
mpeppl : > ------------------------------------------------------------------------
mpeppl : > 
mpeppl :
mpeppl :