Post reply

Exporting varbinary(max) column take a whole day

  • 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.....

  • 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

  • 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.

  • 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)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply