BCP queryout speed

• RD-201664

  Ten Centuries

  Points: 1334

  More actions

  November 18, 2009 at 11:47 am

  #206597

  Hi,

  I have a stored procedure that takes about 10 seconds to run directly on the server in QA. The proc is a series of select statements and only returns about 10 - 20 rows.

  If I use this same proc in a BCP queryout command, it takes 2.5 minutes to complete.

  sproc command in QA (takes 10 seconds):

  exec [MyDB].[dbo].[MySproc]

  BCP command (take 2.5 minutes):

  declare @sql varchar(200)

  select @sql = 'bcp "exec [MyDB].[dbo].[MySproc]" queryout \\server\share\filename.txt -c -T'

  exec master..xp_cmdshell @sql

  I can see the filename.txt is created immedialtely with 0 bytes. 2.5 minutes later the resutls are written to the file and the file is closed.

  Thanks for any help.

  When running the BCP command in QA, the output shows:

  NULL

  Starting copy...

  NULL

  13 rows copied.

  Network packet size (bytes): 4098

  Clock Time (ms.): total 10625

  NULL

• RD-201664

  Ten Centuries

  Points: 1334

  More actions

  November 19, 2009 at 10:38 am

  #1081712

  More strange findings when using BCP:

  The proc has a cursor. I added an audit log step to the proc so it inserts audit info to another table.

  When I run the proc from Query Analyzer it runs as expected and logs data for each step of the cursor as expected.

  When I run this using the above BCP command, it writes double records to the audit table:

  audit record 1

  audit record 2

  audit record 1

  audit record 2