Re: Query Optimization

Roy,

Unless I'm missing something or have misunderstood ... have you tried just straight inner join syntax? It should be the most optimized (but not always).

select count(a.projectid)
from project a
inner join reminder b on a.proj_id = b.proj_id inner join notes c on a.proj_id = c.proj_id where

    a.stat_cd = 'CLOSED'
    and b.rem_type = 'LAST'
    and c.note_type = 'QA'

• Bill

"Roy" <roy.ball_at_gmail.com> wrote in message news:1172523215.449990.175370_at_v33g2000cwv.googlegroups.com...
>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.
>
> Regards...
>
Received on Mon Feb 26 2007 - 19:00:00 CST