SQL Server Performance

BCP and reset batch size

Discussion in 'SQL Server 2008 General Developer Questions' started by odeddror, Mar 6, 2010.

  1. odeddror New Member

    Hi there,
    I'm using SQL Server 2008 and Windows Vista X64
    I run this bcp on a table with 8.2 million rows
    Exec Master..xp_cmdshell
    'bcp "Select * from Coronado.dbo.logprogressnotes" queryout "C:Tempsqlexport.txt" -c -T -Slocalhost'
    Here are the results:1000 rows successfully bulk-copied to host-file. Total received: 8289000
    NULL
    8,289,683 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total : 88219 Average : (93,967.09 rows per sec.) 1min 28 sec.
    NULL
    C:TempSQLExport.txt 1,599,460 KB -> 1.6GIG
    Now I modified the command by adding -b1000 to it and it took 4.5 min to run and 28,000 rows per secoond
    Since then every time I run this command without -b it allways show me 1000 rows and is 3 time slower
    My question is how to reset the bcp that way it will run with no batch size limit
    Like it was in the first time I run the command? like reset the utility
    Thanks,
    Oded Dror
  2. moh_hassan20 New Member

    [quote user="odeddror"]My question is how to reset the bcp that way it will run with no batch size limit [/quote]
    don't use -b option, in that case bcp insert all rows as one transaction , with better performance.
    For -b size , as size increase , performance is better

Share This Page