Link to home
Create AccountLog in
Avatar of MikeDelaney
MikeDelaneyFlag for United States of America

asked on

Copying large records takes too lonk

Hi SQL Server Guru's.

I have a project that is copying the data in a table (this include an image so, these records a large (anywhere from 12 to 30 K per row).   And there are about 1million records.  
SELECT * INTO TEMP_TABLE FROM IMAGE_TABLE
This process is taking about 24 hours.  Another table with a relatively small row size copies in a few minutes.

I'm guessing that this is because it can only grab so much data in the copy and has to "make multiple trips back to the source" per record.  But, that's just a guess.

If that's close to accurate, is there a way to adjust the memory caching in an insert so that the more data  can be copied per packet?

Or, if I'm way off the mark, can someone explain why this takes so long?

Hope someone can help.
Avatar of lcohan
lcohan
Flag of Canada image

I suggest use SSIS Data transfer task to do it and you must see at least some improvement.
You can do just a quick test by using the wizzard from SSMS to do a EXPORT or IMPORT.

Also think that image data(and text/ntext) is stored differently than regular data types in SQL plus I bet if you look at your database T-log/MDF files there must be some (significant) growth during this transfer.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account