SARGable way to find records next to each other based on a time window?

Question

We have events inserted into the table - the start event and the end event. Related events have the same internal_id number and are inserted into the window for 90 seconds. We often make an independent connection on the table:

create table mytable (id bigint identity, internal_id bigint, internal_date datetime, event_number int, field_a varchar(50)) select * from mytable a inner join mytable b on a.internal_id = b.internal_id and a.event_number = 1 and b.event_number = 2 

However, every day we can have millions of related events. Our clustered key is internal_sensor, so we can filter to the partition level, but performance can still be mediocre:

 and a.internal_date >='20120807' and a.internal_date < '20120808' and b.internal_date >='20120807' and b.internal_date < '20120808' 

Is there a SARGable way to narrow it further? Adding this does not work - non-SARGable:

 and a.internal_date <= b.internal_date +.001 --about 90 seconds and a.internal_date > b.internal_date - .001 --make sure they're within the window 

This is not for a point request, so one-time requests do not help - we look for thousands of records and need information about events from the start and end events.

Thanks!

Answer 1

With this index, your query will be much cheaper:

 CREATE UNIQUE INDEX idx_iid on mytable(event_number, internal_id) INCLUDE (id, internal_date, field_a); 

The index allows you to search for event_number , rather than perform clustered index scans, and also allows you to merge merge into internal_id , rather than a hash join. The uniqueness constraint makes merge consolidation even cheaper, eliminating many-to-many merging.

See this for a more detailed explanation of merge merging.