哪种 SQL 消耗的内存更少？
昨天我问了一个问题重写SQL来批量选择和插入。我需要这样做是为了尝试消耗更少的虚拟内存，因为我需要在此处移动数百万行。
目标是将行从表 B 移至表 A。以下是我能想到的执行此操作的方法：
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;
以上是否有错误？ 数据库是Informix 11.5。
更新：
原来还有其他原因导致 IDS 消耗大量内存。上面的代码导致内存超过分配的阈值。在这一点上，我不认为使用一种方法优于另一种方法有什么意义。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
瓶颈将是事务日志磁盘空间（或同等空间），以便在需要时进行回滚。
我从来没有考虑过任何单个语句或操作的内存。曾经。
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.
由于大多数 SQL 是伪代码（对于存储过程）而不是工作代码（您需要 VALUES 子句才能使数字 2、3 和 4 有效），因此它们可能没问题。第 4 条需要仔细注意才能正确完成工作 - 我开始写“第 4 条是错误的”，但后来意识到其他的 SQL 也都是不起作用的。
除非您遇到内存问题，否则第 1 号是最好的。鉴于您遇到了内存问题，那么 2 号可能是最好的工作基础。如果数据库已记录，我会考虑使用游标“FOR HOLD”放入子事务。如果数据库未记录，则无需担心事务大小；每个 INSERT 都是原子的。
我在回答您的其他问题时指出，您可能需要查看服务器配置以了解内存不足的原因。 （考虑订阅 IIUG - 国际 Informix 用户组（免费）并在邮件列表上询问您的配置“iiug dot org 的 ids”。
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'.