Counting status totals
I want to update a batch when all the tasks for that batch are complete. For this I want to compare the total number of jobs and the total number of jobs completed. I am doing this with the query below, however it is a rather slow query. Any suggestions for improving this? Or alternative ways to get closer to a major update?
SELECT DISTINCT j.BatchId,
j.JobStatusId,
COUNT(j.BatchId) OVER(PARTITION BY j.BatchID, j.JobStatusID),
COUNT(j.BatchId) OVER(PARTITION BY j.BatchID)
FROM [Job] j
ORDER BY j.BatchID
Pointer only to JobID (PK)
source to share
<<<<THIS IS NOT A TEST → →
Assuming JobStatusId describes Status like this:
0 = completed
1 = running
2 = error
You can try these two queries, don't know which one is faster, just try:
SELECT DISTINCT j.BatchId AS CompletedJobs
FROM Job j
WHERE NOT EXISTS( --running jobs or jobs who ran into an error
SELECT TOP 1 1
FROM Job j2
WHERE j2.BatchId = j.BatchId
AND j2.JobStatusId IN (1,2))
Or that:
SELECT DISTINCT j.BatchId AS CompletedJobs
FROM Job j
LEFT JOIN
--running jobs or jobs who ran into an error:
(SELECT j2.BatchId
FROM Job j2
WHERE j2.JobStatusId IN (1,2)) j2 ON j.BatchId = j2.BatchId
WHERE j2.BatchId IS NULL
There should be many orders in the original query (which in most cases turns out to be bad. Working on databases in recent years has shown me: an order is required only in 2% of queries, where I saw an offer "in order". Perhaps even 1% ;-)
DISTINCT, ORDER BY, COUNT OVER PARTITION, ... will never be a fast operation - if you combine them, you will see that the result is.
source to share