TheSQLGuru (10/20/2016)
I really didn't follow what said about a view and then sproc. But I have these issues:
1) first and foremost, you have a function around a pair of columns in the WHERE clause. Death on a hotplate there.
You could try to split this into 2 queries and union the data
SELECT
e.ID,
e.[TYPE],
e.EVENT_DATE,
e.TENANT_ID,
e.WORKSPACE_ID,
e.TOUCHPOINT_ID,
e.INTERACTION_ID,
e.PROPOSITION_ID,
e.ACTIVITY_TYPE_ID,
e.ACTION_ID,
e.RECOGNITION_STATUS,
e.CAUSE,
e.IN_CONTROL_GROUP,
e.IS_COMPLETE,
tids.PRIMARY_TRACKER_ID TRACKER_ID
FROM EVENTS e
INNER OUTER JOIN TRACKER_IDS tids
ON e.TRACKER_ID = tids.TRACKER_ID
AND e.TENANT_ID = tids.TENANT_ID
AND e.WORKSPACE_ID = tids.WORKSPACE_ID
WHERE tids.PRIMARY_TRACKER_ID_HASH BETWEEN @p1 AND @p2
AND e.DATE_ID >= @p3
UNION ALL
SELECT
e.ID,
e.[TYPE],
e.EVENT_DATE,
e.TENANT_ID,
e.WORKSPACE_ID,
e.TOUCHPOINT_ID,
e.INTERACTION_ID,
e.PROPOSITION_ID,
e.ACTIVITY_TYPE_ID,
e.ACTION_ID,
e.RECOGNITION_STATUS,
e.CAUSE,
e.IN_CONTROL_GROUP,
e.IS_COMPLETE,
e.TRACKER_ID
FROM EVENTS e
LEFT OUTER JOIN TRACKER_IDS tids
ON e.TRACKER_ID = tids.TRACKER_ID
AND e.TENANT_ID = tids.TENANT_ID
AND e.WORKSPACE_ID = tids.WORKSPACE_ID
WHERE tids.PRIMARY_TRACKER_ID_HASH IS NULL
AND e.TRACKER_ID_HASH BETWEEN @p1 AND @p2
AND e.DATE_ID >= @p3
ORDER BY [TYPE] DESC;