[freetds] Bulkcopy in ct-lib

Lowden, James K LowdenJK at bernstein.com
Thu Feb 5 16:18:20 EST 2004


> From: Frediano Ziglio [mailto:freddyz77 at tin.it]
> Sent: February 5, 2004 10:37 AM
> Il mer, 2004-02-04 alle 22:05, Lowden, James K ha scritto:
> > 
> > The best alternative I've come up with is to support 
> bcp_moretext().  That would require, ISTM, some kind of 
> "bcp_bind_tmpfile()", something that associates a chunk of a 
> file with a column.  The row image need not be precomposed in 
> memory; rather, a "row image description" -- a list of 
> pointers/handles and lengths of post-iconv, post-dbconvert, 
> server-ready data -- would be enough.  
> > 
> 
> I didn't understand this paragraphs that much... What do you mean by
> "something that associates a chunk of a file with a column" ?

bcp_bind() associates a buffer in a host variable with a column.  When the column is sent (with bcp_sendrow), the data are read from the buffer.  

For text/binary data, I propose bcp_bind_tmpfile() to do the same thing, but using a workfile instead of a memory buffer.  The column's data would be held in a workfile, where it wouldn't tax virtual memory.  

My other point is that we don't have to allocate a buffer to hold the row image, before sending it to the server.  It's enough to know where each column's data will be read from.  

> > 	for each column {
> > 		bcp_bind() or bcp_bind_tmpfile()
> > 	}
> > 	for each row {
> > 		for each column {
> > 			for each chunk {
> > 				fread() /* to iconv input */
> > 				if (char type)
> > 					iconv() /* to temp buffer */
> > 				if (varbinary etc.) {
> > 					fwrite() /* no dbconvert */
> 
> fwrite where ? to a temporary file ?

Yes.  That's what I mean by a "workfile".  

> > 				}
> > 			}
> > 			dbconvert() /* to bound buffer */
> > 		}
> > 		bcp_sendrow()
> > 		for each such column {
> > 			bcp_moretext()
> > 		}
> > 		if (! nrows % batchsize == 0)
> > 			bcp_batch()
> > 	}
> > 	bcp_done()
> > 
> > The subtle part being that bcp_sendrow() and bcp_moretext() 
> are really front-ends for a _bcp_send_columns() that 
> interleaves the columns correctly and ships the packet(s).  
> > 
> 
> The harder thing it's length. You need first to send length and then
> data. To know length you need to scan all column if terminated or to
> convert to proper charset if we need to convert it (text). 

Exactly.  The server wants each column's length before its data.  That's why we read the whole row, passing each column's data (depending on the datatype) through iconv() and dbconvert().  The server-ready data are parked, either in memory or on disk, and the sizes noted.  When the whole row has been read from the file, processed, and parked, we're ready to call bcp_sendrow(), which reads the prepared data and writes them column-by-column to the server.  

> If we know length before we can just copy from file to wire. 

No, we can't.  If the row crosses packet boundaries, we'd send the first part of the row before we're done reading the rest of it from disk.  If we encounter a problem -- iconv(), unexpected EOF -- we have no way to retract the partial row from the server, and no clean way to end the session.  

> I don't think it's
> good to use temporary files just to avoid fseek... perhaps it would be
> better to test if we can fseek and then use temporary file.

I'm not proposing to use temporary files just to avoid fseek().  I'm saying:

1.  We need then to support bcp_moretext(), because there's no reason to allocate memory for very large columns.  
2.  To do that, we have to restructure bcp_exec(), something we'll have to work on anyway when we move bcp wire handling to libtds.  
3.  When we restructure bcp_exec(), we get an opportunity to reorder the processing, such that "measuring" the data file is no longer needed.  It's that "measuring" that requires fseek(3).  

If you're worried about throughput, don't.  :-)  Disk I/O is orders of magnitude faster than network I/O.  We involve workfiles only for huge columns, and we need them for text/ntext, if we're going to pass the data through iconv().  

Is that any clearer?  

--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.
If you, as an intended recipient of this commercial e-mail or advertisement, would not like to receive further e-mail correspondence from the sender, please "reply" to the sender indicating your wishes.
In the U.S.: 1345 Avenue of the Americas, New York, NY 10105






More information about the FreeTDS mailing list