Macola ES Speed Issues

[MCS814]

thread639-1495390


Peter Shirley wrote:
Agree with you on the aging reports. If customers and resellers can produce versions that run quickly and accurately, Exact should be able to as well..The nature of the tables (gbkmut, banktransctions etc.) accentuate performance problems in SQL and there are a number of things you can configure on your SQL server that will offset the problems caused by long running queries.They are too lengthy to go into here, but I'd be happy to forward you some suggestions. Peter ShirleyMacola Consultant, PA and surrounding states.

--I'm interested in what we can configure in SQL.

 

[dgillz]

Hopefully Peter will chime in. But what version and edition of SQL are you running? How big is your database? The version/edition of SQL can make a huge difference in performance.

I will say that, regardless of version/edition issues, Peter's aging reports run in seconds whereas Exact's take minutes or hours.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[MCS814]

SQL Server 2005 Standard Edition
Database size: 98.5 GB

Our aging reports definitley take a bit to run out of ES. But one of our biggest issue comes from Lock ups between Cash Flow, G/L , AP , Production etc. It seems if any one is running excel add in for financials or doing AP postings and such we start experiencing slow downs and lock ups

 

[dgillz]

That is one big database - the biggest I have heard of yet for ES. But then a lot of my larger Progression customers have not made the move to ES.

Peter's aging reports should definitely help you.

Please define "lock up". Are you actually getting an error message or are you deciding to kill a process because it is taking too long?

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[MCS814]

We get error messages,our applications freeze, or just take a lot longer to go through.

Mariestel Salas

 

[alpofreely]

Experiencing extremely slow performance with commissions due report compared to progression. Anyone have a suggestion or alternative to running the native report?

ES v9.5.3

 

[NEmacGuy]

In terms of the agings, the reports don't take two long. It is the process that creates a copy of the gmkmut and banktransaction file that is a killer.

 

[dgillz]

Peter Shirley has developed reports for this that take seconds instead of hours. Check out his website

http://erpessentials.com/index.php/crystal-reports/reports-and-articles/49-es-aging-reports.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[crystalreporting]

MSC814 - sorry for the slow response; it's been a while since I stopped in here.

I have a document I would be happy to share with you if you drop me an email via the website in my signature.

Peter Shirley
Macola Consultant, PA and surrounding states.

http://www.erpessentials.com

 

[allogagan1]

A/R - Customers - Maintain - Statistics (Slow)

Macola ES 9.5.400 - Recently Upgraded

After upgrade we noticed a significant degradation in performance on loading the Statistics tab from A/R - Customers - Maintain. Has anyone else encountered this and have any solutions?
Our performance has gone from a few seconds to 1 hour + to run.

I have done some research and found:
Attempted to run a trace with SQL Profiler and found a statement that would be running and max out the CPU.

Attempted to "breakdown" the statement to individual pieces:
- Sales of goods - data was returned
- Cost of goods - remained executing (ran for 1 hour 10 minutes)
- Invoice count - data was returned
- Paid count - data was returned

After comparing SQL statements between 9.5.200 and 9.5.400 there are LTRIM and RTRIM functions in the 9.5.400 version that appear to be extremely slowing the system down (These did not exist in the 9.5.200 version). In a test run in a new SQL Query I took the 9.5.400 SQL statement and removed the LTRIM and RTRIM function lines and data was returned quickly. There was 3 instances of the function line in the Cost of Goods section ---
OR LTRIM(RTRIM(d.debcode)) IN (Select LTRIM(RTRIM(debcode)) from cicmpy where InvoiceDebtor = (select debnr from cicmpy where debcode = '<ACCOUNT NUMBER HERE>' ))

Response from Exact:
The LTrim(RTRIM()) statements are in there for the differences between AlphaNumeric and Numeric alignments in the tables so they can't be changed or removed. We're not sure if we can adjust the query further to improve speed. If you would like us to attempt that we would need a copy of your data because currently there are not other sites reporting slow speed for the statistics tab.

I’m curious about other experiences and solutions.

Thank you.

 

[NEmacGuy]

If the fields were index fields, I beleive performing a function on the fields would loose the value of an index. Thus your performance would be degraded. Great post on comparing what is going on.

 

[dgillz]

Tell Exact to look on their own forum for someone else reporting the problem with the customer statictics tab:

http://forums.exactamerica.com/exact/viewtopic.php?f=3&t=621

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html