Home:ALL Converter>Optimize SQL Query or archive the large table

Optimize SQL Query or archive the large table

Ask Time:2020-02-13T22:25:42         Author:user12366709

Json Formatter

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?

Author:user12366709,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/60210074/optimize-sql-query-or-archive-the-large-table
yy