Ok... I don't understand why this query is taking so long (MSSQL Server 2005):
[Typical output 3K rows, 5.5 minute execution time]
SELECT dbo.Point.PointDriverID, dbo.Point.AssetID, dbo.Point.PointID, dbo.Point.PointTypeID, dbo.Point.PointName, dbo.Point.ForeignID, dbo.Pointtype.TrendInterval, coalesce(dbo.Point.trendpts,5) AS TrendPts, LastTimeStamp = PointDTTM, LastValue=PointValue, Timezone
FROM dbo.Point
LEFT JOIN dbo.PointType ON dbo.PointType.PointTypeID = dbo.Point.PointTypeID
LEFT JOIN dbo.PointData ON dbo.Point.PointID = dbo.PointData.PointID
AND PointDTTM = (SELECT Max(PointDTTM) FROM dbo.PointData WHERE PointData.PointID = Point.PointID)
LEFT JOIN dbo.SiteAsset ON dbo.SiteAsset.AssetID = dbo.Point.AssetID
LEFT JOIN dbo.Site ON dbo.Site.SiteID = dbo.SiteAsset.SiteID
WHERE onlinetrended =1 and WantTrend=1
PointData is the biggun, but I thought its definition should allow me to pick up what I want easily enough:
CREATE TABLE [dbo].[PointData](
[PointID] [int] NOT NULL,
[PointDTTM] [datetime] NOT NULL,
[PointValue] [real] NULL,
[DataQuality] [tinyint] NULL,
CONSTRAINT [PK_PointData_1] PRIMARY KEY CLUSTERED
(
[PointID] ASC,
[PointDTTM] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PointDataDesc] ON [dbo].[PointData]
(
[PointID] ASC,
[PointDTTM] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
PointData is 550M rows, and Point (source of PointID) is only 28K rows.
I tried making an Indexed View, but I can't figure out how to get the Last Timestamp/Value out of it in a compatible way (no Max, no subquery, no CTE).
This runs twice an hour, and after it runs I put more data into those 3K PointID's that I selected. I thought about creating LastTime/LastValue tables directly into Point, but that seems like the wrong approach.
Am I missing something, or should I rebuild something?
(I'm also the DBA, but I know very little about A'ing a DB!)
