Which is quicker when inserting: DROP table and rebuild indexes, or TRUNCATE

Question

Which is quicker when inserting: DROP table and rebuild indexes, or TRUNCATE

ahneave

Right there with Babe

Points: 754
More actions
October 26, 2008 at 7:41 pm

#214453

When importing data using DTS packages, which would be quicker: (a) DROP each target table, import the data, then re-create the indexes, or (b) TRUNCATE each target table and retain the indexes. I suspect (a).

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

as with any general question , the answer depends on more info;

how much data is being brought in? if it's a suite of small tables, it might not matter; obviously importing gigs of data is where you need to concentrate on efficiency a bit more.

will the import process insert the records in the same order as the clustered index/primary key?

if not, adding the index after might be better,otherwise truncation with the exising indexes would work fine, I'd think.

I've changed the primary key on a zip+4 database, and it seemed to take forever to reorganize the 30 gigs or so of data under the new index. I got tired of watching it after a couple of hours on my dev machine and left for the night. keep that in mind, it might be possible to text sort your data prior to import your data in order to save time later when a PK gets added.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ahneave Right there with Babe Points: 754 More actions · Answer 2

Thanks for the reply.

There is about 20 gig in about 30 tables.

I'm using DTS packages created directly from the wizard so I guess it's imported in the same order as the source.

The data is sourced in DB2 and Oracle databases on which I have read-only access and therefore can't reorganise it prior to import.

I think from your reply, adding the indexes afterwards will be quicker.

I will try to do a test both ways and report the results back to this thread.

Again, thanks

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

depends on the referential integrity of the tables, etc. Do you want to import data such as key id fields?

disable any constraints and import the data then re enable them

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

Actually, the better option is:

1) Disable all non-clustered indexes

2) Truncate tables

3) Import data

4) Rebuild all indexes

That is for a full refresh process where you are extracting all data from the source system every time you run. If you are not performing a full extract - and instead, are performing daily/weekly/monthly updates the process will be different. You would (most likely) not disable indexes and rebuild them later.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

you wont be able to truncate tables if there are referential integrity constraints on them (foreign keys for example)

disabling the indexes will help from a performance point and maybe you could truncate the tables in referential order (you would need to work this out first) but it is easier to disable any constraints, load the data then re apply them

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Perry Whittle (10/27/2008)
you wont be able to truncate tables if there are referential integrity constraints on them (foreign keys for example)
disabling the indexes will help from a performance point and maybe you could truncate the tables in referential order (you would need to work this out first) but it is easier to disable any constraints, load the data then re apply them

That would be true, if the OP was not considering dropping the tables and recreating them in the first place. :hehe:

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

there's more than one way to skin a cat my friend 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉