Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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.
0
MikeDelaney
Asked:
MikeDelaney
1 Solution
 
lcohanDatabase AnalystCommented:
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.
0
 
Scott PletcherSenior DBACommented:
I suspect it's log file growth/expansion that is really killing your performance.

You need to pre-allocate enough log space to handle the entire INSERT.

Also, using BULK INSERT might give you better performance and would definitely give you better control over how the table is loaded: batch size, do triggers fire, etc..
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now