October 20, 2016 at 1:54 pm
Hello,
I have a view selecting from two tables that combines the results with a UNION ALL. Both SELECT statements are selecting from a column like as follows:
SELECT CAST('ACTION' as NVARCHAR(255)) as TYPE,
column(1)..
column(2)..
UNION ALL
SELECT 'EVENTS',
column(1)..
column(2)..
Then there's a stored procedure that selects a series of columns then does an ORDER BY by the TYPE column. I was told the ORDER BY the TYPE column is a must.
Below is the code:
SELECTe.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,
COALESCE(tids.PRIMARY_TRACKER_ID, e.TRACKER_ID) 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 COALESCE (tids.PRIMARY_TRACKER_ID_HASH, e.TRACKER_ID_HASH) BETWEEN @p1 AND @p2
AND e.DATE_ID >= @p3
ORDER BY e.TYPE DESC;
Since I'm unable to create an indexed view due to the UNION BY, is there anything else I could do to enhance the performance of this query?
Is there a way I could somehow index the TYPE, even though this doesn't exist within any of the tables?
Thank you for your answers 🙂
October 20, 2016 at 2:50 pm
PFlorenzano-641896 (10/20/2016)
Hello,I have a view selecting from two tables that combines the results with a UNION ALL. Both SELECT statements are selecting from a column like as follows:
SELECT CAST('ACTION' as NVARCHAR(255)) as TYPE,
column(1)..
column(2)..
UNION ALL
SELECT 'EVENTS',
column(1)..
column(2)..
Then there's a stored procedure that selects a series of columns then does an ORDER BY by the TYPE column. I was told the ORDER BY the TYPE column is a must.
Below is the code:
SELECTe.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,
COALESCE(tids.PRIMARY_TRACKER_ID, e.TRACKER_ID) 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 COALESCE (tids.PRIMARY_TRACKER_ID_HASH, e.TRACKER_ID_HASH) BETWEEN @p1 AND @p2
AND e.DATE_ID >= @p3
ORDER BY e.TYPE DESC;
Since I'm unable to create an indexed view due to the UNION BY, is there anything else I could do to enhance the performance of this query?
Is there a way I could somehow index the TYPE, even though this doesn't exist within any of the tables?
Thank you for your answers 🙂
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.
2) The Date filter could return no rows or all data in the table. Since this is in sproc I would consider OPTION (RECOMPILE) to avoid storing either plan and then get totally screwed by the opposite call.
3) Did you check for WHY it was slow? IO? wait stats? sp_whoisactive? query plan? Returning a bajillion rows? etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 21, 2016 at 1:28 am
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;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply