Query to find instances of two types of events occurring more than one second apart

Question

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 < '20180701'
        and innerBO.CreationDate > '20180101'
        and innerBO.BusinessObjectId = bo.BusinessObjectId
) > 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?

Answer 1

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 ) > 1 or datediff( second, B.min, A.max ) > 1
group by A.BusinessObjectId

I have not included the six month restriction.

Answer 2

You need to work on below statement of your code. You are INNER joining two tables but not with both tables reference.

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

Please mention your Query-result requirement, so according to that we can help with code sample.