Skip to Content.
Sympa Menu

freetds - RE: [freetds] Bulkcopy in ct-lib

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Lowden, James K" <LowdenJK AT bernstein.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] Bulkcopy in ct-lib
  • Date: Thu, 5 Feb 2004 16:18:20 -0500

> 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








Archive powered by MHonArc 2.6.24.

Top of Page