2

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?

  • 2
    it would be helpful if you captured the execution plan, and posted it to pastetheplan.com then add the link to your question via the edit link. Also, the T-SQL definitions for the tables involved would be helpful. – Max Vernon Aug 23 '18 at 14:11
  • Code can be formatted in your question by selecting the code then hitting CTRL + K. You should take the tour when you get a chance. – Max Vernon Aug 23 '18 at 14:11
  • @Yakov - I noticed your use of NOLOCK. As this link will explain, its benefits come with substantial risks: blogs.msdn.microsoft.com/davidlean/2009/04/05/… – Victor Di Leo Aug 23 '18 at 16:01
2

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)

  • 2
    wow !!! this really runs less the a second. Thanks a lot. Thanks to Max Vernon ! – Yakov Ravitz Aug 23 '18 at 14:29
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.