SQL Server Long Query -

OK ... I do not understand how long this query is taking (MSSQ server 2005) :
[typical output 3K rows, 5.5 min execution time]

  select dbo.Point.PointDriverID, dbo.Point.AssetID, dbo.Point.PointID, dbo.Point. PointTypeID, dbo.Point.PointName, dbo.Point.ForeignID, dbo.Pointtype.TrendInterval, (dbo.Point.trendpts, 5) combined TrendPts, LastTimeStamp = PointDTTM, LastValue = PointValue, dbo.Point time zone from the left Join dbo.PointType.PointTypeID = dbo.Point.PointTypeID on dbo.PointType dbo.PointData on the left Join dbo.Point.PointID = dbo.PointData.PointID and PointDTTM = (select max (PointDTTM) PointData.PointID Where dbo.PointData = Point.PointID) Left dbo.SiteAsset Join the dbo.SiteAsset.AssetID = dbo.Point.AssetID dbo left. Site.SiteID = dbo.SiteAsset.SiteID dbo.Site Join where = 1 onlinetrended and WantTrend = 1  

PointData is Biggun, but I thought its definition. I want permission to take what I want easily enough:

  CREATE TABLE [dbo] [PointData] ([PointID] [int] not NULL, [PointDTTM] [datetime] (Pad_indx = off, statics_NOCOMPUT = off, IGNORUPUK_KY = off, with NULL, [real] tap, [dataquite] [tinent] null, contract [PK_PointData_1] primary key classified ([POINT] ASC, [pointdtm] ASC) , At ALLOW_ROW_LOCKS =, ALLOW_PAGE_LOCKS =) [primary]) [Prath Mik], with [PointDTTM] DESC (PAD_INDEX = closed, STATISTICS_NORECOMPUTE = close nonclustered index [IX_PointDataDesc] ON [dbo]. [PointID] ([PointID] ASC GO, SORT_IN_TEMPDB = closed, IGNORE_DUP_KEY = off, DROP_EXISTING = close online = off, ALLOW_ROW_LOCKS =, [primary] GO on ALLOW_PAGE_LOCKS =)  

PointData is 550m lines, and points (source PointID of) only 28K rows I to see an indexed Have tried, but I do not understand how the last time out of it in a consistent way (no max, no subquery, no CTE) / Get value.

It runs two hours a day, and after running it, I put more data in those 3K point-Id which I have selected I thought of making the last time / final value table directly in point, but It seems like a wrong approach.

Am I missing something, or should I rebuild something? (I am also DBA, but I know that there is something about lot ADD!)

For starters, try to get rid of the correlated subquery; I have also re-wrote the table with the alias, making it very easy to read

Try it (and write less!):.

  Select p.PointDriverID, p. assetid, p.PointID, p.PointTypeID, p.PointName, p.ForeignID, pt.TrendInterval, (p.trendpts, 5) as TrendPts, LastTimeStamp = PointDTTM, LastValue = PointValue, the dbo.Point P Left time- area dbo involved Join KPointType PT to pt.PointTypeID = p.PointTypeID lEFT JOIN Join dbo.PointData p.PointID PD = pd.PointID interiors (PointID, Max (PointDTTM) select dbo.PointData group as MaxPointDTTM the PDM on) pd.PointID by PointID = pdm.PointID and pd.PointDTTM = pdm.MaxPointDTTM lEFT JOIN JOIN dbo.SiteAsset bit sa.AssetID = p.AssetID left s.SiteID = sa.SiteID dbo.Site Where onlinetrended = 1 and WantTrend = 1