FreeTDS Development Group

["Richard C. Pollock" <rcp-conseaco AT worldnet.att.net>]

-----Original Message-----
From: freetds-admin AT lists.ibiblio.org
[mailto:freetds-admin AT lists.ibiblio.org]On Behalf Of Mitchell Seltzer
Sent: Friday, November 22, 2002 7:44 PM
To: freetds AT lists.ibiblio.org
Subject: RE: [freetds] FW: bcp -h


Please Remove me from the list

-----Original Message-----
From: freetds-admin AT lists.ibiblio.org
[mailto:freetds-admin AT lists.ibiblio.org]On Behalf Of Lowden, James K
Sent: Friday, November 22, 2002 1:23 PM
To: 'freetds AT lists.ibiblio.org'
Subject: [freetds] FW: bcp -h


> Does anyone have any information about the TDS implementation of bcp
> hints?
>
> I think I know how to implement them.  The magic appears in the "insert
> bulk" statement:
>
> i.n.s.e.r.t...b.u.l.k...t.e.s.t.d.b.....a.(.a...i.n.t.).w.i.t.h.(.T.A.B.L.
> O.C.K.).
>
> That "with TABLOCK" is a hint.  A description of the hints from the
> documentation follows.  Hint support would be useful.
>
> There's an interesting dilemma, though:  Microsoft doesn't define a db-lib
> way to set hints.  There's really no technical reason not to, but we'll
> have to extend their spec.
>
> Microsoft lets you pass bcp hints via the ODBC bcp_control function, by
> passing the literal hint string.
>
> The db-lib version of bcp_control matches Sybase's.  It accepts a small
> domain of integers (and no strings).  I propose we define an integer
> constant for every available hint, and pass it that way.  When
> _bcp_start_copy_in() builds the insert statement, it will look up any
> hints and tack them on.
>
> The above expresses pretty much everything I know on the subject.  If you
> know anything else on the subject, please do share.  :-)
>
> --jkl
>
> (The Microsoft bcp utility has a "-h" (hint) switch. )
>
> [begin doc]
> The switch is defined as:
>
> -h "hint [,...n]"
>
> Specifies the hint(s) to be used during a bulk copy of data into a table
> or view. This option cannot be used when bulk copying data into SQL Server
> 6.x or earlier.
>
> Hint Description
>
>       ORDER (column [ASC | DESC] [,...n])
>       Sort order of the data in the data file. Bulk copy performance is
> improved if the data being loaded is sorted according to the clustered
> index on the table. If the data file is sorted in a different order, or
> there is no clustered index on the
> table, the ORDER hint is ignored. The names of the columns supplied must
> be valid columns in the destination table. By default, bcp assumes the
> data file is unordered.
>
>       ROWS_PER_BATCH = bb
>       Number of rows of data per batch (as bb). Used when -b is not
> specified, resulting in the entire data file being sent to the server as a
> single transaction. The server optimizes the bulk load according to the
> value bb. By default, ROWS_PER_BATCH is unknown.
>
>       KILOBYTES_PER_BATCH = cc
>       Approximate number of kilobytes (KB) of data per batch (as cc). By
> default, KILOBYTES_PER_BATCH is unknown.
>
>       TABLOCK
>       A table-level lock is acquired for the duration of the bulk copy
> operation. This hint significantly improves performance because holding a
> lock only for the duration of the bulk copy operation reduces lock
> contention on the table. A table can be loaded by multiple clients
> concurrently if the table has no indexes and TABLOCK is specified. By
> default, locking behavior is determined by the table option table lock on
> bulk load.
>
>       CHECK_CONSTRAINTS
>       Any constraints on the destination table are checked during the bulk
> copy operation. By default, constraints are ignored.
> [end doc]
>
>
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

_______________________________________________
freetds mailing list
freetds AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds