Mutiple Inserts. Normal method VS Union ALL method

I know the eternal war between DBA’s and Developers rages on :D, While most developers prefer to make multiple inserts. DBA’s always use UNION all. While highly debatable which is fast performance wise. But if you are doing thousands of insert’s UNION ALL is definitely faster. I have narrowed down inserts from 40 second to milliseconds using UNION ALL.

How to do UNION ALL

  1. Consider a table MyTable with two columns : FirstCol, SecondCol
  1. Normal insert on this table you would do as follows

INSERT INTO MyTable  (FirstCol, SecondCol)

VALUES (‘First’,1);

INSERT INTO MyTable  (FirstCol, SecondCol)

VALUES (‘Second’,2);

INSERT INTO MyTable  (FirstCol, SecondCol)

VALUES (‘Third’,3);

INSERT INTO MyTable  (FirstCol, SecondCol)

VALUES (‘Fourth’,4);

INSERT INTO MyTable  (FirstCol, SecondCol)

VALUES (‘Fifth’,5);

COMMIT;

The clause INSERT INTO is repeated multiple times. Many times we copy and paste to save time. Now let’s do it the alternative way

  1. UNION ALL alternative method

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5

COMMIT;

If you are doing more than 1K inserts, then this is the simplest way. Anything above 10K inserts should use BULK INSERT method or Open Cursors in a loop.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s