Slow Insert

Do you deal with large data set in your organization? If yes, this is a must read for you. I have worked on a project where the small table size was just over 21 millions records. And there are some companies which database has less then 21 millions. Moving data from these huge tables are always challenging and fun. With this article, I am going to talk about the secrets on how to overcome the challenges and insert record damn fast.

Th recommendation presented here still applies to smaller tables but you will need to evaluate and see if it is worth implementing.

There are several factors that  can cause your INSERT or  IMPORT job running CRAZY SLOW on Oracle DBMS. 8 out of 10 times, following recommended steps will speed up your Inserts.  I would try these steps in the order in which they are presented. Combining two or more listed steps will give you the excellent result (sometimes!).

1. Trigger: Disable any triggers associated with a table. Enable after Inserts are complete.
2. Index: Drop and recreate Index after your Inserts are complete. 
3. Stale Stats:  Re-analyze table and Index stats.
4. Index Fragmentation: Rebuild Index if needed
5. Use No Logging: Insert using INSERT APPEND (Oracle only). This approach is very risky  approach, no redo logs are generated  therefore you can’t do a rollback - make a backup of table before you begin. This is strongly not recommended on productions tables.  If your DBMS is not Oracle, check if your db has similar option
6. Parallel Insert: Running parallel Insert will get the job faster.
7. Use Bulk Insert:
8. Constraints: Not much overhead during inserts but still a good idea to check, if it is still slow after even  after step 1-4

Recently, I combined (No Logging, Parallel and Trigger Disable) and I was able to reduce the Insert time from 32 hours to 1 ½ hour. Isn't that amazing? I could have reduced  little more by dropping an  re-creating Index. This article is not limited to Oracle Database, these recommendations still applies to SQL Sever, MySQL and all other relational database systems available out there.

Each steps has it’s own pros and cons. Therefore, knowing the cons and pros in advance will save you from the trouble & clean-up you may have to perform later. I believe Zero or NO Insert is fastest of all. what do you think?