Slow query joining and grouping tables into a report

Ignition
1

I have a few report viewers on the same window and I control their visibility by a dropdown selector.
I am experiencing unusually long and unpredictable response times to retrieve the data.
When I first look at one of the reports I may wait up to a minute to actually retrieve data and after I have the data, changing the timeframe requires no additional wait times, So my range could be 2 hours and I may wait 1 minute and once I have those it will be a fraction of a second to get data between 2 months.
I can’t understand WHY the query is sometimes slow and sometimes instant but here is an example of one of my report data bindings:

[code]if {Root Container.HighBayFaults.open} = 1
begin
SELECT ROUND(SUM(t.HBNorthJam) + 0.4,0) AS HBNorthJam, ROUND(SUM(t.CDIMergeJam) + 0.4,0) AS CDIMergeJam, ROUND(SUM(t.ShoesMergeJam) + 0.4,0) AS ShoesMergeJam,
ROUND(SUM(t.ShoesMergeFull) + 0.4,0) AS ShoesMergeFull,
ROUND(SUM(t.Door81MergeFull) + 0.4,0) AS Door81MergeFull, ROUND(SUM(t.MergeFull) + 0.4,0) AS MergeFull,
CAST(DATEPART(month, p.t_stamp) AS varchar) + ‘-’ + RIGHT(‘0’ + CAST(DATEPART(day, p.t_stamp) AS varchar),2) + ’ ’ +
RIGHT(‘0’ + CAST(DATEPART(hour, p.t_stamp) AS varchar), 2) + ‘:00’ AS [Hour],
SUM(p.ShoesMergeJams) AS ShoesMergeJams, SUM(p.Door81MergeJams) AS Door81MergeJams, SUM(p.NorthHighBayMergeJams) AS NorthHighBayMergeJams
FROM HighBayDowntime t

FULL OUTER JOIN HighBay p
ON CAST(DATEPART(month, p.t_stamp) AS varchar) + ‘-’ + RIGHT(‘0’ + CAST(DATEPART(day, p.t_stamp) AS varchar),2) + ’ ’ +
CAST(DATEPART(hour, p.t_stamp) AS varchar) + ’ ’ + CAST(DATEPART(minute, p.t_stamp) AS varchar)=
CAST(DATEPART(month, t.t_stamp) AS varchar) + ‘-’ + RIGHT(‘0’ + CAST(DATEPART(day, t.t_stamp) AS varchar),2) + ’ ’ +
CAST(DATEPART(hour, t.t_stamp) AS varchar) + ’ ’ + CAST(DATEPART(minute, t.t_stamp) AS varchar)

WHERE p.t_stamp BETWEEN Cast(’{Root Container.HighBayFaults.StartDate} {Root Container.HighBayFaults.Start}’ as datetime)
and Cast(’{Root Container.HighBayFaults.EndDate} {Root Container.HighBayFaults.End}’ as datetime)

Group By CAST(DATEPART(month, p.t_stamp) AS varchar) + ‘-’ + RIGHT(‘0’ + CAST(DATEPART(day, p.t_stamp) AS varchar),2) +
’ ’ + RIGHT(‘0’ + CAST(DATEPART(hour, p.t_stamp) AS varchar), 2) + ‘:00’
ORDER BY Hour
end
else
begin
select 0
end[/code]

The idea here is to join and group the data by HOUR and the range is selectable by using a daterange slider component. I have a feeling the problem lies with the conversion of datetime to varchar but am not sure how this could be made more efficient.
Any ideas?

2

Sounds like the db is caching.

Or the tables are changing rapidly enough the the meta info is getting out of data. Thus the query engine is using bad data to optimize the query. After your firt query, the meta data is updated the optimizer is working (much) better.

3

You could try compiling this into a stored procedure to see if it’s a temporary compilation that is taking so long.

You could also try eliminating the processing of the calculations by exporting the SUM and COUNT data into a table of its own and doing a simple JOIN instead of the FULL OUTER JOIN. (It doesn’t sound like that’s the problem if you are accessing data outside your original range, but you never know.)

I was able to solve a similar problem by running a Gateway Timer Script to export the data into a new table (hourly, but you could have it run more often if needed) and joining to that new table instead of trying to do the SUM and COUNT calculations in the same query.

4

You might also be able to speed the query up by looking at two things:

  1. Take a look at your indexes. Anything in a where clause could be sped up by a good index.

  2. Try using datetime fields on your join instead of varchars.

I hope this helps,

5

Executing this query in a stored procedure with variable time is a few seconds slower for me.
My t-stamps are indexed for both tables in the join.
My initial query is only returning a 8 X 10 or so set (in 20 seconds)
Robert, since I group the Sums by date and Hour, how might I format my date so I could only get the date and the hour?
I haven’t been able to find this for SQL SERVER…

6

I meant to use a date format in your ON clause instead of creating a varchar. Any time you’re converting timestamps to varchars will slow down your query, especially if you are then testing against it. If you just want to truncate the seconds off, then you can use datediff() to keep everything in the date format.FULL OUTER JOIN HighBay p ON dateadd(mi, datediff(mi, 0, p.t_stamp), 0) = dateadd(mi, datediff(mi, 0, t.t_stamp), 0)

You could also set your GROUP BY clause to something like this:GROUP BY DATEPART(month, p.t_stamp), DATEPART(day, p.t_stamp), DATEPART(hour, p.t_stamp)

7

thanks for your input…
I actually realized now that the WHERE clause is to blame here.
I removed it and I run very fast Queries.
Poking further I realized that I should use t.t_stamp instead of the p.t_stamp.
Now, I’m very fast. Thanks for all your suggestions, I will keep them in mind for the future!