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.