which sql consumes less memory

Question

Yesterday I asked a question on how to re-write sql to do selects and inserts in batches. I needed to do this to try and consume less virtual memory, since I need to move millions of rows here.

The object is to move rows from Table B into Table A. Here are the ways I can think of doing this:

SQL #1)

   INSERT INTO A (x, y, z)
   SELECT x, y, z
   FROM B b
   WHERE ...

SQL #2)

FOREACH SELECT x,y,z INTO _x, _y, _z
        FROM B b
        WHERE ...
            INSERT INTO A(_x,_y,_z);
END FOREACH;

Are any of the above incorrect? The database is informix 11.5.

UPDATE:

Turns out something else was causing IDS to consume crazy amounts of memory. The code above, was causing the memory to cross the allotted threshold. At this point, I don't see the point of using one method over the other.

Answer 1

The bottleneck would be the transaction log disk space (or equivalent) to facilitate a rollback if needed.

I've never thought about memory at all for any single statement or operation. Ever.

Answer 2

Since most of the SQL is pseudo-code (for a stored procedure) rather than working code (you'd need VALUES clauses for numbers 2, 3, and 4 to be valid), then they're probably OK. Number 4 needs careful attention to get the job done correctly - I started to write 'Number 4 is wrong' but then realized the others were all non-working SQL too.

Except that you are running into memory issues, Number 1 would be best. Given that you are running into memory issues, then Number 2 is probably the best basis to work from. I would consider putting in sub-transactions using a cursor 'FOR HOLD' if the database is logged. If the database is unlogged, then there is no need to worry about transaction size; each INSERT is atomic.

I noted in my answer to your other question that you might need to look at your server configuration to see why it is running out of memory. (Consider subscribing to the IIUG - International Informix Users Group (free) and asking about your configuration on the mailing list 'ids at iiug dot org'.