CODEWITHSUNDEEP
mysqlsql
daytonk
daytonk

Reputation: 27

Need help improving SQL performance (subquery versus join)

I have 2 tables (AllClients & AllActivities) and need to retrieve the following information:

I need a list of distinct clients where the most recent activity has been entered in the last year. I've gotten the following code to work, but it is painfully slow and therefore not useful. I believe a join (without the subquery) will be faster, but I just can't figure it out. Here is my current sql statement:

select  distinct(AllClients.LookupCode) 
from    AllClients 
join    (select LookupCode, 
                max(AllActivities.EnteredDate) as EnteredDate 
        from AllActivities 
        group by LookupCode) AllActivities 
on      AllClients.LookupCode = AllActivities.LookupCode 
where   AllClients.Name = '$userName' 
and     AllClients.TypeCode = 'P' and AllActivities.EnteredDate < '$oneYearAgo'";

Upvotes: 1

Views: 81

Answers (3)

Sebas
Sebas

Reputation: 21532

you don't need to do the aggregate.

select  distinct(AllClients.LookupCode) 
from    AllClients 
where   
        AllClients.Name = '$userName' 
and     AllClients.TypeCode = 'P' 
and     exists (
    select 1 from AllActivities where AllClients.LookupCode = AllActivities.LookupCode and AllActivities.EnteredDate > '$oneYearAgo'
)

I'm not even sure you need the distinct either in this configuration.

Upvotes: 0

mucio
mucio

Reputation: 7119

do you mean something like this?

  SELECT AllClients.LookupCode
    FROM AllClients
    JOIN AllActivities
      ON AllClients.LookupCode = AllActivities.LookupCode
   WHERE AllClients.Name = '$userName'
     AND AllClients.TypeCode = 'P'
GROUP BY AllClients.LookupCode
  HAVING MAX(AllActivities.EnteredDate) < '$oneYearAgo'";

Upvotes: 1

matteospampani
matteospampani

Reputation: 604

try this:

select AllClients.LookupCode
from AllClients
join AllActivities on AllClients.LookupCode = AllActivities.LookupCode
where AllClients.Name = '$userName' and AllClients.TypeCode = 'P' 
group by AllClients.LookupCode
having max(AllActivities.EnteredDate) < '$oneYearAgo';

Upvotes: 2

Related Questions