Very Slow SSRS report (which typically isn't)

Epicor ERP 10
1

I’m working on a custom style of the ARForm report (AR Invoice), and it takes over 10 minutes to run.

I opened the report with SSRS Report Builder to see if I can figure out what is going on.

But when I click Run, it takes a good 30 seconds before the parameters pane (where you enter the TableGuid) to appear. This makes me think something is messed up in the RDL itself. If it was the RDD alone, I’d expect the TableGuid field to appear instantly, and the processing (after entering the tableGuid) to take a while.

Any pointers on where to look?

2

I would start with following the query in this post by @josecgomez. I would also check your report server log files and look for error around the time this report is executed.

I have found a lot of report speed issues are resolved with this as well.

3

Thanks for the info.

Is there any way to cancel a running task?

As you can see, these have been running for quite some time.

Stop and Resart the Agent, and/or Stop and Restart the Service?

4

Typically you can highlight the task and click delete which will cancel them. To speed that up you can cancel and then stop and start the agent.

5

I did the query to inspect the data times on the SQL server, and found that a report run last week which averaged 144(ms?), now takes 198,554(ms?).

Short of rebooting the SQL server, any suggestions?

FWIW - Our E10 App Server is a separate box from the DB server. And the E10 DB is the only thing that runs on the SQL server.

6

Was this the data retrieval time? You could start by just restarting your SSRS service and see if that helps.I assume things like disk space, free memory, and CPU utilization all look good?
Are your standard reports running fine? Did you review your SQL Reporting services log?

7

Yes, Data Retrieval Time. ProcessingTime has also jumped by several orders of magnitude. Rendering times look roughly the same.

Everything else about the server (Memory disk usage, etc…) looks fine.

Standard reports run fine. Custom reports seem to be affected - Regardless of if they’ve recently been changed.

Where is exactly is the SQL Reporting services log? In the system event viewer, or in the SQL Server Mngmnt Studio?

8

I have seen the SQL Update (link in original response) fix several custom report performance issues. To make sure this is part of the problem you can review your Reporting Services log on the SQL Server. The default path to this file is %programfiles%\Microsoft SQL Server<SQL Server Instance>\Reporting Services\LogFiles. Depending on how your SQL server reporting services were installed this could be quite a bit different. You can look at the reporting service to find this easily.

image.png922×535 85.3 KB

9

The logs show the error message types from the MS knowledge base article.

Here’s the averages of DataRetrieval, Processing and Rendering times for a report that hasn’t been touched (RDL or RDD).

So yeah … something is awry

10

The knowledge base article typically addresses the TimeProcessing so while I would recommend applying the update, if you can, I would not bet on that fixing the issue. I guess I would restart SQL Server Reporting Services and see if you gain any performance uptick with this report. You could also take the SQL query text from the RDL and run it against your Epicor Reporting Services database to see if that is fast.This could be tough if you have sub-reports and/or multiple datasets. SQL Profiler would be the next step if you cannot narrow it down with the above attempts. You could run a duration trace just to see where the long execution time is coming from. It could be that your Epicor Reporting Database has thousands of rows coming across and populating it. You could directly query a couple of the tables, in your Epicor Reporting Database, once the data has generated to see if perhaps something is causing a higher than expected number of rows to come over.

11

Thanks for all the suggestions.

FWIW, the query of Executionlog3 (performed in SQL Server Mngmnt Studio) returns the row count as well.

12

Yep, that shows the aggregate rows, which could be a pretty good indicator of a problem. I would check the tables if that number seems too high to see where the problem could reside. You can look at the additional info column as well in the ExecutionLog3 table for individual row counts.

13

We had a similar problem and what we did was:

  • Check the timeouts, document you can find in the epicweb.
  • We tune the network. Check the TTLs between servers.
  • Maintenance of the database logs.

But what seemed to work was the tuning of infrastructure. The Inventory / Reconciliation Report was a torture, took about 12 hours or more. And now no more than 20 min.

14

Had a similar problem on another report… Re-Indexed SQL Database and dropped from 30,000ms to 5,000ms.

15

All DB’s? Or just the ones related to reporting(RDB, and ReportServices)?

16

We re-indexed the Epicor Database only… There was one report it took like 3-5min to run… despite restarting servers… doing a basic full database re-index solved our issue.

17

At my current company, I inherited a system where there was no routine maintenance on the DB indexes for Epicor – the system got slower and slower on certain reports, until I realised that the indexes were needing re-index. I now have a weekly scheduled job on the SQL server that does a full index rebuild and update statistics.

1 Like