Hi guys

I have a query that comes to a complete halt, Ive left it running for ages but it never returns any data, just seems to 'hang' Ive been asked to look at it to find out why, can anyone give me some pointers ?

SELECT
mm_file_id_cod,
mm_move_moved_dtm,
mm_move_loc_nam
FROM
mm_file
INNER JOIN mm_move ON mm_move.mm_file_key = mm_file.mm_file_key
WHERE
mm_move_loc_nam IN ('ARCUS','ARCUS.','ARCUS..','ARCUS...','ARCUS/','.ARCUS','arcus','ARCUS: Reading','ARCUS: Western Avenue') AND
mm_move.mm_move_moved_dtm = (SELECT MAX(m1.mm_move_moved_dtm)
FROM
mm_move m1
WHERE
m1.mm_file_key = mm_move.mm_file_key)


table mm_move has over 4 million rows, table mm_file has over 770,000.

heres the execution plan


QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION ON.


QUERY PLAN FOR STATEMENT 2 (at line 2).


STEP 1
The type of query is SELECT.

FROM TABLE
mm_move
Nested iteration.
Index : mm_move_id
Forward scan.
Positioning by key.
Keys are:
mm_move_id ASC
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
mm_file
Nested iteration.
Using Clustered Index.
Index : mm_file_pk
Forward scan.
Positioning by key.
Keys are:
mm_file_key ASC

Run subquery 1 (at nesting level 1).
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
STEP 1

NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 2.

QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 11).

Correlated Subquery.
Subquery under an EXPRESSION predicate.


STEP 1
The type of query is SELECT.
Evaluate Ungrouped MAXIMUM AGGREGATE.

FROM TABLE
mm_move
m1
Nested iteration.
Index : mm_move_file
Forward scan.
Positioning by key.
Keys are:
mm_file_key ASC
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

END OF QUERY PLAN FOR SUBQUERY 1.