I have never been able to get a report in SQL to load to the report viewer. I have reports that work fine in tag historian, but as soon as they are set to an SQL query the report viewer faults. It will fault on a connection lost, time out, and Java heap space. There is very little information on troubleshooting this in the manual or the videos.
The video shows a .csv. The example in the manual shows only a few items. How does this work with a real database?
Is there a limit on how large a data table can be?
What happens in an installation where the database could hold a huge amount of data?
My queries show properly in the report designer. I can sample as little or as much data as I want. I even tried a preview limit of 1 and the report viewer will not work.
On the report viewer page I have even limited data by binding the viewer to an hour and even less, but it will not load a report from my database.
Table component and power table will load the entire data table and even multiple data from different tables. Report viewer only errors.
What properties need set that are not stated in the manual or shown on the videos?
Failures on Java heap space are purely a memory problem. The reporting system is a memory hog. /-:
Database connection loss and/or timeout is typically a problem with the database. Usually lack of query optimization due to improper indices. It is not unusually for such databases to spend minutes scanning entire tables to match a where clause, whether returning a dozen rows or tens of thousands. The answer is almost always creation or tweaking of database indices.
You’ll have to share actual queries with us, and the execution time they get when run in the query browser vs. table component vs. direct database interface (Management Studio, pgAdmin3/4, SQL Developer, …).
2 Likes
I’ve heard this before and it does not work. I set all memory settings for the clients and the designer for 4GB that leaves me about 4 to run my PC. The files have about 30 lines of 4 columns with small data (largest is a float of whatever Ignition makes it)
When watching the status in the GW it seldom goes about 600MB
You should open a ticket with support and share your system remotely with them. I can’t tell what’s wrong with the scant information you’ve provided.
Paul has a copy of my .proj if that helps. I’m at a loss as to why so much memory use. I used the query builder to build my query, and even if I recall only 2 items on the same table ( 2 INTs) I get the same result.
I did. I was hoping someone else had the same problem and could post some of their solution.
Is the gateway on the same machine? And the database? How much memory is the gateway allowed to use? The report is actually generated in the gateway, after all.
And you need to run the query in the designer’s “Query Browser” to see the execution time – not the query builder.
I’ve been watching memory use. I can watch the query. When I do them in report designer it is nearly instantaneous. I’m about to relaunch everything again. Once it faults I need to at least close all the Ignition items and then relaunch the gateway and designer. I have the Gateway set to 4GB. Everything is on the same PC. W10, with plenty of disk space left (near 500GB) and should be plenty of RAM (16GB and since I have been having problems with reports I have 12 GB of it allocated to Ignition).
I don’t have an answer for you – whatever’s going wrong is either a trivial misconfiguration that IA remote access would expose, or something serious that only IA will be able to identify/solve.
That is what I’ve been hoping, but I’ve been fighting this over a month.
However, I just got the first Gateway memory warning. 3.9GB. If that is the case how do I make the project use less memory with the viewer. The only thing different is I added a viewer with an SQL query, and that window is not launched yet.
I closed the project and gateway. Relaunched the gateway and it is still using 4GB of RAM. First time I’ve seen it, but it may be the problem. Without a project or client open how can it be using so much memory? maybe this is why I’ve had to uninstall Ignition and reinstall it several times since I started using it.