How to debug a slow SQL Server query?

Question

I am executing a query in SQL Server as below:

select MAX(id) as id  FROM Employee where  CONVERT(date,submitted_dt)='2015-07-15' group by EmpId

Here Id is different column and Empid is different column ,Id is primary key with identity

Result is as below:

It's around 185 rows, and I am doing NOT IN statement in the result query

    select  EmpId,submitted_dt  
FROM Employee 
where id NOT IN 
(select MAX(id) as id  FROM Employee where     
CONVERT(date,submitted_dt)='2015-07-15' 
group by EmpId) and CONVERT(date,submitted_dt)='2015-07-15'

It is running very slow it is taking 2 min.

What are wait events for the query? As first you can try remove the following condition: CONVERT(date,submitted_dt)='2015-07-15' Convert operation for each row in the table/index are very expensive — Giova, Aug 21 '15 at 13:19

score 1 · Answer 1 · answered Aug 21 '15 at 13:32

1

It seems like you want to find employees with more than one id on a particular submitted date. Here is another approach

select e.EmpId, e.submitted_dt  
from (select e.EmpId, e.submitted_dt,
             max(id) over (partition by EmpId) as maxid
      from Employee e
      where CONVERT(date, submitted_dt) = '2015-07-15'
     ) e
where id <> maxid;

For performance, in index on Employee(submitted_dt, EmpId, id) is recommended.

answered Aug 21 '15 at 13:32

Gordon Linoff

1,242,037
58
646
786

And if you can change the data type on submitted date to be a date that would be helpful too. If it is stored as datetime you might consider using submitted_dt>='2015-07-15' AND submitted_dt<'2015-07-16' rather than converting all the dates. – HLGEM Aug 21 '15 at 13:36
1

@HLGEM . . . I am trying to remember that SQL Server *does* use an index for conversions from `datetime` to `date`. I usually recommend the form that you have in your comment (because that is sargable on more databases), but i this case, I actually intentionally kept it this way. – Gordon Linoff Aug 21 '15 at 13:41
@GordonLinoff - I'd be interesting reading about that feature, do you have a link? The only stuff I'm managing to find is the older, general advice about functions. – Clockwork-Muse Aug 21 '15 at 13:47
@Clockwork-Muse do a quick search for sql server date sargable and you will dozens of references. The cast/convert to date or datetime is sargable. It is one of the few functions that are. – Sean Lange Aug 21 '15 at 14:11
@Clockwork-Muse my answer to this question http://stackoverflow.com/questions/25564482/how-to-compare-datetime-with-only-date-in-sql-server/25564539#25564539 shows a query plan where a CAST(myDate As Date) = '2014-01-01' into a > and < Seek – Steve Ford Aug 21 '15 at 15:57
@SteveFord - oh, of _course_ it's an exclusive lower bound.... whyyyy? It has to know it's a datetime/2/etc type by that point (since a string isn't guaranteed to be SARGable), so the proper inclusive lower should be available... – Clockwork-Muse Aug 21 '15 at 22:43

score 0 · Answer 2 · edited Dec 24 '17 at 09:34

0

You are probably missing a index on the columns EmpId and submitted_dt.

The CONVERT(date, submitted_dt) = '2015-07-15' will make any index useless on this column, convert the '2015-07-15' to the type of the column (date) to make the compare more efficient.

Have a look at the query plan to see what else you are missing.

edited Dec 24 '17 at 09:34

marc_s

732,580
175
1,330
1,459

answered Aug 21 '15 at 13:36

Peter

27,590
8
64
84

JosephStyons · Answer 3 · 2015-08-21T14:04:36.657

What datatype is submitted_dt? If it is a date, you don't need to convert it. If it isn't a date, then why not?

The way it is now, the string '2015-07-15' has to be converted to a date for every single row in the Employee table. Push it into a variable first. That should be a good start. If it's still slow, then try getting the explain plan and see what part is the most expensive. If you can share that, it will help troubleshoot quite a bit more.

declare @submitted_dt datetime = '2015-07-15';
select
  EmpId
 ,submitted_dt  
FROM
  Employee 
where id NOT IN (
  select MAX(id) as id
  FROM Employee
  where submitted_dt = @yesterday
  group by EmpId
  )
  and submitted_dt = @yesterday

score 0 · Answer 4 · answered Aug 21 '15 at 13:38

If it is the second query that is taking long, and you are just looking for id's that are NOT the MAX(id), you can use an exists to exclude those values, instead of NOT IN, which can usually slow down your query:

select  EmpId,submitted_dt  
FROM Employee E1
where EXISTS
(select 1 FROM Employee E2 where     
CONVERT(date,submitted_dt)='2015-07-15' 
and E2.id > E1.id ) 
and CONVERT(date,submitted_dt)='2015-07-15'

How to debug a slow SQL Server query?

4 Answers4