Do you have (if any) a good practice guide for the creation of dashboards in the designer?
We have an experience where a client has about 60 dashboards published, and many of them have 22 gauge with queries to queries in a database, and the response time for the dashboard to load was about 20 minutes.
We would like to know if there is a maximum number of queries that a dashboard can have to a database without affecting its performance.
I recently had a similar problem where the loading of the Dashboards took a long time, this problem appeared after updating to 20.4 cumulative update 5, within the probes that it updates in the Operation Console there is a probe called "ump_dashboard", in the cumulative update 5 install version 20.45, if this is your case, you must reinstall this same probe in version 20.41, in my case this solved the problem.
The environment I mention is in version 20.3.3, it was working correctly for a long time, however, after a dashboard with many gauges was published it started to slow down all the dashboards, it had to be removed and after that the other dashboards started to load immediately.
What was the total size in MB of that dashboard?
How can i search this information?
It's not exactly clear from your original question what the data sources are for these gauges but you seem to imply that they are SQL queries against a database.
It would be good to get a look at them if that's the case so that comment can be made to improve their efficiency.
But to move forward with the assumption that these are SQL queries against the UIM database, my starting point in looking at this would be to take the individual queries and run them through a SQL profiling tool to see if there's anything obvious that could be improved. A couple years ago we had some performance issues and it boiled down to the need to add another index to the s_qos_data table to support the queries we were executing.
Also, a gauge is looking at typically the most recent value, you can pull this from s_qos_snapshot instead of the rn table for instance.
If you are doing computation in the sql query, it might be reasonable to consider doing that computation in a script and storing the result for use instead. If you have a hundred users opening the same page it will be running that query a hundred times so it can be better, if that query is expensive, to have a script run that query and store the result and the gauge pull that result. Obviously, that disconnects the page refresh rate from the update of the data but that might be ok.
Essentially the idea being that all these queries have to run on the page refresh interval and they run along side all the other queries and page refreshes happening for another users. So a slow query multiplies by the users and impacts the sql server directly.
To the question about the number of gauges allowed, obviously the more widgets the more work it takes to populate the page but it really depends on the data source complexity. I have forms with 50-100 widgets that run fine and others with a couple that are "slow" just because they have significantly more complex queries underneath them.
The database from which the information is being consulted is different from that of UIM, in fact it is a highly transactional database.
I understand from the client's information, the queries are basic and are only consulting the percentage of effective transactions of a service, on the other side, the number of users consuming this dashboard at the same time can be around 4-5 operators, adding to the other dashboards (which also consult this database) it is possible that in total many are being consumed at the same time.
It was explained to the client that possibly the cause of the slowness is the large number of queries that are being made at the same time, adding to its high consumption by external programs. But I wanted to find out if there was any limitation in the dashboard designer regarding the number of simultaneous queries that could be made.