CODEWITHSUNDEEP
sqloracle-databaseperformance
msanaullah
msanaullah

Reputation: 37

Do sub queries in select statement creating any performance downgrade?

select po_number,
            (select t.full_name 
             from person t 
             where t.subscriber_id=358 
              and t.company_id=2
              and person_id=p.buyer_person_id) buyer_name 
from po_header p 
where subscriber_id=358 and company_id=2 ;

Please see above example. Guys could you please help me to understand the subquery performance in select statement?

Upvotes: 0

Views: 60

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

Well, what is there to say? For each po_header you get a certain matching person's name. We may naturally think of loops:

  1. Loop through the headers.
  2. For each header loop through the names and find the matching one.

But we already know that, provided an appropriate index exists, the DBMS may just use the index to find the name rather than loop through the person table. What is not so evident, however, is the fact that the DBMS is free to completely to re-write the query internally. So maybe the first thing the DBMS does instead is join the two tables and then remove what it doesn't need. Or it may create a list of all names matching the company and subscriber ID first and use this for a lookup later. The DBMS has many techniques on how to deal with tables and Oracle's optimizer is very good.

So we may guess what the DBMS actually does, or we may look at the excecution plan. Anyway, this isn't something we should waste too much time on. If the query runs fine without any obvious performance issues, then why worry? Once it shows that the query is slow, we'd look at the execution plan and consider which indexes to build, in this case probably something like:

create index idx1 on po_header(subscriber_id, company_id, buyer_person_id, po_number);
create index idx2 on person(subscriber_id, company_id, person_id, full_name);

The query as is looks fine and is readable and hence maintainable. I see no reason to change it much. Well, we could make the correlation clearer of course:

select 
  po_number,
  (
    select p.full_name 
    from person p 
    where p.subscriber_id = h.subscriber_id
      and p.company_id    = h.company_id
      and p.person_id     = h.buyer_person_id
  ) as buyer_name 
from po_header h
where subscriber_id = 358 and company_id = 2;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521684

Here is your current query:

select po_number,
       (select t.full_name from person t where t.subscriber_id=358 and
        t.company_id=2 and person_id=p.buyer_person_id) buyer_name
from po_header p
where subscriber_id=358 and company_id=2;

The subquery in the SELECT statement is a particular type called a correlated subquery. The output of this subquery depends on, or is correlated with, the outer query. In practice, this means that the subquery must be run for every record of the po_header table. This can be costly from a performance point of view.

An alternative to what you have written would be to join your two tables, something like this:

SELECT
    p.po_number,
    COALESCE(t.full_name, 'NA') AS full_name
FROM po_header p
LEFT JOIN person t
    ON p.buyer_person_id = t.person_id AND
       t.subscriber_id   = 358         AND
       t.company_id      = 2
WHERE p.subscriber_id = 358 AND
      p.company_id    = 2

I would expect this to perform better than your original query, especially if we were to add indices to the columns involved in the join and the WHERE clause.

Upvotes: 0

Related Questions