Does having too many subqueries in the from clause slow down the query-CodePudding

I'm having to create subqueries in the from clause just to write cleaner code but in return does having too many subqueries slow down the query.

for example:

select id,
  name,
  annual_income * 0.10 AS tax
from (
  select id,
    name,
    annual_income
  from (
     select id,
       first_name || ' ' || last_name AS name
       income * 12 AS annual_income
  )
);

the above is just a made up example

CodePudding user response：

[TL;DR] It depends on the queries you are using in the sub-queries.

In your case:

select id,
       name,
       annual_income * 0.10 AS tax
from   (
  select id,
         name,
         annual_income
  from   (
    select id,
           first_name || ' ' || last_name AS name
           income * 12 AS annual_income
    from   table_name
  )
);

Will get rewritten by the SQL engine to:

select id,
       first_name || ' ' || last_name AS name
       income * 1.2 AS tax
from   table_name;

There will be no difference in performance between the two queries and if it is easier for you to understand and/or maintain the query in its expanded form then you should use that format and not worry about the nested sub-queries.

However, there are some cases when sub-queries can affect performance. For example, this question was a particularly complex issue where the sub-query factoring clause was being materialized by the inclusion of the ROWNUM pseudo-column and that forced the SQL engine to execute in a particular order and prevented if from rewriting the query into a more optimal form and prevented it from using an index which made the query very slow.