SQL server optimization of complex queries

The SQL Server query optimizer may incorrectly parse a complex query and not send certain query criteria to the Historian OLE DB provider for handling. This can cause unexpected results for the data.

If you suspect that this is happening, use SQL Server Management Studio tools to examine the query plan that the optimizer is using and then modify your query so that the needed criteria gets directed to the Historian OLE DB provider.

For example, the following query will be incorrectly parsed:

SELECT GETDATE()

DECLARE @TagList TABLE (TagName nvarchar(256))

INSERT @TagList

SELECT 'SysTimeSec' UNION

SELECT 'SysPerfCPUTotal'

-- Prevent the TagName criteria from being sent to the Historian OLE DB provider (incorrect)

SELECT DateTime, h.vValue, h.TagName

FROM History h

INNER REMOTE JOIN @TagList l

ON h.TagName = l.TagName

WHERE DateTime >= DATEADD(hour,-1,GETDATE())

AND DateTime < GETDATE()

AND wwRetrievalMode = 'AVG'

AND wwCycleCount=1

GO

To correct this issue, rewrite the query so that the tagname criteria is passed to the Historian OLE DB provider correctly.

SELECT GETDATE()

DECLARE @TagList TABLE (TagName nvarchar(256))

INSERT @TagList

SELECT 'SysTimeSec' UNION

SELECT 'SysPerfCPUTotal'

-- Force the TagName criteria to be sent to the InSQL OLE DB Provider (correct)

SELECT DateTime, h.vValue, h.TagName

FROM @TagList l

INNER REMOTE JOIN History h

ON h.TagName = l.TagName

WHERE DateTime >= DATEADD(hour,-1,GETDATE())

AND DateTime < GETDATE()

AND wwRetrievalMode = 'AVG'

AND wwCycleCount=1

GO