I'm trying to optimize my SQL Query because it takes 12s - 15s to execute and it will show only 3 rows.
This is my query:
SELECT DISTINCT
[bs].[id] AS 'id'
, [pt].[parenttask] AS 'parent'
, [ct].[childtaskname] AS 'child'
, [tc].[TaskCategoryName] AS 'taskCat'
, [bs].[volume] AS 'volume'
, FORMAT([bs].[startTime], 'hh:mm:ss tt') AS 'startTime'
, FORMAT([bs].[endtime], 'hh:mm:ss tt') AS 'endTime'
, [bs].[ReportingDate] AS 'reportDate'
, [ts].[taskstatusname] AS 'taskStatus'
, [bs].[TotalTAT] AS 'tat'
, [ta].[TaskOwnerWorkdayID] 'workdayId'
, [pt].[teamID] 'teamId'
, [ts].[id] 'tasksStat'
FROM [tbl_DiloBillableServices] [bs] WITH(NOLOCK)
INNER JOIN [tbl_taskallocation] [ta] WITH(NOLOCK)
ON [ta].[ID] = [bs].[TaskAllocationID]
INNER JOIN [tbl_sostasks] [pt] WITH(NOLOCK)
ON [pt].[id] = [ta].[parenttaskID]
INNER JOIN [tbl_soschildtasks] [ct] WITH(NOLOCK)
ON [ct].[id] = [ta].[ChildtaskID]
INNER JOIN [tbl_taskcategory] [tc] WITH(NOLOCK)
ON [tc].[id] = [ta].[TaskCategoryID]
INNER JOIN [tbl_tasksstatus] [ts] WITH(NOLOCK)
ON [ts].[id] = [bs].[taskstatusID]
WHERE [ta].[taskownerworkdayid] = 388537
AND [bs].[reportingdate] = '2/13/2020'
AND [pt].[teamID] = 68
AND [bs].[taskstatusID] <> 5;
This is all the list of table that I've used above :
SELECT * FROM tbl_DiloBillableServices
Running time: 6s Records: 300000 +
SELECT * FROM tbl_taskallocation
Running time: 3s Records: 170000 +
SELECT * FROM tbl_sostasks
Running time: 0 Records: 10000 +
SELECT * FROM tbl_soschildtasks
Running time: 0 Records: 10000 +
SELECT * FROM tbl_taskcategory
Running time: 0 Records: 5 +
SELECT * FROM tbl_tasksstatus
Running time: 0 Records: 5 +
Is there any ways to optimize that query above? or I should archive the 2 tables above with the oldest data?
If that the case that I should archive the data, what is the better way to do that?