CODEWITHSUNDEEP
sqlsql-server
user3590149
user3590149

Reputation: 1605

SQL - Optimize query for iteration

I'm using this query in a loop and would like to make it faster.

SELECT * FROM job_activity as ja
INNER JOIN job as j
  ON ja.job_id = j.id 
WHERE j.name = 'name'
  AND ja.avg_runtime <> 0 
  AND ja.id = (SELECT MAX(id) FROM job_activity 
                  WHERE job_id = ja.job_id and avg_runtime <> 0);

Using MS SQL 11 I'm joining two tables job and job_activity using the id of job when equal to job.name that will find me the avg_runtime for that job on the job_activity table but doing this for a big dataset. Would using 'with' query be better?

Table layout

job
****
id
name


job_activity
*************
id
job_id
avg_runtime

Upvotes: 0

Views: 93

Answers (3)

Justin
Justin

Reputation: 9724

try:

SELECT a.*
FROM(
SELECT *,
ROW_NUMBER()OVER(PARTITION BY ja.job_id ORDER BY ja.id DESC) as rnk
FROM job_activity as ja
INNER JOIN job as j
  ON ja.job_id = j.id 
WHERE j.name = 'name'
  AND ja.avg_runtime <> 0 )a
WHERE a.rnk = 1

Upvotes: 0

radar
radar

Reputation: 13425

This is modified query, where we get maximum using group by in a subquery and do a inner join.

SELECT * FROM job_activity  as ja 
join job as j 
on ja.job_id = j.id 
INNER JOIN
(
select ja.job_id, max(id) as id from job_activity ja
       join job j
       ON j.id = ja.job_id
where avg_runtime <> 0 
and j.name = 'name'
group by job_id
) T
ON T.id = ja.id

Upvotes: 1

DRapp
DRapp

Reputation: 48139

It looks like you are looking for the most recent activity for each possible job a person may be working on. And then you want whatever that latest activity was for each respective job. That said, I am starting the first prequery (alias PreMax) is where I am getting all job IDs for the person in question joined to the job activity that has an avg_runtime <> 0. The max() is applied to each job via the group by of just the job. From this, it will be a unique list of any/all jobs for the person who qualfied based on the avg_runtime. This leaves a simple re-join back to the job and job activity table based on those values and returns the data for it all.

SELECT
       j2.*, ja2.*
   FROM
      ( SELECT
              j.ID,
              MAX( ja.id ) as MaxJAID
           from 
              job j
                 join job_activity ja
                    ON j.id = ja.job_id
                    and ja.avg_runtime <> 0
           where
              j.name = 'name'
           group by 
              j.id ) PreMax
        JOIN job j2 on PreMax.id = j2.id
        JOIN job_activity ja2 on PreMax.MaxJAID = ja2.id

Upvotes: 0

Related Questions