Report Generation using Data Mart slower than Legacy Connector

(0) Share

Report

Posted on by Community Member

We recently upgraded from GP 2010 to GP 2015 and updated Management Reporter to CU13. We also implemented the Data Mart for the first time. I understood it was supposed to improve performance, especially for our consolidated reports that draw from four different company databases.

Now reports take MUCH longer. A report that used to take five minutes now takes 20. Why is this and how can we speed it up? I've already rebuilt the Data Mart but that didn't help.

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (0)

babubaskaran@outloo... 12 on at

Like (0)

Report

Hi Sherri,

How is the SQL server looks when you run the report on MR. Is the memory getting full.

Was this reply helpful? Yes No
Tom Cruse 1,477 on at

Like (0)

Report

I would also suggest to look at your server running Management Reporter, I'm guessing it might be a dedicated Web Server but it might have a heavy load running too.

Check your server performance and see if something is bottlenecking it.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I feel your pain. We have a Customer that had similar issues. BTW - The suggestions from this group are spot on. We don't have any information on your environment - physical, virtual, etc., but here is where we found the problem at one of our sites:

1. Run a Management Reporter Report and check the Resource Monitor on the SQL Server and the Management Reporter application Server.

2. Check the memory on SQL and see if it is spiking and staying at 100%

3. If you are running SQL Server on premise, is it RAID 5 or 10? Does it have the CPU horsepower required for the application? Sometimes customers will upgrade to the minimum requirements, but then put more stress on the system than what was purchased.

Would like to hear what you discover.

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

Hi Sherri,

Most of the previous replies are pointing to a performance related troubleshooting and suggest to look at the underlying infrastructure.... Honestly, if your MR 2012 server was running fine with the Legacy connector, there should be no reason that it would not run fine with the DataMart, except for this one rule : How many GP company databases do you have configured in your MR DM ?

If you have more than a couple, you may easily run into a "temporal space-continuum" problem (aka server CPU overload due to the refresh cycle).. Read on my blog post here about this DM refresh issue

Let me know if this relates to your issue.
Hope this helps.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

We do have Management Reporter running on a separate server, but it doesn't seem to be getting hit very hard. I don't know exactly which performance statistics you would consider most important, but while running an MR report, the % Processor Time maxed at 35.9% and for memory, the % Committed Bytes in Use maxed at 38.5%. If there are other measures that are more meaningful, I can certainly look at those.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Traditionally, it seems that whatever amount of memory SQL is given it takes it all. While running an MR report, I see the % Committed Bytes in Use maxing out at 66.9%. Is there another performance measure that would be a better indicator?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

The Management Reporter services run on one server and SQL is on another. While running a MR report, the Available MBytes of memory on SQL got very low about a minute into the process, but recovered somewhat after that. It certainly wasn't pegged. The average Available MBytes was 1,006.7. It has 8 GB to work with. As for the CPU, it is a 4 core, 2.0 Ghz. While running an MR report, the % Processor Time averaged 32.5 but got as high as 62%. We do see the every minute activity of updating the Data Mart. Are there other specific performance measures that I should be looking at?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Our GP environment has been running well prior to the implementation of the Data Mart. We had recently moved the databases to a SQL 2012 server. We have six companies configured to update the Data Mart. While that's more than two, I've heard of people running 20 or more, so that doesn't seem like it should be an issue. We are definitely seeing the effects of the every 1 minute updates on the SQL server and I'm seriously considering using your approach to reset that to a more reasonable interval, but that alone doesn't seem like it should cause a report that ran in 5 minutes before to take 15 now.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (2)

Report

After opening a trouble ticket with Microsoft, I learned that CU13 has known performance issues. The recommendation was to upgrade to CU14, then rebuild the Data Mart. Fortunately, this worked for us.

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

Thank you Sherri for posting an update about this... From what I'd heard so far, CU13 seemed to be a problematic release, so it doesn't come very as a surprise... Good to know that CU14 fixed the performance issue... Will give it a try too when I've some time after the Year-End Tax update in January.

Have a great holiday.

Was this reply helpful? Yes No