I have two tables named "Visit" and "VisitMovement" and one Visit can contain many VisitMovements.

Within my query I need to join to the latest record for the Visit in VisitMovement so I can get back the latest VisitMovement details.

To do this I have a view like this that I join to:

    max(VisitMovementID) as VisitMovementID

and finish up with:

            Visit V
        INNER JOIN VisitMovement VM ON
            V.VisitID = VM.VisitID
        INNER JOIN vwLatestVisitMovement LVM on
            VM.VisitMovementID = LVM.VisitMovementID

I can't Index this View due to the max operator, and I can see over-time this becoming a performance issue with the Index Scan operation.

I wondered if anyone had any good ideas on how to approach this? I was thinking of having a trigger and maintaining the latest VisitMovementID in the Visit table but this doesn't sit well with me.


I think (not very clear) that you want something either:

For a VIEW solution - use something like this

SELECT v.visitor_id AS vid, v.visitor_name
FROM visitor v
INNER JOIN vw_max_visit_movement vmw ON
v.visitor_id = vmw.visitor_id;

Personally, I might want to use a CTE (unless I had millions of visitors), as per here. The reason I mentioned that you might want to avoid this approach for large tables is best explained here.

WITH visitor_max AS
  SELECT visitor_id, MAX(visit_movement_id)
  FROM visit_movement
  GROUP BY visitor_id
SELECT v.visitor_id AS "Vistor No.", v.visitor_name AS "Visitor name"
FROM visitor v
INNER JOIN visitor_max vmax ON
v.visitor_id = vmax.visitor_id;

In any case, the results are the same (not sure if this is what you're asking - if not, please expand the question)

Vistor No.  Visitor name
         2        Paulie
         3           Jim
         4          Bill
         5          Fred

=== DDL and DML for the tables used =====

CREATE TABLE visit_movement
  visit_movement_id INTEGER,   -- possibly a timestamp?
  visitor_id INTEGER

INSERT INTO visit_movement 
(34, 2),
(35, 2),
(34, 2),
(37, 3),
(38, 3),
(39, 3),
(40, 3),
(41, 4),
(42, 4),
(43, 5);

  visitor_id INTEGER,
  visitor_name VARCHAR (20)

(1, 'Mary'),
(2, 'Paulie'),
(3, 'Jim'),
(4, 'Bill'),
(5, 'Fred'),
(6, 'Xavier');

CREATE VIEW vw_max_visit_movement AS
SELECT visitor_id, MAX(visit_movement_id)
FROM visit_movement
GROUP BY visitor_id;
  • Yes, that is indeed exactly what I'm looking to do. Just wondered about the best approach.
    – Philip
    Nov 13 '17 at 7:12
  • @James - I've reworded slightly to make my answer (hopefully) more clear. If you found it helpful and/or correct, please upvote or mark it as such.
    – Vérace
    Nov 13 '17 at 10:46
  • thanks, yes the view option is exactly what I was doing. I'm not looking at a sub-query which looks to give a better execution, but I appreciate your reply.
    – Philip
    Nov 14 '17 at 7:13

Two things, first, what does the execution plan look like on this? Are you seeing efficient use of the existing indexes and structure? If so, then that's likely to continue into the future. If it's problematic now, it's just going to get worse.

So, you might want to look at mechanisms for querying the data like those I outline in this article. By and large, at least through my tests, using ROW_NUMBER to get that latest value is likely to work better for you.

Also, you may not want to use a view. I know that it makes code re-use possible, but sometimes in T-SQL we just have to write the same query 100 (or more) times because the engine deals with it better. Test with & without a view to be sure of that though.

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .