Use a sub-SELECT with a SQL server table and an extension table

Using a sub-SELECT with a query on a normal SQL Server table and an extension table should be avoided; it is very inefficient due to the way SQL Server executes the query. For example:

SELECT TagName, DateTime, Value

FROM INSQL.Runtime.dbo.History

WHERE TagName IN (select TagName FROM SnapshotTag WHERE EventTagName = 'SysStatusEvent')

AND DateTime = '2001-12-20 0:00'

Instead, it is recommended that you use the INNER REMOTE JOIN syntax:

SELECT h.TagName, DateTime, Value

FROM SnapshotTag st INNER REMOTE JOIN INSQL.Runtime.dbo.History h

ON st.TagName = h.TagName

AND EventTagName = 'SysStatusEvent'

AND DateTime = '2001-12-20 0:00'

The results are:

In general, use the following pattern for INNER REMOTE JOIN queries against the historian is:

<SQLServerTable> INNER REMOTE JOIN <HistorianExtensionTable>

For more information on INNER REMOTE JOIN, see your Microsoft SQL Server documentation.