什么是更好的?子查询还是内部联接十个表? [英] What is better? Subqueries or inner joining ten tables?

问题描述

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

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.

在重构旧代码时，我们发现了一些遵循以下模式的sql查询(出于示例目的，对查询进行了简化):

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

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?

推荐答案

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

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.

我的建议是停止并首先开始确定您当前在哪里花费时间. 只有在发现带有这些标量子选择的查询中并且是由于这些标量子选择之后，才应该重写它们. 在此之前:开始跟踪和检查.

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.

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

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

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

关于，
罗布.

Regards,
Rob.

而且:由于标量子查询缓存，您的原始查询可能会快很多而不是使用联接的重写查询.

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

这篇关于什么是更好的?子查询还是内部联接十个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！