Proc SQL 删除花费的时间太长
我正在运行以下 SAS 命令：
Proc SQL;
Delete From Server003.CustomerList;
Quit;
这需要 8 分钟以上...而读取该文件只需要几秒钟。 是什么原因导致删除花费如此长的时间？我可以采取什么措施使其速度更快？
（我无权删除表，所以我只能删除所有行）
谢谢，
丹
编辑：我显然也无法截断表。
I'm running the following SAS command:
Proc SQL;
Delete From Server003.CustomerList;
Quit;
Which is taking over 8 minutes... when it takes only a few seconds to read that file. What could be cause a delete to take so long and what can I do to make it go faster?
(I do not have access to drop the table, so I can only delete all rows)
Thanks,
Dan
Edit: I also apparently cannot Truncate tables.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
这不是常规 SQL。 SAS 的 Proc SQL 不支持 Truncate 语句。 理想情况下，您希望了解
delete from的性能发生了什么； 但如果您真正需要的是截断功能，那么您始终可以只使用纯 SAS，而根本不使用 SQL。这可以像
Truncate一样有效地执行和操作。 它维护数据集/表结构，但无法用数据填充它（由于 OBS= 选项）。This is NOT regular SQL. SAS' Proc SQL does not support the Truncate statement. Ideally, you want to figure out what's going on with the performance of the
delete from; but if what you really need is truncate functionality, you could always just use pure SAS and not mess with SQL at all.This effectively performs and operates like a
Truncatewould. It maintains the dataset/table structure but fails to populate it with data (due to the OBS= option).尝试将其添加到您的
LIBNAME语句中：根据 适用于关系数据库的 SAS/ACCESS(R) 9.2：参考，
Try adding this to your
LIBNAMEstatement:According to SAS/ACCESS(R) 9.2 for Relational Databases: Reference,
是否有很多其他表具有该表的外键？ 如果这些表在外键列上没有索引，那么 SQL 可能需要一段时间才能确定删除这些行是否安全，即使其他表实际上在外键中都没有值列）。
Are there a lot of other tables which have foreign keys to this table? If those tables don't have indexes on the foreign key column(s) then it could take awhile for SQL to determine whether or not it's safe to delete the rows, even if none of the other tables actually has a value in the foreign key column(s).
我还要提到的是，一般来说 SQL 命令在 SAS PROC SQL 中运行速度较慢。 最近，我做了一个项目，并将 TRUNCATE TABLE 语句移动到存储过程中，以避免将它们放在 SAS 中并由 SQL 优化器和周围的执行 shell 处理的惩罚。 最终，这极大地提高了 TRUNCATE TABLE 的性能。
I would also mention that in general SQL commands run slower in SAS PROC SQL. Recently I did a project and moved the TRUNCATE TABLE statements into a Stored Procedure to avoid the penalty of having them inside SAS and being handled by their SQL Optimizer and surrounding execution shell. In the end this increased the performance of the TRUNCATE TABLE substantially.
它可能会更慢，因为磁盘写入通常比读取慢。
至于在不删除/截断的情况下解决它的方法，好问题！ :)
It might be slower because disk writes are typically slower than reads.
As for a way around it without dropping/truncating, good question! :)
您也可以考虑优雅的：
proc sql; 创建表 libname.tablename 像 libname.tablename; 辞职;
我将生成一个与前一个表具有相同名称和相同元数据的新表，并在同一操作中删除旧表。
You also could consider the elegant:
proc sql; create table libname.tablename like libname.tablename; quit;
I will produce a new table with the same name and same meta data of your previous table and delete the old one in the same operation.