Copying large records takes too lonk
Posted on 2013-05-15
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.