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:

If there is an entry with the specified tag it should returns the entry with the maximum lVer value and bActive should be TRUE.
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!

@Tom Dee, thank you for the answer! I use the MS ACCESS database over the ADODB connection. To execute queries i use Connection.Execute — John, Oct 07 '19 at 15:23
In MS Access, try saving the derived table as its own query then use named query here. Saved queries can help performance. — Parfait, Oct 07 '19 at 15:38
What flavor SQL? SQL Server? I assume you are writing a pass-thru query. In my experience, Parfait's recommendation will make your query slower. Anything that uses Access' native tool kit (linked tables, saved queries, etc.) to access SQL Server is painfully slow. Pass-thru is definitely the way to go. The query you provided does not produce results consistent with your requirements. Are you asking for performance tips, or for us to adjust your query to get the correct results? — dougp, Oct 07 '19 at 15:51
I assumed you were using Access as the UI and something else was storing the data. Is that backwards? Are you using Access as the database and something else for the UI? — dougp, Oct 07 '19 at 16:04
If your data is stored in Access, please add the jet-sql tag. — dougp, Oct 07 '19 at 20:17

Gordon Linoff · Accepted Answer · 2019-10-07T15:47:08.480

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
);

Thank you for the reply! I'll check it. – John Oct 07 '19 at 15:29 — John, Oct 07 '19 at 15:29

How to optimize the SQL query with the subquery?

1 Answers1