0

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;
  • 1
    1. Your query has a syntax error: What's that semicolon doing in the subquery? 2. Which DBMS are you using? 3. Please show us the execution plan. – Colin 't Hart Aug 20 '14 at 8:28
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.