0

I have a batch file, which is used to copy data from a database. The database has over 75 million records.

So I have made 8 output files.. each of 10 million retrieval range.

Tha Batch file is as:

echo off REM -t"^^" -r"~`~" "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp" "DatabaseD1.dbo.TOOLS_AUDIT" out audit_b1.dat -S"LDNPCM05927V05B\DSDCU_MAIN2_UAT" -UUserID -PPassword-c -F1 -L10000000 -t"^^" -r"~`~" REM "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp" "DatabaseD1.dbo.TOOLS_AUDIT" out audit_b2.dat -S"LDNPCM05927V05B\DSDCU_MAIN2_UAT" -UUserID -PPassword-c -F10000001 -L20000000 -t"^^" -r"~`~" rem "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp" "DatabaseD1.dbo.TOOLS_AUDIT" out audit_b3.dat -S"LDNPCM05927V05B\DSDCU_MAIN2_UAT" -UUserID -PPassword-c -F20000001 -L30000000 -t"^^" -r"~`~" rem "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp" "DatabaseD1.dbo.TOOLS_AUDIT" out audit_b4.dat -S"LDNPCM05927V05B\DSDCU_MAIN2_UAT" -UUserID -PPassword-c -F30000001 -L40000000 -t"^^" -r"~`~" rem "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp" "DatabaseD1.dbo.TOOLS_AUDIT" out audit_b5.dat -S"LDNPCM05927V05B\DSDCU_MAIN2_UAT" -UUserID -PPassword-c -F40000001 -L50000000 -t"^^" -r"~`~" rem "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp" "DatabaseD1.dbo.TOOLS_AUDIT" out audit_b6.dat -S"LDNPCM05927V05B\DSDCU_MAIN2_UAT" -UUserID -PPassword-c -F50000001 -L60000000 -t"^^" -r"~`~" rem "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp" "DatabaseD1.dbo.TOOLS_AUDIT" out audit_b7.dat -S"LDNPCM05927V05B\DSDCU_MAIN2_UAT" -UUserID -PPassword-c -F60000001 -L70000000 -t"^^" -r"~`~" rem "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp" "DatabaseD1.dbo.TOOLS_AUDIT" out audit_b8.dat -S"LDNPCM05927V05B\DSDCU_MAIN2_UAT" -UUserID -PPassword-c -F70000001 -L80000000 -t"^^" -r"~`~"

pause

The method that I am following is: I am commenting the last 7 statements using rem, and running only the first line..and then commenting all lines except the second line and running the batch file..and so on.. until the last line..

For the first line, it took me 45 minutes to copy records. For the second line, it took me 1 hour 20 minutes.. For the third line.. 2 hours 5 minutes.. and so on... with time increasing each time.

Could someone suggest any ways to optimise such batch files?

Best Regards Asutosh

Improve this question

2 Answers 2

Reset to default
1

I want to state two points about your question:

1- You may automate the execution of your Batch file in the way showed in the Batch file below:

echo off setlocal EnableDelayedExpansion REM -t"^^" -r"~`~" set start=1 for /L %%i in (1,1,7) do ( "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp" "DatabaseD1.dbo.TOOLS_AUDIT" out audit_b%%i.dat -S"LDNPCM05927V05B\DSDCU_MAIN2_UAT" -UUserID -PPassword-c -F!start! -L%%i0000000 -t"^^" -r"~`~" set /A start+=10000000 ) pause

This Batch file executes the 1..8 steps in one run... If this solution is not suitable for you, the Batch file may be modified to run only one step indicated in a parameter (ask for it, if you want it).

2- The problem with the execution times is not related to Batch files (and I don't know sql), so you should follow the advices of other people at this respect.

I hope it helps...

Antonio

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Anotnio, could you also post your modified batch file, which you were suggesting? Thanks :)
0

Don't use -F and -L parameters, because in your case bcp scans every time the table to row "F" and it takes a time. Just export whole table. Or consider to use partitioning and "sliding window patitioning schema".

Improve this answer

2 Comments

Hi Eduard, I have considered the same, but keeping 80 Mils record in one file would not be feasible, I believe.. But thanks anyways... I have very less idea about partitioning and sliding window partioning schema.. Could you please suggest.. :) Thanks
@Ashutosh: here can find more about "sliding window": sqlskills.com/resources/whitepapers/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.