I've been looking into a performance issue for the last few days and although I've improved the query runtime from 48 seconds to 11 seconds, I feel that it can be improved even further. The issue is that I'm not sure what the next steps could be in order to do that, so I thought I'd post it here!
Execution plan is here
Here is what I've been focusing on at the moment;
After the Clustered Index Scan and Filter, it then goes to a Hash Match (Right Anti Semi Join).
After that, we get the bottleneck;
Here is the script which is being run;
SELECT AVG([F_TASKS].[TA_SEQ]) [TA_SEQ]
,COUNT([F_TASK_TIME].[TT_SEQ]) [TimeRecordsCount]
FROM [F_TASKS] WITH (NOLOCK)
LEFT OUTER JOIN [F_TASK_TIME] WITH (NOLOCK) ON [F_TASKS].[TA_SEQ] = [F_TASK_TIME].[TT_FKEY_TA_SEQ]
AND [F_TASK_TIME].[TT_FKEY_TA_SEQ] IN (
SELECT [F_TASKS].[TA_SEQ]
FROM [F_TASKS] WITH (NOLOCK)
WHERE (
[F_TASKS].[TA_FKEY_CTR_SEQ] = 0
OR [F_TASKS].[TA_FKEY_CTR_SEQ] IS NULL
OR [F_TASKS].[TA_FKEY_CTR_SEQ] = ''
OR [F_TASKS].[TA_FKEY_CTR_SEQ] IN (
SELECT [ContractGroupsContract].[CTR_SEQ]
FROM [ContractGroupsContract]
INNER JOIN [AccountContractGroups] ON [AccountContractGroups].[ContractGroupId] = [ContractGroupsContract].[ContractGroupId]
WHERE [AccountContractGroups].[AccountId] = 93
AND [AccountContractGroups].[Deleted] = 0
AND [ContractGroupsContract].[Deleted] = 0
)
)
AND (
[F_TASKS].[TA_SEQ] > 0
OR [F_TASKS].[TA_SEQ] IS NULL
)
)
WHERE (
[F_TASKS].[TA_SEQ] IN (
SELECT [F_TASKS].[TA_SEQ]
FROM [F_TASKS] WITH (NOLOCK)
WHERE (
[F_TASKS].[TA_SEQ] NOT IN (
SELECT [ProjectTaskDetails].[TaskId]
FROM [ProjectTaskDetails] WITH (NOLOCK)
INNER JOIN [Projects] WITH (NOLOCK) ON [ProjectTaskDetails].[ProjectId] = [Projects].[ProjectId]
WHERE (
(
[Projects].[StateId] IN (
6
,7
,8
)
)
AND (
[ProjectTaskDetails].[Deleted] = 0
OR [ProjectTaskDetails].[Deleted] IS NULL
)
AND (
[ProjectTaskDetails].[ProjectTaskDetailId] > 0
OR [ProjectTaskDetails].[ProjectTaskDetailId] IS NULL
)
)
AND (
[Projects].[ProjectId] = 0
OR [Projects].[ProjectId] IS NULL
OR [Projects].[ProjectId] = ''
OR (
SELECT COUNT([ProjectContracts].[ContractId])
FROM [ProjectContracts]
WHERE [Projects].[ProjectId] = [ProjectContracts].[ProjectId]
AND [ProjectContracts].[ContractId] NOT IN (
SELECT [ContractGroupsContract].[CTR_SEQ]
FROM [ContractGroupsContract]
INNER JOIN [AccountContractGroups] ON [AccountContractGroups].[ContractGroupId] = [ContractGroupsContract].[ContractGroupId]
WHERE [AccountContractGroups].[AccountId] = 93
AND [AccountContractGroups].[Deleted] = 0
AND [ContractGroupsContract].[Deleted] = 0
)
) = 0
)
AND (
[Projects].[Deleted] = 0
OR [Projects].[Deleted] IS NULL
)
)
)
AND (
[F_TASKS].[TA_FKEY_CTR_SEQ] = 0
OR [F_TASKS].[TA_FKEY_CTR_SEQ] IS NULL
OR [F_TASKS].[TA_FKEY_CTR_SEQ] = ''
OR [F_TASKS].[TA_FKEY_CTR_SEQ] IN (
SELECT [ContractGroupsContract].[CTR_SEQ]
FROM [ContractGroupsContract]
INNER JOIN [AccountContractGroups] ON [AccountContractGroups].[ContractGroupId] = [ContractGroupsContract].[ContractGroupId]
WHERE [AccountContractGroups].[AccountId] = 93
AND [AccountContractGroups].[Deleted] = 0
AND [ContractGroupsContract].[Deleted] = 0
)
)
AND (
[F_TASKS].[TA_SEQ] > 0
OR [F_TASKS].[TA_SEQ] IS NULL
)
)
)
AND (
[F_TASKS].[TA_FKEY_CTR_SEQ] = 0
OR [F_TASKS].[TA_FKEY_CTR_SEQ] IS NULL
OR [F_TASKS].[TA_FKEY_CTR_SEQ] = ''
OR [F_TASKS].[TA_FKEY_CTR_SEQ] IN (
SELECT [ContractGroupsContract].[CTR_SEQ]
FROM [ContractGroupsContract]
INNER JOIN [AccountContractGroups] ON [AccountContractGroups].[ContractGroupId] = [ContractGroupsContract].[ContractGroupId]
WHERE [AccountContractGroups].[AccountId] = 93
AND [AccountContractGroups].[Deleted] = 0
AND [ContractGroupsContract].[Deleted] = 0
)
)
AND (
[F_TASKS].[TA_SEQ] > 0
OR [F_TASKS].[TA_SEQ] IS NULL
)
GROUP BY [F_TASKS].[TA_SEQ]
I'm fairly new to execution plan analysis, so any suggestions on this issue would be very helpful.
Thanks in advance.
