Skip to Content.
Sympa Menu

freetds - [freetds] More freebcp -h changes

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • 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.

Top of Page