Why are my reports so slow in Epicor? Adventures with report performance
I frequently modify Crystal Report files in Epicor for both standard and custom reports, so I know there’s a few moving parts to say the least. First, I have my data definition in Epicor to generate the structure of my XML data source. Second, there’s my Crystal Reports file on the server to render that data as something useful. Third, I’ve got the Report Style marrying the report file to the generated XML; and finally, fourth, I’ve got my system agent actually doing the processing. Therefore, when I click print-preview on a report the data have to be queried in Epicor via the report data definition and written to the server as a file, the system agent has to check for when that file has cleanly closed, and then it has to actually render the print-preview. I’ve noticed that when I modify a report things normally fall into two relatively broad categories: reports that print preview quickly, and reports that do not… time to explore.
I’ll start by defining my broad categories. With some general testing across many different reports (standard and custom) I found that my “quick” reports all print previewed in around 15-18 seconds. The “not so quick” reports were always 45 seconds or greater, there was no middle ground of any report that previewed in the 20-45 second range. Apparently that is the great Crystal Reports time-void in Epicor (and I forgot my TARDIS!). This was not entirely intuitive to me, the 25-30 second gap of zero previews did not make sense. Time for testing!
With a single report that I had in my “quick” group I started adding data by expanding the filter ranges to see if I could force something into that gap. Preview times were more or less linear up to the 15-ish second mark as I kept adding data and then BAM! I fell into the void. 30 seconds later, at the 45 second mark, my preview dutifully popped up as if nothing had happened. I kept pushing in data and again and again it would immediately preview at 45 seconds, but no sooner or later. Eventually I had pushed so much in that the 45 second mark preview did not happen and I was pretty sure I killed the report. A minute went by… time was standing still… I looked on the server in the EpicorData directory, the XML was there, and it closed cleanly. At one minute and 15 seconds I finally got my print-preview screen.
So what’s going on? I can either get reports quickly within 15 seconds, at 45 seconds, or if I really go nuts, 75 seconds. Given the choice I’d always opt for under 15, but 45 seems like an extreme jump. What’s so bad about 22 seconds? The answer, as it turns out, is the System Monitor polling interval.
In Epicor 9.05 under System Management > Utilities you can open up the System Monitor and from there go to Actions and choose Configuration. Here we get a pop-up with three entry values.
- Normal = 30000
- Priority = 3000
- Duration = 15000
This is the polling interval settings for the System Monitor to check the EpicorData directory for the cleanly closed XML for report previewing. When I clicked “print preview” the System Monitor went into Priority polling mode at 3000 (which is milliseconds, so 3 seconds). This Priority polling lasted for the set Duration of 15000 milliseconds (15 seconds). Therefore, in the first 15 seconds, the System Monitor actually checked the EpicorData directory five times. If the XML was ready it previewed the report, if not it went back into Normal polling mode at 30000 milliseconds (30 seconds). So then, 30 seconds later from the original 15 second priority duration, it re-checked the directory and if the XML was ready previewed the report. This totally explained the behavior I saw. Anything that finished in under 15 seconds came up immediately because the System Monitor checked every 3 seconds, anything in the 16-44 second range previewed at 45 seconds, and then every 30 seconds after that.
For grins, I was able to mess with these settings as long as the Duration was less than or equal to the Normal value. I finally got my 22 second print preview and successfully entered the time-void. These changes were saved into my local client mfgsys file, but obviously if I needed to push this out I could by customizing the client for all users. That said, I think understanding how it works lets me design reports with this in mind. The moral of this amazing adventure is that, as usual, everything is a trade-off. By default when I add a new table to a report-data-definition Epicor brings in all of the fields. If I’m not cognizant of this it rapidly (and needlessly) grows the size of my XML file. From a Crystal Reports development perspective I might not have cared, it’s just extra fields in my field explorer and they’re there if I need them. Now with this polling interval it’s apparent that I should only be grabbing fields I need, since every other field add size to the XML and potentially adds latency to the time it takes to write to the server. From a System Administrator perspective it’s also making me think about disk io and other structural considerations related to the LUN used for my EpicorData directory. The bottom-line is that if you change a previous fast report and it suddenly falls into the time-void and doesn’t resurface until 45 seconds later, time the XML generation time on the server and see if that can be cut down in any way.