Designer memory failure on report generation

[pdixon]

My questions are:

• Does an out of memory failure in Designer when working on a report mean that in runtime the gateway will be fragile when it tries to generate the report?
• How do I prevent out of memory problems in Designer?

The report uses query tags to retrieve motor runtimes from history and subtract values one day apart to get daily runtimes, with the following queries:

1 day old value:
SELECT TAG
FROM TABLE
WHERE DATEPART(dy,t_stamp) = DATEPART(dy,(cast(GETDATE( ) -1 as date)))
AND DATEPART(hr,t_stamp) = 0

2 day old value:
SELECT TAG
FROM TABLE
WHERE DATEPART(dy,t_stamp) = DATEPART(dy,(cast(GETDATE( ) -2 as date)))
AND DATEPART(hr,t_stamp) = 0

Then I make an expression tag to subtract runtime of 1 day old from 2 day old to get the daily runtime. I add those tags to a daily report. I forgot to set the scan class to something slower so it is the default scan class for all these tags. There are 66 tags in all (22 runtimes, 3 tags for each runtime)

When I add the 22 daily runtime tags to a report and go to Preview, after 30 minutes it still has not completed the Preview:

Looking at the console, I see memory errors:

When I try to close Designer, it won’t close:

I do not see the gateway (on which I am using Designer) running out of memory:

The only way to close Designer is to kill the Java web launcher:

My biggest concern is that if the report, which is scheduled to save to a PDF every night at 1 am, runs out of memory resources when it is generated can it crash the gateway? Can it severely impact gateway performance?

If this is only a Designer problem, how is this fixed? Do reports, query tags, or expression tags inherently have greater resource demands? Should I avoid doing reports in Ignition?

[MMaynard]

That is not a lot of data.
I have routines pulling over 750,000 records, putting them in a dataset and returning them with the web module as either CSV or JSON and it takes around 3 seconds.
Could you post your code and expression you are using?
The actual history query and the expression?
It sounds to me that you have an expression on a field that is being repeated in the report itself, instead of doing the calculation into a dataset and then using that for the report.

[pdixon]

MMaynardUSG, thank you for the response.

The expression is simple, just a subtraction:

{[.]TAG_1DAYAGO} - {[.]TAG_2DAYAGO}

Therefore, the 3 tags are the 2 query tags for which I already posted the queries, and the expression tag for the calculation. These 3 tags are for each of 22 motors, so 66 tags. I have them at the default scan class of 1 sec, OPC mode is subscribed; I should have set them to a slower rate.

The 22 expression tags are put on the report:

Pretty simple.

The system has been slow, but no symptoms of Designer problems were encountered until I built these tags and the report. Designer works until I select Preview on the report, and then I get the memory errors.

[MMaynard]

I thought your queries were just an example…
If you are querying directly against the tables instead of using querytaghistory calls then you are returning over 5 million records. No way the report module is going to handle doing calculations on that size of a dataset. 66 tags x 60 sec x 60 min x 24 hrs = 5,702,400 records.

Try switching to using tag history query calls and set your sample size interval to something like 1 minute. Or a fixed number. Then you can test bringing it up to higher amounts to find out your limit.
https://docs.inductiveautomation.com/display/DOC79/Tag+Historian+Query

[jpark]

I noticed your designer is only using 512mb. Working with the pdf reports it is recommended to have a minimum of 2gb allocated to the designer. I usually set mine to 4gb.

[pdixon]

Thank you jpark. The next time I am on site I will make that change. It appears I only have the memory problem in Designer; I do not notice any runtime problems. Do you think this is only a Designer issue? Would you expect performance or potential failure problems in runtime when the report is saved to PDF every night at 1 am?

[pdixon]

MMaynardUSG, that is very helpful, thanks! I now realize the query is actually returning records from the historian instead of returning a single value. I needed to be more careful in my approach and can see the data load is a lot bigger than I expected. The next time I am on site I will change to the tag history query calls and set the scan rate to a slower update.

My concern is whether this will make the gateway fragile in runtime when the report saves a PDF every night at 1 am? I am only noticing problems in Designer. In runtime the display navigation seems responsive while these tags are running their queries.

[pdixon]

Now that I look at Tag History Query in more detail, I do not think it will work. The aggregates that it provides does not give me the ability to query for values at specific times and dates as in the queries I posted. I think the fix is to change the scan rate.

[pturmel]

Consider making your database do both your per-day selection and your delta calculations. Something like this:

SELECT ts_day,
  sometable.tag,
  sometable.tag - LAG(sometable.tag) OVER (ORDER BY ts_day) AS tagd1,
  sometable.tag - LAG(sometable.tag, 2) OVER (ORDER BY ts_day) AS tagd2
FROM sometable INNER JOIN (
  SELECT CAST(t_stamp AS DATE) AS ts_day, MAX(t_stamp) AS t_stamp_match
  FROM sometable
  WHERE t_stamp >= someStartDate AND t_stamp < someEndDate
  GROUP BY CAST(t_stamp AS DATE)
) AS days ON sometable.t_stamp = days.t_stamp_match
ORDER BY ts_day

I suspect you’ll find the above blistering fast, as it only does the hard work on the last sample per day. Add the three select items for each tag you want deltas for. Or just the two deltas if you don’t need the tags’ final values.

{ Edited to drop the unnecessary outer query }

[pdixon]

I reproduced the issue on my test system. It shows there are no runtime issues; reports continue to be generated and saved as PDF and displays can be navigated and are responsive. Therefore, the memory problem seems to be exclusively a Designer issue.

The remedies are:

• Use a slower scan rate on the query and expression tags
• Use a more efficient query that returns fewer records
• Increase the Designer memory in Gateway Settings