# 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


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
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))
where EventTypeId = 11
and innerBO.CreationDate < '20180701'
and innerBO.CreationDate > '20180101'
) > 1


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

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
) as A
inner join
( select BusinessObjectId, min(DateEvent) as min, max(DateEvent) as max
from CustomEvent where EventTypeId = 10

from CustomEvent ce inner join BusinessObject innerBO on ce.BusinessObjectId = bo.BusinessObjectId