This should be a quite easy query, but I honestly think its execution time can be improved.
select idTag,MAX(pctimestamp) AS PCTIMESTAMP,getdate() AS NOW, datediff(SECOND,MAX(pctimestamp),getdate()) AS DELAY from ValuesTagsOPC group by IdTag
This query returns 1386 rows from 'ValuesTagsOPC' table, that contains about 40 million rows, and has the following structure, retrieved by the create script:
CREATE TABLE [dbo].[ValuesTagsOPC]( [IdTag] [int] NOT NULL, [TTimeStamp] [datetime] NOT NULL, [PCTimeStamp] [datetime] NOT NULL, [Value] [nvarchar](50) NOT NULL, [Quality] [int] NOT NULL, CONSTRAINT [PK_ValuesTagsOPC] PRIMARY KEY CLUSTERED ( [IdTag] ASC, [PCTimeStamp] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Obviously, there is a clustered index on its primary key. Time and IO statistics from SQL Server are the following (sorry, it's in spanish):
(1386 filas afectadas) Tabla 'ValuesTagsOPC'. Recuento de exámenes 5, lecturas lógicas 224612, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0. Tiempos de ejecución de SQL Server: Tiempo de CPU = 9578 ms, tiempo transcurrido = 3019 ms.
I've checked that the estimated execution plan is the same as the real one, and it tells me that 94 % of the cost comes from the cluster index scan. What I don't understand is why it is not performing a seek instead of a scan, since all required fields in the query are included in the clustered index....
Thanks in advance!!