copy large blob table

Question

Post reply

copy large blob table

obeacons

SSC-Addicted

Points: 438
More actions
July 7, 2011 at 4:56 pm

#243945

What is the fastest/most efficient way to transfer/copy data from a table including blob data. The table is around 70GB of which 99% is blob data. Using the data copy wizard or a select into statement takes eons.
thanks
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Where are you transferring the data to? Same database, different database, same server, different server?

What is the table definition?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

obeacons SSC-Addicted Points: 438 More actions · Answer 2

I want to transfer it to a different database on another server, but for my testing so far i am just using a different database on the same server. The blobs are no larger than 512K each.

The table definition looks something like this.

CREATE TABLE Blob(

BlobId BIGINT IDENTITY(1,1),

Data VARBINARY(MAX)

)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

Does the target database have pre-allocated storage large enough to store the data?

Are the databases stored on different hardware or the same?

What is the maximum throughput of the storage system?

Have you tried using bulk logged recovery on the target database and a select into construct?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

obeacons SSC-Addicted Points: 438 More actions · Answer 4

By pre allocated storage do you mean setting the database file size? I have not tried that, but will do so.

The servers are on the same hardware (virtualized). This is an ESX server running vm ware. The vm is 16GB ram dual core xeon. I'm not sure what the throughput is.

The recovery mode is full right now, and I have tried select into. I can try the bulk logged recovery. Would it really make that much of a difference?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

Bulk logged with select into will make a difference, yes - don't use insert into.

You have to remember though that with that setup, you are going to thrash the disk to hell, so you will need to be patient.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

obeacons SSC-Addicted Points: 438 More actions · Answer 6

Looks like with SELECT INTO, the table cannot exist already and the destination will be a partitioned table in my scenario. I realized that the select into is fast because of minimal logging under simple and bulk logged. So i did some research and I was able to achieve a similar insert time using INSERT INTO .. SELECT with a TABLOCK hint.

Thanks for pointing me in the right direction. Btw, pre sizing the db made a big difference as well (shaved 3 min off total time).