The Pharos Reports application window displays "Program Not Responding" while it waits an extended period of time for the database to return the data. When most reports run, but a handful take much longer, it's often the case that either the report criteria, like date range, is searching a very large number of records, or the database histogram stats are out-of-date.
The histogram stats are used by the query engine to determine how to search the database. When the stats no longer match the data records, the query engine generates inefficient search paths (explain plans) through the database.
You can use this query to peek at the last time stats were updated.
SELECT s.name AS stats_name
,stats_date(s.object_id, s.stats_id) AS statistics_update_date
FROM sys.stats s
WHERE EXISTS (SELECT 1 FROM sys.objects o WHERE o.object_id = s.object_id and o.is_ms_shipped = 0)
AND s.auto_created = 0
ORDER BY statistics_update_date
Then, you can use the stock procedure "sp_updatestats" to update the stats automatically. For example:
These are the most common reasons. Otherwise, there could be a more obscure reason, such as an absent table index. In such a case, please contact Pharos Support to start a formal investigation.
Thanks Greg. I'll check the histogram stats and update.