Query takes too much time (2 stages. each stage fast, together - nightmare) MS-SQL 2008 R2 (Dell, 2 CPU)

This query takes two minutes to run, which is too slow.

SELECT * 
FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190 
and event_no &lt;= ( SELECT MIN(event_no) 
                  FROM t_events WITH (NOLOCK)
                  WHERE order_no = 2783190 and complaint_id = 80898 
                )


However, If I run:

SELECT MIN(event_no) FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190 and complaint_id = 80898


it returns the result, 18880040, in less than a second.

If I run:

SELECT * FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190 
and event_no &lt;= 18880040


it returns about 17 lines in 1 sec.

However, together the query takes 2 minutes to return the same 17 lines.

Why is this happening?
You could insert the rows from the subquery into a temporary table, then JOIN against that table.  Something like:

SELECT min_event_no = MIN(event_no) 
INTO #events
FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190 
    and complaint_id = 80898; 


SELECT * 
FROM t_events WITH (NOLOCK)
INNER JOIN #events e ON t_events.event_no = e.min_event_no
WHERE order_no = 2783190 


This makes SQL Server perform the two operations independently, which may make this query faster.  Potentially you could add appropriate indexes, but without the table definition in your question it's impossible to say.



Be aware of potential issues arising from the use of WITH (NOLOCK)