How to optimize the SQL query with the subquery?

Question

I want to improve the performance of a SQL query. I have the table 'tblEntries' with the column 'sTag':

+----+------+-------+------+---------+
| Id | sTag | sPath | lVer | bActive |
+====+======+=======+======+=========+
| 1  | NULL |  t1   | 100  |   +     |
| 2  | NULL |  t2   | 110  |   +     |
| 3  | x1   |  t4   | 110  |   +     |
| 4  | x1   |  t3   | 120  |   +     |
| 5  | x2   |  t7   | 100  |   +     |
+----+------+-------+------+---------+

A client queries for the path with the specified tag and the query should return a specified entry with the next condition:

1. If there is an entry with the specified tag it should returns the entry with the maximum lVer value and bActive should be TRUE.
2. If there is no entry with the specified tag it should returns the entry with the NULL sTag value and with the maximum lVer value and bActive should be TRUE.

The "tagged" entry has the more priority over "non-tagged" one.

The current SQL query is:

SELECT lVer, sPath 
FROM tblEntries 
INNER JOIN 
(SELECT MAX(lVer) AS V, sTag AS T 
FROM tblEntries 
WHERE bActive = TRUE 
GROUP BY sTag)
ON lVer = V 
WHERE T IS NULL OR T = 'user_tag' 
ORDER BY T DESC

Then i can select the first entry which satisfies the conditions. Can i avoid the subquery?

Thanks!

Answer 1

Depending on your data and database, this might have sufficient performance:

select top (1) e.*
from tblEntries e
where e.bActive = TRUE and
      (e.t IS NULL OR e.t = 'user_tag')
order by (e.t desc),  -- null values last
         t.lver desc;

If speed is really an issue and you have an index on (t, active, lver desc), this might be a bit faster:

(select top (1) e.*
 from tblEntries e
 where e.t = 'user_tag' and e.bActive = TRUE 
 order by e.lver desc
) union all
(select top (1) e.*
 from tblEntries e
 where e.t is null and e.bActive = TRUE and
       not exists (select 1 from tblEntries e2 where e2.t = 'user_tag' and e2.bActive = TRUE )
 order by e.lver desc
);