加入操作的性能与循环中的选择操作比较

Question

我是一个有用的助手，可以为您进行文本翻译。下面是您需要翻译的内容：

我想解释一个困扰我的性能问题...

我有两个表，A和B。

表A定义了对象：

+----+--------------+
+ ID + other_things +
+----+--------------+
+ 1  + ~~~~~~~~~~~~ +
+ 2  + ~~~~~~~~~~~~ +
+ 3  + ~~~~~~~~~~~~ +
+----+--------------+

这个表格中有575行。

表B定义了这些对象的一些属性。

+----+-------------+-------------+
+ ID + prop_type   +  prop_value +
+----+-------------+-------------+
+ 1  +    prop1    +   foo       +
+----+-------------+-------------+
+ 1  +    prop2    +   toto      +
+----+-------------+-------------+
+ 3  +    prop2    +  lorem      +
+----+-------------+-------------+  

这个表格中有20254行。

目标是获取表A中所有项目的“prop2”值。

这里的一些用户已经很好地帮我设置了“好”的解决方案（参见问题41331902）：

SELECT A.ID, B.prop_value
FROM A LEFT JOIN
     B
     ON A.ID = B.ID AND B.prop_type = 'prop2';

这个查询大约需要20秒的时间来执行。

然而，目前使用的是另一种版本，我一开始试图改进它：

FOR (
SELECT ID as id_a FROM A
)
DO
prop = (select prop_value from B where prop_type = 'prop2' and id = id_a);
write id_a || '   ' || prop;
END

这样做可以得到相同的结果，但查询只需要1.5秒钟完成...

我从网上阅读的内容中了解到，使用JOIN应该比使用循环更好，但实际结果却与此相反...

我尝试过改变表的顺序（即大表连接小表），但这只会使情况变得更糟（高达1分钟）。

你能帮我理解这里发生了什么吗？

注：由于AspenTech的SQLPlus不允许执行计划，所以我无法获得执行计划。

非常感谢你的帮助。

- Maxime

---

糟糕。我把所有变量都重命名为通用名称，但有些被截断了。 - Maxime

你的循环代码相当于内连接，而你正在将性能与编码为外连接但由于where子句的缘故实际上是内连接的查询进行比较。将第一个查询作为显式内连接运行，并将其用于比较。 - Dan Bracuk

@DanBracuk，我不明白你的意思。如果我在第一个查询中用'INNER JOIN'替换'LEFT JOIN'，我得到的结果不同，即表A中没有在表B中具有'prop2'的项目。对于这些新手的评论，我感到抱歉... - Maxime

Answer 1

如果您有以下查询：

SELECT A.ID, B.prop_value
FROM A LEFT JOIN
     B
     ON A.ID = B.ID AND B.prop_type = 'prop2';

如果你希望提高速度，可以在B上创建一个索引：

CREATE INDEX idx_b_id_type_value ON B(id, prop_type, prop_value);

这将大大提高性能。

如果 B 有一个索引，其中 prop_type 是索引中的第一个键，则您的双查询版本将更快。 我应该补充说：我没有使用 Aspen SQL 的经验。 另一种可能性是它只有一个糟糕的优化器。

- Gordon Linoff

---

我的错误，我删除了太多。有一个“where”条件可以获得相同的结果。 - Maxime