Parameterized query performance

Archived Topics
1

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;
2

Update: I figured out that the slowness comes from SQL Server being unable to optimize the queries that contain parameters, since it doesn’t know ahead of time what they will be. I was able to get much better performance by applying the parameterized filtering criteria to the data once and creating a temporary table of filtered data to run the main query against.

The new query executes in a second and a half or so for the same 4 hour window, which is acceptable. So basically I answered my own question, but if you know a better way or if you can explain it better than I can, please chime in.

New query:

IF OBJECT_ID('tempdb.dbo.#CDUNames') IS NOT NULL 
	drop table #CDUNames;
IF OBJECT_ID('tempdb.dbo.#POUNames') IS NOT NULL 
	drop table #POUNames;
IF OBJECT_ID('tempdb.dbo.#FilteredData') IS NOT NULL 
	drop table #FilteredData;
	
select * into #CDUNames from Assigned_CDU_Names;
select * into #POUNames from POU_Names;
select * into #FilteredData from data_log where 
	_VAL is not null 
	AND point_id LIKE 'OCP.%'+?+'.POU.%'+?+'.Demand_Active'
	AND point_id IN (SELECT point_id FROM #POUNames WHERE Name LIKE '%'+?+'%')
	AND point_id IN (SELECT point_id FROM #CDUNames WHERE CDU_Name LIKE '%'+?+'%')
	AND timestamp >= ?
	AND timestamp < ?;
		

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 #FilteredData as l1
JOIN #FilteredData as l2 ON l1.point_id = l2.point_id AND l2.timestamp = (SELECT top 1 timestamp FROM #FilteredData WHERE timestamp > l1.timestamp AND point_id = l1.point_id 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 
	
ORDER BY l1.timestamp desc;