Query to find instances of two types of events occurring more than one second apart I have 3 tables on my database: BusinessObject and CustomEvent, and EventType. BusinessObject having a lot of columns, CustomEvent having the following:

Id
EventTypeId
DateEvent
CommentEvent
UserId
BusinessObjectId


I want to get all the BusinessObject having two CustomEvent with EventTypeId in (10, 11) such has the difference between the two DateEvent are more than 1 second. From the 6 first month of this year.

I came up with the following query but it's quite slow (~40 seconds). I assume there's a (lot of) ways to speed this up, but my SQL level is not good enough.

Here's the query:

select bo.Id
from BusinessObject bo
where 
(
    select top 1 DATEDIFF(second, 
                (select top 1 DateEvent from CustomEvent where EventTypeId = 11 and BusinessObjectid = bo.BusinessObjectId), 
                (select top 1 DateEvent from CustomEvent where EventTypeId = 10 and BusinessObjectid = bo.BusinessObjectId))
    from CustomEvent ce inner join BusinessObject innerBO on ce.BusinessObjectId = bo.BusinessObjectId
    where EventTypeId = 11
        and innerBO.CreationDate &lt; '20180701'
        and innerBO.CreationDate &gt; '20180101'
        and innerBO.BusinessObjectId = bo.BusinessObjectId
) &gt; 1


FYI, the query returns 4.138 rows. Here's the tables volumes:

BusinessObject  => 302k lines

CustomEvent => 4.326k lines

How could I speed this up?
I don't think your code is correct.

select top 1, without any kind of order by clause selects an essentially random record from a table, so unless you know that there can be only one event with the right EventTypeId you may be missing event combinations that are relevant.

If I understand the problem correctly, I think you need something more like this:

select A.BusinessObjectId from
( select BusinessObjectId, min(DateEvent) as min, max(DateEvent) as max 
  from CustomEvent where EventTypeId = 11
  group by BusinessObjectId 
) as A
inner join 
( select BusinessObjectId, min(DateEvent) as min, max(DateEvent) as max 
  from CustomEvent where EventTypeId = 10
  group by BusinessObjectId 
) as B on A.BusinessObjectId = B.BusinessObjectId
where datediff( second, A.min, B.max ) &gt; 1 or datediff( second, B.min, A.max ) &gt; 1
group by A.BusinessObjectId


I have not included the six month restriction.