Skip to content

One insert or multiple inserts?

May 13, 2010


A colleague asked me a trick* question today which I failed :)

* whether it was a “trick” question is probably in the eye of the beholder, though…

“What are the differences, if any, between the following two approaches to inserting multiple rows (assume v1 and v2 have different values):

INSERT INTO mytable (a)
SELECT :v1 FROM DUAL;
INSERT INTO mytable (a)
SELECT :v2 FROM DUAL;

OR:

INSERT INTO mytable (a)
SELECT :v1 FROM DUAL
UNION
SELECT :v2 FROM DUAL;

I quickly answered:

  1. The first approach requires one extra parse;
  2. The second approach requires a Sort Distinct (albeit of only two rows)
  3. A UNION ALL would be better, which would not require a Sort, nor would require the extra parse.

My colleague responded, there's one very important thing I missed: Triggers! The first approach would execute the BEFORE STATEMENT and AFTER STATEMENT triggers once for each row. The second approach would only execute these triggers once.

What's sad is that the application we're working on has row-level logic in the BEFORE/AFTER statement triggers. If we try to optimise the code to insert more than one row in one INSERT statement, the application only runs the row-level logic for the first row inserted. Bad code! Very very bad!

About these ads

From → PL/SQL, Tools

4 Comments
  1. Is there any reason for not using INSERT INTO …. VALUES ….. Instead of using DUAL?

    • Hi Jiri,

      Yes, this was just an example. In our case the queries were not on DUAL but on other tables – and each query would only retrieve one row.

      • Thank you for the clarification.

        Typing on iPhone far from dev box to test this….

        I think Option2 should be theoretically faster If

        1.there are indexes
        2. Table is compressed

      • Well, Jiri, we could discuss whether one approach is faster than the other, and under which conditions – but the point of this post is not really about performance, although I can see why it might seem so. The point is really about the differences due to the existence of triggers on the table.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 180 other followers

%d bloggers like this: