freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
- From: Konrad J Hambrick <konrad AT jax01.payplus.com>
- To: freetds AT lists.ibiblio.org, Konrad J Hambrick <konrad AT jax01.payplus.com>
- Subject: [freetds] More freebcp -h changes
- Date: Mon, 15 May 2006 14:17:57 -0400
All --
I found that the freebcp -h handler in function bcp_options()
in dblib/bcp.c would only handle one, simple, scalar hint.
When there were multiple hints or if ORDER, ROWS_PER_BATCH or
KILOBYTES_PER_BATCH were invoked via the -h command line flag,
the bcp_options() function copied only the first hint KEYWORD
into the hint structure member (KEYWORD Only, no data went in).
I needed the ORDER( col1, col2, col3 ) hint for loading a set
of tables with (very) large data sets and the existing code in
bcp_options() did not work.
I found that if I pre-sorted the data 'offline' then loaded the
sorted .TXT files via BCP.EXE with the ORDER(...) hint that the
load time was reduced about 50-fold !
Without the ORDER(...) hint, the SQL Server Machine was all but
unusable AFTER BCP.EXE COMPLETED THE LOAD because SQL Server was
apparently building a clustered index in the background.
Same thing happened with freebcp.
Anyhow, the attached patch to bcp.c simply copies the pointer to
the -h commandline string into dbproc->bcpinfo->hint WITHOUT ANY
CHECKING !
A proper patch would require parsing the pdata->hint string then
checking the syntax of (possibly) a compound hint-string.
So ...
Is there already a 'simple text parser' somewhere in the freetds
source tree ?
If so, I could look at adding sanity checks for the hint string
using the existing text parser.
Otherwise, what I have done works for me and has reduced my load +
index time by a factor of fifty, not to mention that the ORDER(...)
hint leaves the server available for other users while I load data ... <G>
Thanks again for freetds !
-- kjh
*** bcp~01.c Mon Mar 20 08:29:17 2006
--- bcp.c Mon May 15 12:23:12 2006
***************
*** 604,610 ****
{
int i;
static const char *const hints[] = {
! "ORDER", "ROWS_PER_BATCH", "KILOBYTES_PER_BATCH", "TABLOCK", "CHECK_CONSTRAINTS", NULL
};
tdsdump_log(TDS_DBG_FUNC, "bcp_options(%p, %d, %p, %d)\n", dbproc, option, value, valuelen);
--- 604,615 ----
{
int i;
static const char *const hints[] = {
! "ORDER", /* special handling req for "ORDER( Col1, Col2, ..., Coln )" */
! "ROWS_PER_BATCH", /* special handling req for "ROWS_PER_BATCH = 1000000 */
! "KILOBYTES_PER_BATCH", /* special handling req for "KILOBYTES_PER_BATCH = 1000000 */
! "TABLOCK",
! "CHECK_CONSTRAINTS",
! NULL
};
tdsdump_log(TDS_DBG_FUNC, "bcp_options(%p, %d, %p, %d)\n", dbproc, option, value, valuelen);
***************
*** 619,634 ****
case BCPHINTS:
if (!value || valuelen <= 0)
return FAIL;
! for (i = 0; hints[i]; i++) { /* look up hint */
! if (strncasecmp((char *) value, hints[i], strlen(hints[i])) == 0) {
! dbproc->bcpinfo->hint = hints[i]; /* safe: hints[i] is static constant, above */
! return SUCCEED;
! }
! }
! tdsdump_log(TDS_DBG_FUNC, "failed, no such hint\n");
! return FAIL;
break;
default:
tdsdump_log(TDS_DBG_FUNC, "UNIMPLEMENTED bcp option: %u\n", option);
return FAIL;
--- 624,655 ----
case BCPHINTS:
if (!value || valuelen <= 0)
return FAIL;
+ /*
+ * the original hint handler only works for the first hint and only
+ * if the hint is a simple hint i.e. -h "ORDER( Col1, Col2, ... Coln )"
+ * or ROWS_PER_BATCH = 1000000 will not work because the following code
+ * copies only the single words 'ORDER' or 'ROWS_PER_BATCH' into the
+ * dbproc->bcpinfo->hint structure member.
+ *
+ * sigh ... Let SQL Server handle hint syntax ???
+ *
+ */
+ /* for (i = 0; hints[i]; i++) { *//* look up hint */
+ /* if (strncasecmp((char *) value, hints[i], strlen(hints[i])) == 0) { */
+ /* dbproc->bcpinfo->hint = hints[i]; *//* safe: hints[i] is static constant, above */
+ /* return SUCCEED; */
+ /* } */
+ /* } */
+ /* tdsdump_log(TDS_DBG_FUNC, "failed, no such hint\n"); */
+ /* return FAIL; */
+ /* break; */
! /* kjh A60515 - this works but does no hints testing at all ! */
!
! dbproc->bcpinfo->hint = value;
! return SUCCEED;
break;
+
default:
tdsdump_log(TDS_DBG_FUNC, "UNIMPLEMENTED bcp option: %u\n", option);
return FAIL;
- [freetds] More freebcp -h changes, Konrad J Hambrick, 05/15/2006
Archive powered by MHonArc 2.6.24.