SQL – Reformatting to Improve Performance 6

4 12 2009

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/c28103fe283b7551

I am learning SQL (trial by fire) and have a question regarding a query optimization. Lets say I have three tables ‘project’,  ‘notes’ & ‘reminder’. Key on ‘project’ is ‘proj_id’. The other two tables reference this key as ‘notes’ contains note entries on a given project and ‘reminder’ tracks todo type and due dates.

select count(a.proj_id)
from project a
where a.stat_cd = 'CLOSED'
and  exists
      (
      select b.proj_id
      from reminder b
      where b.rem_type = 'LAST'
      and a.proj_id = b.proj_id
      )
and exists
      (
      select c.proj_id
      from notes c
      where c.note_type = 'QA'
      and a.proj_id = c.proj_id
      )

I am trying to determine the number of projects that have a ‘CLOSED’ status, contain a reminder type of ‘LAST’ and has a note type of ‘QA’

I get the result I am looking for but these are very large tables and it takes over 30 minutes for this to run. Is there a better way to write this query? I’m betting there is. Thank you in advance for your advice.

Give this re-write a try to see if it improves performance – I have essentially moved the exists statements into two inline views (some versions of Oracle may automatically perform such transformations):

SELECT
  COUNT(A.PROJ_ID)
FROM
  PROJECT A,
  (SELECT DISTINCT
    B.PROJ_ID
  FROM
    REMINDER B
  WHERE
    B.REM_TYPE = 'LAST') B,
  (SELECT DISTINCT
    C.PROJ_ID
  FROM
    NOTES C
  WHERE
    C.NOTE_TYPE = 'QA') C
WHERE
  A.STAT_CD = 'CLOSED'
  AND A.PROJ_ID = B.PROJ_ID
  AND A.PROJ_ID = C.PROJ_ID;

Very likely, you will need an index on the PROJ_ID column for each table, especially if the REMINDER and NOTES tables contain columns that are wide.  An index on REMINDER.REM_TYPE might help.  An index on NOTES.NOTE_TYPE also might help.  Take a look at the explain plan (preferrably DBMS_XPLAN) for your query, and compare it to the explain plan for the one above.  Also, make certain that the tables and indexes are analyzed (use DBMS_STATS for Oracle 8i and above).

It is important to keep in mind that the two solutions provided may or may not yield the same results.  This will be evident in those cases where there is more than one row in REMINDER for a PROJ_ID WHERE REM_TYPE = ‘LAST’, and in cases where there is more than one row in NOTES for a PROJ_ID WHERE NOTE_TYPE = ‘QA’.

To work around the above issue, if it may occur, modify AlterEgo’s solution:

select count(a.projectid)

Can be modified as (assuming that PROJECTID is the primary key of the
PROJECT table):

SELECT
  COUNT(DISTINCT A.PROJECTID)

With the above change, you may find that AlterEgo’s SQL statement executes slightly faster than the solution that I provided (this may be Oracle version dependent).

One final note.  When building SQL statements with aliases, it is easier to troubleshoot problems with the SQL statements if the alias name is somehow related to the object name that it represents:
Rather than using:

  PROJECT A,
  REMINDER B,
  NOTES C

I would use:

  PROJECT P,
  REMINDER R,
  NOTES N

Actions

Information

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




Follow

Get every new post delivered to your Inbox.

Join 137 other followers

%d bloggers like this: