Exporting varbinary(max) column take a whole day

• williamaffandi

  SSC Enthusiast

  Points: 103

  More actions

  February 7, 2011 at 9:44 pm

  #233978

  Hi,

  I have some trouble exporting a table with a varbinary(max) column in it.

  The table currently have 530 rows ( and expanding)

  Each of the varbinary(max) column have around 44000 characters.

  And whenever I use SSIS to export the table to another DB, It take a whole day until it's finished.

  I find it weird, coz the table itself just have about 400KB Disk Usage.

  If I try a "Select * from the table", It cost me 1minutes and 12sec to finished show up the content. (I wonder what took him so long?)

  Any1 can advise what to do on this case ?

  Thank you for any help and comment.

  FYI, the table have PK and so it have Index.

  Thanks.....

• Geoff A

  SSChampion

  Points: 11511

  More actions

  February 8, 2011 at 8:14 am

  #1283720

  williamaffandi (2/7/2011)

  ---

  Hi,

  I have some trouble exporting a table with a varbinary(max) column in it.

  The table currently have 530 rows ( and expanding)

  Each of the varbinary(max) column have around 44000 characters.

  And whenever I use SSIS to export the table to another DB, It take a whole day until it's finished.

  I find it weird, coz the table itself just have about 400KB Disk Usage.

  If I try a "Select * from the table", It cost me 1minutes and 12sec to finished show up the content. (I wonder what took him so long?)

  Any1 can advise what to do on this case ?

  Thank you for any help and comment.

  FYI, the table have PK and so it have Index.

  Thanks.....

  first off, the PK is meaningless when you "Select * from the table". you need to include a where statement and reference the PK to use it.

  second, what is the reason for the export? is it a one time thing? a daily thing?

  i would try to see what kind of performance you can get using BCP

• williamaffandi

  SSC Enthusiast

  Points: 103

  More actions

  February 9, 2011 at 1:09 am

  #1284248

  Thx for the reply,

  Yes, it's a daily Activity (once a day). So I want to make it run automatically using Agent Job.

  The reason to do this, because I have to distribute the table to other several DBs on remote Servers.

• Jeff Moden

  SSC Guru

  Points: 1004343

  More actions

  February 13, 2011 at 10:31 am

  #1286166

  In that case, I recommend reading up on BCP exporting as a "Native" format in Books Online.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)