How to optimize a SELECT statement with multiple subqueries
I have a query that I am trying to optimize but has not had much success. There are two tables, one with basic data and one with timestamps when certain events occurred. The tables are relational using the public key adID. I am trying to execute a query that retrieves all timestamps and other data from the main table. It works for me, but I'm trying to optimize to make it run 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 am not very familiar with SQL Server, so I am not very good at optimizing such queries.
source to share
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;
source to share
I think the following request will get 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 concatenates tables together and identifies the earliest record using row_number()
. Then it selects only those records and groups by 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 need this sentence having
at the end of the request:
having sum(case when statusId = 8 then 1 else 0 end) > 0
source to share