Query takes too much time (2 stages. each stage fast, together - nightmare)

Question

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 <= ( 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 <= 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?

Hannah Vernon · Accepted Answer · 2018-08-23T15:41:47.040

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)}

Haris Khan · Answer 2 · 2018-08-23T15:22:43.803

Without the plan, I can only help you so much, but the subquery is likely the problem.

If this:

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

Returns a single record, just store it in a variable like this:

DECLARE @min_event_no INT

...

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

...and use it in the query instead:

SELECT * FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190 
and event_no <= @min_event_no

Hope that helps.

EDIT: Erik caught me doing a bad thing, so let me apply the better practice here, with a temporary stored procedure. For simple, ad-hoc, one-off queries with small result sets (which is what I was assuming this was given the specificity of the WHERE clause), the above will work fine, in my opinion. For anything else, do this.

CREATE PROCEDURE #GetEvents
 @min_event_no INT
AS
SELECT * FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190 
and event_no <= @min_event_no

GO

EXEC #GetEvents @min_event_no = (SELECT MIN(event_no) FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190 and complaint_id = 80898);

GO

Query takes too much time (2 stages. each stage fast, together - nightmare)

2 Answers2