I have the following SQL query:
SELECT
Event.ID,
Event.IATA,
Device.Name,
EventType.Description,
Event.Data1,
Event.Data2
Event.PLCTimeStamp,
Event.EventTypeID
FROM
Event
INNER JOIN EventType ON EventType.ID = Event.EventTypeID
INNER JOIN Device ON Device.ID = Event.DeviceID
WHERE
Event.EventTypeID IN (3, 30, 40, 41, 42, 46, 49, 50)
AND Event.PLCTimeStamp BETWEEN '2011-01-28' AND '2011-01-29'
AND Event.IATA LIKE '%0005836217%'
ORDER BY Event.ID;
I also have an index on the Event
table for the column TimeStamp
. My understanding is that this index is not used because of the IN()
statement. So my question is is there a way to make an index for this particular IN()
statement to speed up this query?
I also tried adding Event.EventTypeID IN (2, 5, 7, 8, 9, 14)
as a filter for the index on TimeStamp
, but when looking at the execution plan it doesn't appear to be using this index. Any suggestions or insight into this would be greatly appreciated.
Below is the graphical plan:
And here is a link to the .sqlplan file.
Best Answer
Given tables of the following general form:
The following index is useful:
For the query:
The filter meets the
AND
clause requirement, the first key of the index allows a seek on[TimeStamp]
for the filteredEventTypeIDs
and including theDeviceID
column makes the index covering (becauseDeviceID
is required for the join to theDevice
table).The second key of the index -
EventTypeID
is not strictly required (it could also be anINCLUDEd
column); I have included it in the key for the reasons stated here. In general, I advise people to at leastINCLUDE
columns from a filtered indexWHERE
clause.Based on the updated query and execution plan in the question, I agree that the more general index suggested by SSMS is likely the better choice here, unless the list of filtered
EventTypeIDs
is static as Aaron also mentions in his answer:Suggested index (declare it unique if that is appropriate):
Cardinality information from the execution plan (undocumented syntax, do not use in production systems):
Updated query (repeating the
IN
list for theEventType
table helps the optimizer in this specific case):Estimated execution plan:
The plan you get will likely be different because I am using guessed statistics. The general point is to give the optimizer as much information as you can, and provide an efficient access method (index) on the 4-million row
[Event]
table.