which sql consumes less memory
- by prmatta
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
FROM B b
WHERE ...
INSERT INTO A(x,y,z);
END FOREACH;
SQL #3)
FOREACH SELECT FIRST 2000 x,y,z
FROM B b
WHERE ...
INSERT INTO A(x,y,z);
END FOREACH;
SQL #4)
FOREACH SELECT FIRST 2000 x,y,z
FROM B b
WHERE ...
AND NOT EXISTS IN (SELECT * FROM A)
INSERT INTO A(x,y,z);
END FOREACH;
Are any of the above incorrect?
The database is informix 11.5.
