SQLServer – BCP Optimization via Running Multiple Instances in Parallel

Background

Here is another in our series “Optimizing SQL Server BCP”.

 

Code

Original Code

 

invokeBCP.cmd

The original code looks like this:


	bcp %DBTargetDB%.dbo.[tblUser] in %TEMP_SESSION%\dbo.tblUser.data -T -E -n -b%BATCH_SIZE% -S%DBTargetServer%
	bcp %DBTargetDB%.dbo.[tblInternalUser] in %TEMP_SESSION%\dbo.tblInternalUser.data -T -E -n -b%BATCH_SIZE% -S%DBTargetServer%
	bcp %DBTargetDB%.dbo.[tblUserDefaultView] in %TEMP_SESSION%\dbo.tblUserDefaultView.data -T -E -n -b%BATCH_SIZE% -S%DBTargetServer%
	bcp %DBTargetDB%.dbo.[tblUserBusinessUnitAccess] in %TEMP_SESSION%\dbo.tblUserBusinessUnitAccess.data -T -E -n -b%BATCH_SIZE% -S%DBTargetServer%
	bcp %DBTargetDB%.dbo.[tblUserLocationCountryAdmin] in %TEMP_SESSION%\dbo.tblUserLocationCountryAdmin.data -T -E -n -b%BATCH_SIZE% -S%DBTargetServer%
	bcp %DBTargetDB%.dbo.[tblLocationBusinessUnit] in %TEMP_SESSION%\dbo.tblLocationBusinessUnit.data -T -E -n -b%BATCH_SIZE% -S%DBTargetServer%

Revised Code

Here is the revised code:

 

waitOnChildTasks.cmd

@echo off
setlocal

	@REM Reset Error Level
	set ERRORLEVEL=
	set ERRORLEVEL_LOCAL=

	set SLEEP_TIME_IN_SECONDS=5
	set "EXE=bcp.exe"

	:WAITLOOP

		@REM Using TaskList get list of matching processes
		@REM Send list to FIND.exe
		@REM Find returns 0 if match found, else 1
		tasklist /FI "IMAGENAME eq %EXE%" 2>NUL | find /I /N "%EXE%">NUL

		@REM Save ERRORLEVEL into ERRORLEVEL_LOCAL
		set "ERRORLEVEL_LOCAL=%ERRORLEVEL%"

		@REM If ERRORLEVEL_LOCAL is 0, then goto RUNNING BLOCK
		if %ERRORLEVEL_LOCAL% equ 0 goto :RUNNING

		@REM Else, assume not running
		goto :NOTRUNNING

	:RUNNING

		echo "Running  - %EXE% @ Check performed @ %time%"

		@Rem sleep for SLEEP_TIME_IN_SECONDS seconds
		Echo "Sleeping for %SLEEP_TIME_IN_SECONDS% (secs)"
		sleep %SLEEP_TIME_IN_SECONDS%

		goto WAITLOOP

	:NOTRUNNING

		echo "Completed Check for determining whether %EXE% is running!"

endlocal

invokeBCP.cmd

	echo "Bulk coppying data from datafiles " @ %DATE% %TIME% " ...."

	start bcp %DBTargetDB%.dbo.[tblUser] in %TEMP_SESSION%\dbo.tblUser.data -T -E -n -b%BATCH_SIZE% -S%DBTargetServer%
	start bcp %DBTargetDB%.dbo.[tblInternalUser] in %TEMP_SESSION%\dbo.tblInternalUser.data -T -E -n -b%BATCH_SIZE% -S%DBTargetServer%
	start bcp %DBTargetDB%.dbo.[tblUserDefaultView] in %TEMP_SESSION%\dbo.tblUserDefaultView.data -T -E -n -b%BATCH_SIZE% -S%DBTargetServer%
	start bcp %DBTargetDB%.dbo.[tblUserBusinessUnitAccess] in %TEMP_SESSION%\dbo.tblUserBusinessUnitAccess.data -T -E -n -b%BATCH_SIZE% -S%DBTargetServer%
	start bcp %DBTargetDB%.dbo.[tblUserLocationCountryAdmin] in %TEMP_SESSION%\dbo.tblUserLocationCountryAdmin.data -T -E -n -b%BATCH_SIZE% -S%DBTargetServer%
	start bcp %DBTargetDB%.dbo.[tblLocationBusinessUnit] in %TEMP_SESSION%\dbo.tblLocationBusinessUnit.data -T -E -n -b%BATCH_SIZE% -S%DBTargetServer%

	Rem wait on all child processes
	waitOnChildTasks.cmd

	echo "Bulked copied data from datafiles " @ %DATE% %TIME% 

Environment

SLEEP.EXE

Depending on your version of MS Windows, ours is MS Windows 2008/R2, you might get an error stating that

‘sleep’ is not recognized as an internal or external command, operable program or batch file.

If so, please download “Windows Server 2003 Resource Kits” from http://www.microsoft.com/en-us/download/details.aspx?id=17657.

And, perform a deep extract to get you a nice version of the sleep.exe

 

One thought on “SQLServer – BCP Optimization via Running Multiple Instances in Parallel

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s