bad query

[misuk11]

Hi guys

Can anyone give me some pointers on the SQL below. It seems to freeze and doesnt return any results. Ive left it running for a few hours and have had to terminate it every time


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 currently has over 4 million rows, table mm_file has over 770,000.

tia
Mark

 

[micha123]

can you please run it with

SET SHOWPLAN, NOEXEC ON

to see if all the indexes on your tables are correct and post us the results?

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.