Ignition 7.8, MS SQL Server 2008 SP1 Express
So I’m doing some query optimization on a report, and I’m having some trouble getting the parameterized query performance to match the unparameterized query performance. The query is a bit involved, creating a couple temporary tables and doing several joins and subqueries. With parameters, the query for about 4 hours of data takes ~6 seconds. However, if I remove the ? params and hard-code the values I want to use, it takes less than one second. I know that parameterized queries are a much better idea than sticking user data into a query, but this performance hit is quite significant (for longer time spans, it’s make or break as the slower query will time out, and it seems to be O(n) time). I have a total of 6 params… is there anything I can do to make the parameterized query faster? Does it use the SQL Server query planner / optimizer or is it a different one? Is there any way to see the query plan Ignition is using?
Query: (POU_Names and Assigned_CDU_Names are views also based on DATA_LOG…)
IF OBJECT_ID('tempdb.dbo.#CDUNames') IS NOT NULL drop table #CDUNames; IF OBJECT_ID('tempdb.dbo.#POUNames') IS NOT NULL drop table #POUNames; select * into #CDUNames from Assigned_CDU_Names; select * into #POUNames from POU_Names; select l1.timestamp AS [Start Time], substring(l1.point_id, 0, 15) AS [Tag], n.name AS [Name], c.CDU_Name AS [CDU Name], datediff(s, l1.timestamp, l2.timestamp) AS [Duration] from dbo.DATA_LOG as l1 JOIN dbo.DATA_LOG as l2 ON l1.point_id = l2.point_id AND l2.timestamp = (SELECT top 1 timestamp FROM dbo.DATA_LOG WHERE timestamp > l1.timestamp ORDER BY timestamp ASC) INNER JOIN #POUNames AS n ON l1.point_id = n.point_id INNER JOIN #CDUNames AS c ON l1.point_id = c.point_id WHERE l1._VAL = 1 AND l2._VAL = 0 AND l1._VAL IS NOT NULL AND l1.point_id LIKE 'OCP.%'+?+'.POU.%'+?+'.Demand_Active' AND l1.point_id IN (SELECT point_id FROM #POUNames WHERE Name LIKE '%'+?+'%') AND CDU_Name LIKE '%'+?+'%' AND l1.timestamp >= ? AND l1.timestamp < ? ORDER BY l1.timestamp desc;