How to optimize a SQL Server query with repetitive sub queries

Question

I am restrained to a legacy database structure and require some stats results. The following query works, but is inefficient and slow ...

SELECT various, other, native, columns,
    (SELECT client FROM clients WHERE id = clientid) AS client,
    (SELECT name FROM categories WHERE id = (SELECT categoryid FROM clients WHERE id = clientid)) AS category,
    (SELECT fullname FROM staff WHERE id = producerid) AS producer,
    ISNULL((SELECT SUM(amount) FROM JobsVoiceWork v WHERE v.jobid = j.id),0) AS voicecosts,
    (SELECT COUNT(*) FROM Scripts s WHERE s.jobid = j.id) AS numberofscriptscompleted,
    ISNULL((SELECT SUM(duration) FROM TimeLog WHERE jobid = j.id),0)/60 AS totaltime,
    ISNULL((SELECT SUM(duration)  FROM TimeLog WHERE jobid = j.id AND jobeditid = 3 AND jobpart = 'Add'),0)/60 AS PartAdd,
    ISNULL((SELECT SUM(duration)  FROM TimeLog WHERE jobid = j.id AND jobeditid = 3 AND jobpart = 'Update'),0)/60 AS PartUpdate,
    ISNULL((SELECT SUM(duration)  FROM TimeLog WHERE jobid = j.id AND jobeditid = 3 AND jobpart = 'Produce'),0)/60 AS PartProduce,
    ISNULL((SELECT SUM(duration)  FROM TimeLog WHERE jobid = j.id AND jobeditid = 3 AND jobpart = 'Amend'),0)/60 AS PartAmend,
    ISNULL((SELECT SUM(duration)  FROM TimeLog WHERE jobid = j.id AND jobeditid = 4),0)/60 AS EditProducerError,
    ISNULL((SELECT SUM(duration)  FROM TimeLog WHERE jobid = j.id AND jobeditid = 8),0)/60 AS EditVoiceError,
    ISNULL((SELECT SUM(duration)  FROM TimeLog WHERE jobid = j.id AND jobeditid = 1),0)/60 AS EditClientError,
    ISNULL((SELECT SUM(duration)  FROM TimeLog WHERE jobid = j.id AND jobeditid = 2),0)/60 AS EditEntryError,
    ISNULL((SELECT SUM(duration)  FROM TimeLog WHERE jobid = j.id AND jobeditid = 5),0)/60 AS EditPronunciation,
    ISNULL((SELECT SUM(duration)  FROM TimeLog WHERE jobid = j.id AND jobeditid = 6),0)/60 AS EditRemixRequest,
    ISNULL((SELECT SUM(duration)  FROM TimeLog WHERE jobid = j.id AND jobeditid = 7),0)/60 AS EditRevoiceRequest
FROM Jobs j

I have show a simplified version of the query, but I have included the repetitive sub queries to clearly demonstrate the inefficiency. I have tried various table join scenarios, but I cannot improve performance.

It looks like it should be possible to improve. Is there a way?

Answer 1

Try this one -

SELECT various, other, native, columns,
    c.client,
    c2.name AS category,
    s.fullname AS producer,
    ISNULL(v.amount, 0) AS voicecosts,
    s3.numberofscriptscompleted,
    ISNULL(SUM(t.duration),0)/60 AS totaltime,
    ISNULL(SUM(CASE WHEN t.jobeditid = 3 AND t.jobpart = 'Add' THEN t.duration END),0)/60 AS PartAdd,
    ISNULL(SUM(CASE WHEN t.jobeditid = 3 AND t.jobpart = 'Update' THEN t.duration END),0)/60 AS PartUpdate,
    ISNULL(SUM(CASE WHEN t.jobeditid = 3 AND t.jobpart = 'Produce' THEN t.duration END),0)/60 AS PartProduce,
    ISNULL(SUM(CASE WHEN t.jobeditid = 3 AND t.jobpart = 'Amend' THEN t.duration END),0)/60 AS PartAmend,
    ISNULL(SUM(CASE WHEN t.jobeditid = 4 THEN t.duration END),0)/60 AS EditProducerError,
    ISNULL(SUM(CASE WHEN t.jobeditid = 8 THEN t.duration END),0)/60 AS EditVoiceError,
    ISNULL(SUM(CASE WHEN t.jobeditid = 1 THEN t.duration END),0)/60 AS EditClientError,
    ISNULL(SUM(CASE WHEN t.jobeditid = 2 THEN t.duration END),0)/60 AS EditEntryError,
    ISNULL(SUM(CASE WHEN t.jobeditid = 5 THEN t.duration END),0)/60 AS EditPronunciation,
    ISNULL(SUM(CASE WHEN t.jobeditid = 6 THEN t.duration END),0)/60 AS EditRemixRequest,
    ISNULL(SUM(CASE WHEN t.jobeditid = 7 THEN t.duration END),0)/60 AS EditRevoiceRequest
FROM Jobs j
JOIN clients c ON j.id = c.clientid
JOIN categories c2 ON c2.id = c.clientid
JOIN staff s ON j.id = s.producerid
LEFT JOIN (
     SELECT v.jobid, amount = SUM(amount) 
     FROM JobsVoiceWork v 
     GROUP BY v.jobid
) v ON v.jobid = j.id
JOIN (
     SELECT s.jobid, numberofscriptscompleted = COUNT(*) 
     FROM Scripts s 
     GROUP BY s.jobid
) s3 ON s3.jobid = j.id
LEFT JOIN Timelog t ON j.id = t.jobid
--GROUP BY ...

Answer 2

I'm not going to promise that this is better, it might well be worse - but its a different approach, that may help. Give it a whirl, and see if it is of any use.

First, I'm compiling a temporary table, summing duration for all values of jobs.id + timelog.jobeditid + if jobeditid is 3, timelog.jobpart:

SELECT j.id, tl.jobeditid, case when tl.jobeditid = 3 then tl.jobpart else '' end as [JobPart], (Sum(tl.duration)/60) as AdjTotalDuration
INTO #t
FROM Jobs J CROSS JOIN TimeLog tl
GROUP BY j.id, tl.jobeditid, case when tl.jobeditid = 3 then tl.jobpart else '' end;

Now i've simplified your query considerably, using this temporary table in a number of different ways; I've also moved most of the subqueries in the select clause into joined tables in the from clause:

SELECT various, other, native, columns, c.Client, cg.name as [Category], s.fullname as Producer,
  isnull(v.TotalAmount, 0) as voicecosts, (SELECT COUNT(*) FROM Scripts s WHERE s.jobid = j.id) AS numberofscriptscompleted,
  isnull((SELECT SUM(AdjTotalDuration) FROM #t WHERE jobid = j.id),0) AS TotalTime,
  t3a.AdjTotalDuration as PartAdd,
  t3u.AdjTotalDuration as PartUpdate,
  t3p.AdjTotalDuration as PartProduce,
  t3m.AdjTotalDuration as PartAmend,
  t4.AdjTotalDuration  as EditProducerError,
  t8.AdjTotalDuration  as EditVoiceError,
  t1.AdjTotalDuration  as EditClientError,
  t2.AdjTotalDuration  as EditEntryError,
  t5.AdjTotalDuration  as EditPronunciation,
  t6.AdjTotalDuration  as EditRemixRequest,
  t7.AdjTotalDuration  as EditRevoiceRequest
FROM Jobs j 
  join clients c on j.ClientID = c.ID
  join categories cg on c.CategoryID = cg.ID
  join staff s on j.ProducerID = s.ID
  left join (select jobid, sum(amount) as TotalAmount from JobsVoiceWork group by jobid) v on j.id = v.jobid
  left join (select * from #t where id = j.id and jobeditid = 3 and jobpart = 'Add') t3a
  left join (select * from #t where id = j.id and jobeditid = 3 and jobpart = 'Update') t3u 
  left join (select * from #t where id = j.id and jobeditid = 3 and jobpart = 'Produce') t3p
  left join (select * from #t where id = j.id and jobeditid = 3 and jobpart = 'Amend') t3m 
  left join (select * from #t where id = j.id and jobeditid = 4) t4 
  left join (select * from #t where id = j.id and jobeditid = 8) t8 
  left join (select * from #t where id = j.id and jobeditid = 1) t1 
  left join (select * from #t where id = j.id and jobeditid = 2) t2 
  left join (select * from #t where id = j.id and jobeditid = 5) t5 
  left join (select * from #t where id = j.id and jobeditid = 6) t6 
  left join (select * from #t where id = j.id and jobeditid = 7) t7; 

Does this improve it at all? Does it make it worse?

If it does improve it, please mark as answer.

Regards John

Answer 3

You can use CASE statements to eliminate the redundant subqueries, something like:

SELECT various, other, native, columns,
    (SELECT client FROM clients WHERE id = clientid) AS client,
    (SELECT name FROM categories WHERE id = (SELECT categoryid FROM clients WHERE id = clientid)) AS category,
    (SELECT fullname FROM staff WHERE id = producerid) AS producer,
    ISNULL((SELECT SUM(amount) FROM JobsVoiceWork v WHERE v.jobid = j.id),0) AS voicecosts,
    (SELECT COUNT(*) FROM Scripts s WHERE s.jobid = j.id) AS numberofscriptscompleted,
    ISNULL(SUM(t.duration),0)/60 AS totaltime,
    ISNULL(SUM(CASE WHEN  t.jobeditid = 3 AND t.jobpart = 'Add' THEN t.duration ELSE 0 END),0)/60 AS PartAdd,
    ISNULL(SUM(CASE WHEN  t.jobeditid = 3 AND t.jobpart = 'Update' THEN t.duration ELSE 0 END),0)/60 AS PartUpdate,
    ISNULL(SUM(CASE WHEN  t.jobeditid = 3 AND t.jobpart = 'Produce' THEN t.duration ELSE 0 END),0)/60 AS PartProduce,
    ISNULL(SUM(CASE WHEN  t.jobeditid = 3 AND t.jobpart = 'Amend' THEN t.duration ELSE 0 END),0)/60 AS PartAmend,
    ISNULL(SUM(CASE WHEN  t.jobeditid = 4 THEN t.duration ELSE 0 END),0)/60 AS EditProducerError,
    ISNULL(SUM(CASE WHEN  t.jobeditid = 8 THEN t.duration ELSE 0 END),0)/60 AS EditVoiceError,
    ISNULL(SUM(CASE WHEN  t.jobeditid = 1 THEN t.duration ELSE 0 END),0)/60 AS EditClientError,
    ISNULL(SUM(CASE WHEN  t.jobeditid = 2 THEN t.duration ELSE 0 END),0)/60 AS EditEntryError,
    ISNULL(SUM(CASE WHEN  t.jobeditid = 5 THEN t.duration ELSE 0 END),0)/60 AS EditPronunciation,
    ISNULL(SUM(CASE WHEN  t.jobeditid = 6 THEN t.duration ELSE 0 END),0)/60 AS EditRemixRequest,
    ISNULL(SUM(CASE WHEN  t.jobeditid = 7 THEN t.duration ELSE 0 END),0)/60 AS EditRevoiceRequest
    FROM Jobs j
    LEFT JOIN Timelog t
       ON j.id = t.jobid