Thursday, March 22, 2012

DROP vs TRUNCATE which is faster?

I work with datafiles containing more than 1 million records. Which would be faster DROP TABLE or TRUNCATE TABLE?

You would DROP the table if you do not intend on using the table again.

You would TRUNCATE a table if you intend to use the table again.

I would imagine the speed difference is insignificant compared to doing what is functionally correct for your situation. The only case where the speed might make a difference is if you are performing the operation many hundred times over. So lets consider that scenario:

If you no longer need these several hundred tables, then you might as well DROP them, else they will remain there taking up resources.

If you are going to reuse the tables, then TRUNCATE them, else you are faced with having to recreate them. I would guess that TRUNCATE is faster versus the two oeprations of DELETE+CREATE.

No comments:

Post a Comment