1

I have a table (A) with 112000+ records and 38 columns, including a BLOB one (PDF/PNG files with max size 2Mb). The table have around 13Gb data space and almost 9Gb Index space. For normalization purposes, I've created another table (B), to store these BLOB data. Table A will have a FK from table B.

I'm having problems while inserting data from table A into table B... it just takes too long. I've tried SELECT INTO and the Export wizard. First choice ran for 2h, and I have no idea how much longer it would take. Second choice failed while exporting (managed to insert around 35000 records).

So, my question is: is there a faster way? Is there anything I can do so I can accomplish this? I mean, 112000 records doesn't seem too much, despite the very large disk space.

pb2q
  • 58,613
  • 19
  • 146
  • 147
paezinc
  • 339
  • 1
  • 3
  • 13

2 Answers2

1

While it's not clear why your load is failing, if you are attempting to load the data in one large INSERT statement performance will imporve if you break the work into chunks. Don't do it one row at a time. Try loading rows that total about 10M, 50M, 100M, 250M, etc. until you find an efficient "sweet spot".

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • Thanks @PhilipKelley. Anyway, how can I find where the problem is (if there's any)? Could I improve it with some Index optimization? – paezinc Oct 01 '12 at 22:47
  • Indexing slows down inserts -- not only do you have to add a row, you have to slot it properly in to **each** index. As @Kumar says, dropping and rebuilding indexes can improve performance. It's not practical on a "live" database, but for background or overnight processes it's a standard technique. – Philip Kelley Oct 02 '12 at 14:06
  • As for where the performance hit is, look to your database transaction log file. Anything you change in the database is also changed there as well. If you're running an insert of xxGB of data (13G data and 9G index?), you will require *more* than xxGB in the t-log file. Breaking it in chunks allows for reuse/reallocation of space within the the tlog file--as long as you are using simple recovery mode, of course. – Philip Kelley Oct 02 '12 at 14:09
  • 2
    Thanks Philip and Kumar. It worked like a charm... I've managed to do it in chunks of 2000/3000 record. I've used "row_number() over" to do it.. After an half hour, it was done! Thanks! – paezinc Oct 05 '12 at 02:10
0

The Best way is to split into smaller chunks and insert using while loop like Philip Kelley Said. . Remove all non-clustered index on the destination table , create them after inserting data. . If u have SQL server BI tool installed use the import-export wizard there and create a basic package it will do a good job . It wont depend on your disk space , depends on your memory sharing with other process.

Kumar_2002
  • 584
  • 1
  • 5
  • 14