Seeking help to improve this query performance.
SELECT DsJobStat.JobName AS JobName , AJF.ApplGroup AS GroupName , DsJobStat.JobStatus AS JobStatus , AVG(CAST(DsJobStat.ElapsedSec AS FLOAT)) AS ElapsedSecAVG , AVG(CAST(DsJobStat.CpuMSec AS FLOAT)) AS CpuMSecAVG FROM DsJobStat, AJF WHERE DsJobStat.NumericOrderNo=AJF.OrderNo AND DsJobStat.Odate=AJF.Odate AND DsJobStat.JobName NOT IN( SELECT [DsAvg].JobName FROM [DsAvg] ) GROUP BY DsJobStat.JobName , AJF.ApplGroup , DsJobStat.JobStatus HAVING AVG(CAST(DsJobStat.ElapsedSec AS FLOAT)) <> 0;
Execution message,
(0 row(s) affected) Table 'AJF'. Scan count 11, logical reads 45, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'DsAvg'. Scan count 2, logical reads 1926, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'DsJobStat'. Scan count 1, logical reads 3831235, physical reads 85, read-ahead reads 3724396, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected) SQL Server Execution Times: CPU time = 67268 ms, elapsed time = 90206 ms.
Tables structure,
-- 212271023 rows CREATE TABLE [dbo].[DsJobStat]( [OrderID] [nvarchar](8) NOT NULL, [JobNo] [int] NOT NULL, [Odate] [datetime] NOT NULL, [TaskType] [nvarchar](255) NULL, [JobName] [nvarchar](255) NOT NULL, [StartTime] [datetime] NULL, [EndTime] [datetime] NULL, [NodeID] [nvarchar](255) NULL, [GroupName] [nvarchar](255) NULL, [CompStat] [int] NULL, [RerunCounter] [int] NOT NULL, [JobStatus] [nvarchar](255) NULL, [CpuMSec] [int] NULL, [ElapsedSec] [int] NULL, [StatusReason] [nvarchar](255) NULL, [NumericOrderNo] [int] NULL, CONSTRAINT [PK_DsJobStat] PRIMARY KEY CLUSTERED ( [OrderID] ASC, [JobNo] ASC, [Odate] ASC, [JobName] ASC, [RerunCounter] ASC )); -- 48992126 rows CREATE TABLE [dbo].[AJF]( [JobName] [nvarchar](255) NOT NULL, [JobNo] [int] NOT NULL, [OrderNo] [int] NOT NULL, [Odate] [datetime] NOT NULL, [SchedTab] [nvarchar](255) NULL, [Application] [nvarchar](255) NULL, [ApplGroup] [nvarchar](255) NULL, [GroupName] [nvarchar](255) NULL, [NodeID] [nvarchar](255) NULL, [Memlib] [nvarchar](255) NULL, [Memname] [nvarchar](255) NULL, [CreationTime] [datetime] NULL, CONSTRAINT [AJF$ PrimaryKey] PRIMARY KEY CLUSTERED ( [JobName] ASC, [JobNo] ASC, [OrderNo] ASC, [Odate] ASC )); -- 413176 rows CREATE TABLE [dbo].[DsAvg]( [JobName] [nvarchar](255) NULL, [GroupName] [nvarchar](255) NULL, [JobStatus] [nvarchar](255) NULL, [ElapsedSecAVG] [float] NULL, [CpuMSecAVG] [float] NULL );
Execution plan https://www.brentozar.com/pastetheplan/?id=rkUVhMlXM
Thanks advance