2

Is there any performance benefits in

INSERT INTO table1( column1) select * from table2;

against

BEGIN
FOR t2Row IN SELECT * FROM table2 LOOP
   EXECUTE 'INSERT INTO ...'
END LOOP;
END;

Mostly interesting in postgresql db engine. I believe that first query consume more memory since it buffer whole result, but is first query faster ?

user12384512
  • 3,362
  • 10
  • 61
  • 97
  • 7
    Not knowing anything about your table structure and about how Postgres operates in detail, I would assume from my experience that the **set-based** method (`INSERT INTO table1(...) SELECT (cols) ....` would be **significantly** faster than the RBAR (row-by-agonizing-row) approach. To really know : try it! Measure it ! – marc_s Aug 17 '11 at 11:27

2 Answers2

3

There is a SO answer that touches on this subject here: RBAR vs. Set based programming for SQL

I'd say that the first approach is much faster than the second as it is set-based (as mentioned by marc_s).

Also, the second method involves switching to SQL and then back again for each iteration of the loop. I am not a Postgres user but in Oracle this would cause a performance overhead in the context switching alone, depending upon the number of records involved it could be very significant.

The first method is also the simplest and in most environments the simplest method is the easiest to support.

To answer your question though, the first method is faster than the second.

Community
  • 1
  • 1
Ollie
  • 17,058
  • 7
  • 48
  • 59
0

If you're primarily concerned with speed, then the first method is faster.

If you're primarily concerned with performing the Inserts without causing any locks or degradation in the performance of other queries on the same table, then a For loop Insert is a better option.

Koghena
  • 1
  • 2