什么是更好的？子查询或内部连接十个表？

An old system have arrived on our office for some changes and fix, but it is also suffering from performance issues. We don't know exactly what is the source of this slowness.

一个旧系统已经到我们办公室进行一些更改和修复，但它也遇到了性能问题。我们不确切知道这种缓慢的根源是什么。

While we were refactoring the old code we found several sql queries with the follow pattern (the queries are simplified for example purpose):

当我们重构旧代码时，我们发现了几个带有以下模式的sql查询（为了示例目的简化了查询）：

SELECT
   (
    SELECT X
    FROM A
    WHERE A.id = TABLE.id
   ) AS COLUMN1,
    (
    SELECT Y
    FROM B
    WHERE B.id = TABLE.id
   ) AS COLUMN1,
   (
    SELECT Z
    FROM C
    WHERE C.id = TABLE.id
   ) AS COLUMN1,
   ...
FROM
    TABLE
WHERE
    TABLE.id = @param;

These queries do several internal sub queries from every column they return.

这些查询从它们返回的每个列执行几个内部子查询。

We are planning to rewrite these queries on the follow pattern:

我们计划在以下模式上重写这些查询：

SELECT
    A.X, B.Y, C.Z
FROM
    TABLE
    INNER JOIN A on A.ID = TABLE.ID
    INNER JOIN B on B.ID = TABLE.ID
    INNER JOIN C on C.ID = TABLE.ID
WHERE
    TABLE.id = @param;

With inner joins they are easier to read and understand, but is it really any faster? Is it the better way to write them? Unfortunately the first one we rewrote didn't improve the query time, it made the query a bit slower.

使用内部联接，它们更容易阅读和理解，但它真的更快吗？这是写它们的更好方法吗？不幸的是，我们重写的第一个没有改善查询时间，它使查询有点慢。

Here is my question: should we rewriting all these queries? Are these sub-queries a good way to do this job? Are they faster the the inner-join way?

这是我的问题：我们应该重写所有这些查询吗？这些子查询是否是完成这项工作的好方法？内联方式是否更快？

4 个解决方案

#1

If I understand your question correctly, you are starting an operation to rewrite some of your SQL statements because you THINK there might be an issue with them.

如果我正确理解了您的问题，那么您正在开始重写某些SQL语句的操作，因为您认为它们可能存在问题。

My advice is to stop and first start to determine where your time is currently being spent. Only after you have found that it's in the queries with those scalar subselects AND it's because of those scalar subselects, you should be rewriting them. Until then: start tracing and examining.

我的建议是停下来，先开始确定你的时间目前在哪里。只有在您发现它与那些标量子选择的查询中并且由于那些标量子选择之后，您才应该重写它们。在那之前：开始追踪和检查。

Here are two threads from OTN that are used to guide people with performance problems:

以下是来自OTN的两个线程，用于指导有性能问题的人：

http://forums.oracle.com/forums/thread.jspa?messageID=1812597 http://forums.oracle.com/forums/thread.jspa?threadID=863295

Regards,
Rob.

问候，Rob。

And: because of scalar subquery caching, your original query might be a lot faster than a rewritten query using joins.

并且：由于标量子查询缓存，您的原始查询可能比使用连接的重写查询快得多。

#2

subquery actually runs once for every row whereas the join happens on indexes.

子查询实际上每行运行一次，而连接发生在索引上。

Use joins for better readability and maintainability as you have already mentioned in your questions.

您已经在问题中提到过，使用连接可以提高可读性和可维护性。

#3

Joins will give you better performance, but I recommend taking a look at the execution plan whenever "optimising" queries.

联接将为您提供更好的性能，但我建议您在“优化”查询时查看执行计划。

#4

As this answer argues, it should not affect the performance. However, some query optimizers might perform better on JOINs, so you should make some experiments on your system.

正如这个答案所说，它不应该影响性能。但是，某些查询优化器可能在JOIN上表现更好，因此您应该在系统上进行一些实验。

And now for something completely different: JOINing each table to the next one might be more aesthetic than JOINing all with TABLE, and prevents errors whenever the id appears more than once in one of the tables:

现在，对于完全不同的东西：将每个表连接到下一个表可能比使用TABLE加入所有表更美观，并且只要id在其中一个表中出现多次，就可以防止错误：

SELECT
    A.X, B.Y, C.Z
FROM
    TABLE
    INNER JOIN A on A.ID = TABLE.ID
    INNER JOIN B on A.ID = B.ID
    INNER JOIN C on B.ID = C.ID
WHERE
    TABLE.id = @param;

#1