Is my subquery inneficient. It is extremely slow

Question

I have two tables. Member information and login entries. I am simply trying to display the member information table sorted by the last login access date. So i created this query, but it is extremely slow. Is there a better way to write it?

SELECT
    menmembers.memb_id, 
    menmembers.firstname, 
    menmembers.lastname, 
    (SELECT Max(trank.datetr) AS MaxOfdatetr
        FROM trank 
        where trank.cod_usr=menmembers.memb_id;) AS LastLogin
FROM menmembers;

Answer 1

There are two ways (not mutually exclusive) to improve query performance. First, you could re-write the correlated subquery into a subquery with an aggregate:

SELECT
    m.memb_id, 
    m.firstname, 
    m.lastname, 
    t.LastLogin
FROM menmembers AS m
LEFT JOIN (
    SELECT cod_usr, MAX(datetr) AS LastLogin
    FROM trank
    GROUP BY cod_usr
    ) AS t ON m.memb_id=t.cod_usr;

Second, you can add a proper index to trank:

CREATE /* UNIQUE? */ INDEX IX_trank_LastLogin ON trank (cod_usr, datetr);

Simply for query performance, I would consider sorting the datetr column DESC in this index, although that may not be optimal for INSERTs into the table. Alternatively, you could just INCLUDE the datetr column:

CREATE INDEX IX_trank_LastLogin ON trank (cod_usr) INCLUDE (datetr);

Obviously, you should have an index on menmembers.memb_id as well for this to work properly, but I'm guessing that's already taken care of.

I've written this code for SQL Server, but since you haven't tagged your dbms, I can't be sure it'll work on your setup.

Answer 2

Your query is slow because of the sub-select. It is what is known as a correlated sub query. It will be executed once per row in the outer query.

This question answers a similar problem. It's looking for the minimum, you for the maximum; it's using one table, you're using one. The translation shouldn't be too hard, though.