I have 3 tables on my database:
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?