Systems Engineering and RDBMS

Too many sub-selects

Posted by decipherinfosys on April 28, 2009

While tuning some of the reports at a client site, we noticed that almost all of their reports were using too many sub-selects within their queries in the select part of the queries i.e. select statements within a select statement, example:

select col1, col2,
(select max(col3) from x where x.col4 = t.col1),
(select avg(col5) from y where y.col3 = t.col1)

from t
where t.col7 < 90;

There were also too many derived tables (SQL Server lingo) – also called as inline views (Oracle)…this is when the select statement is a part of the from clause, example:

select col1, col2,
(select max(col3) from x where x.col4 = t.col1),
(select avg(col5) from x where col3 = t.col1)

from (select t.col1, t.col2, t2.col3 from t inner join t2 on t.col1 = t2.col5) as t
where t.col7 < 90;

Inline views/Derived tables have their usefulness and we will cover those in a future blog post.  Back to the sub-selects now…the scalar sub-selects can sometimes be ok to use – especially when the aim is to return top x number of records & the logic is pretty complex which warrants the need for a sub-select.  However, if the data set is large, then since these sub-selects get executed per row returned by the main query, these can be a real drain on the resources.  In a majority of the cases, usage of sub-selects is not an efficient way of writing the SQL queries.

We had a similar situation at the client site and were able to re-write those report SQLs by either:

a) Re-writing the query by making use of joins rather than sub-selects, or
b) Making use of analytic functions to by-pass the reason why the complex sub-selects were written, or
c) By using CTE or by making use of the derived tables.

Resources:

  • Great discussion on Tom Kyte’s site on the same topic – here.
  • Another example – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: