How to optimize SELECT statement with multiple sub-queries

Question

I have a query that I'm trying to optimize but haven't had much success. There are two tables, one with the main data and one with timestamps of when specific events occurred. The tables are relational using a common key of adID. I am trying to perform a query that pulls in all of the timestamps and other data from the main table. I have it working but I am trying to optimize so it runs faster.

SELECT a.ID,a.repID,a.artistID,
(
    SELECT TOP 1 c.timestamp
    FROM Tracking AS c
    WHERE statusID = 4
    AND c.ID = a.ID
    ORDER BY c.timestamp ASC

) 
AS created,
(
    SELECT TOP 1 d.timestamp
    FROM Tracking AS d
    WHERE statusID = 5
    AND d.ID = a.ID
    ORDER BY d.timestamp ASC
)
AS claimed,
(
    SELECT TOP 1 p.timestamp
    FROM Tracking AS p
    WHERE statusID = 6
    AND p.ID = a.ID
    ORDER BY p.timestamp ASC
)
AS proof,
(
    SELECT TOP 1 v.timestamp
    FROM Tracking AS v
    WHERE statusID = 8
    AND v.ID = a.ID
    ORDER BY v.timestamp ASC
)
AS approved,
(
    SELECT count(ID)
    FROM Tracking AS t
    WHERE statusID = 6
    AND t.ID = a.ID
)
AS proofcount
FROM Advertising AS a
WHERE a.statusID = 8

Any help on this is appreciated. I'm not too familiar with SQL Server so I am not too well versed in optimizing queries such as these.

How many rows does the query return? What indexes do you have on Tracking? — Martin Smith
– Martin Smith, Commented Feb 15, 2013 at 20:09

Taryn · Accepted Answer · 2013-02-15 20:24:03Z

3

You should be able to use the following:

SELECT a.ID,
  a.repID,
  a.artistID,
  min(case when t.statusID = 4 then t.timestamp end) created,
  min(case when t.statusID = 5 then t.timestamp end) claimed,
  min(case when t.statusID = 6 then t.timestamp end) proof,
  min(case when t.statusID = 8 then t.timestamp end) approved,
  count(case when t.statusID = 6 then id end) proofcount
FROM Advertising AS a
LEFT JOIN Tracking t
  on a.id = t.id
WHERE a.statusID = 8
GROUP BY a.ID, a.repID, a.artistID;

answered Feb 15, 2013 at 20:24

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Taryn Over a year ago

@jgraening You are welcome, I am happy to help. This is basically a PIVOT, you could also implement that function to perform this task as well.

Gordon Linoff · Accepted Answer · 2013-02-15 20:10:59Z

I think the following query gets to what you want:

select id, repid, artistid,
       max(case when statusId = 4 and seqnum = 1 then timestamp end),
       max(case when statusId = 5 and seqnum = 1 then timestamp end),
       max(case when statusId = 6 and seqnum = 1 then timestamp end),
       max(case when statusId = 8 and seqnum = 1 then timestamp end),
       sum(case when statusId = 6 then 1 else 0 end)
from (select a.ID, a.repID, a.artistID, t.statusId, t.timestamp
             row_number() over (partition by a.id, t.statusId order by timestamp) as seqnum
      from advertising a left outer join
           tracking t
           on a.id = t.id
    ) t
where seqnum = 1
group by id, repid, artistid

It joins the tables together and identifies the earliest record using row_number(). It then selects only these records and groups by the other fields.

Your query also filters only those records that have statusid = 8. I'm not sure if this is intentional. If so, then you want this having clause at the end of the query:

having sum(case when statusId = 8 then 1 else 0 end) > 0

Collectives™ on Stack Overflow

How to optimize SELECT statement with multiple sub-queries

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related