GP performance 3

[barbola]

Over the years, our biggest complaint with Dynamics has been slow performance. We have beefed up our server a few times to accomodate the large database size (our largest is 14 GB and the rest are smaller than 1.5 GB).

A fairly new user (manager) keeps whining that it takes 90 seconds to post a journal entry. I try to tell him to save all his entries to a batch, and post the batch. Even if it's one entry. And only post it if he needs to see the posted result. He doesn't "get it".

Anyway, why would it take so long to post a single journal entry? It seems to take this long even if there is only one other user in the system, after hours.

 

[RobertC22]

Check that your SQL Server Agent has a job called
"Remove Posted <DB> PJOURNALs".

If you do not have that SQL AGENT JOB, you can create it by referencing the PJjob.sql found in the .\sql\utilities folder of your Great Plains "Server" installation.
You will need to replace %DBNAM% with the actual database name.

Alternatively, refer to
KB 864618 "Performance is slow when you post or print an Edit List", or
KB 862293 "Slow performance posting batches in Great Plains General Ledger"


------
Robert

 

[barbola]

Thanks for the suggestion. I looked at the sysjobs table in the msdb database and ran the sql script to update it for the companies that were missing.

I then went and posted a journal entry, by itself, without a batch, and it still took as long as ever (ie. 90 seconds).

Do I need to stop and restart the agent or anything like that? The KB articles will also help but aren't applicable in this particular case, because this isn't a report or a batch, just a single JE posting from the transaction screen.

thanks
b

 

[barbola]

Oh wow...I also found KB article 898982. It says to run "Select * from PJOURNAL" for each company. Our main company had over 900,000 records in this table!!!

Does anyone know any consequences of running this statement? IE will anything bad happen???

thanks
barb

 

[jazgeek]

a select statement won't do anything to your database, it's a reading of the table



-----------
and they wonder why they call it Great Pains!

jaz

 

[barbola]

OOPS sorry I meant the consequences of deleting all those records.

I found another KB article that said to backup the db and then run the delete so that is what I did, and now it takes only a few seconds to post.

You'd think our vendor would know of this fix! grrr!

 

[RobertC22]

After creating the SQL agent job, it will, by default, every 30 mins.
However, prior to version 8.0, this SQL agent job is not created correctly. The target server is not specified.

See KB 858744 "...Cannot start job 'Remove Posted TWO PJOURNALS' because it does not have any job server(s).." to how to finish configuring this sql agent job

As long as no one is posting, completely clearing the PJOURNAL table manually will not affect anything.


For sites with extreme processing loads, I adjust the schedule to less than 30 mins. For heavy processing loads , leave as is. For Light, change to every 4 hours or even daily.

At most, the number of records in the PJOURNAL should not exceed the number of records in the GL10001 table.
(There is an exception when using allocation accounts).

------
Robert

 

[barbola]

Yes I see ours is going every 30 minutes, and that's okay for us.

Another KB article said to find the jobs under Enterprise Manager > Management > SQL Server Agent > Jobs ... which isn't where I looked before, and it said to right-click on the job, then click properties. Then it said to click Target Local Server, and even if it is already clicked, to click Apply and that will get it going.

Thanks again.
b.

 

[barbola]

I found out from our vendor that the reason these jobs were likely missing is because they didn't get moved to the new SQL server when we upgraded to a new one.

 

[RobertC22]

Barb,

Yes, this is a classic issue when transferring to a new SQL. the SQL Agent jobs are not re-created.

This can also affect Business Alerts.
If you are using Business Alerts, it is simpler to get re-create the agent job rather than transferring.

To do this, open your existing Business Alert from
Cards --> System --> Business Alerts.
Select a Business Alert ID
Unmark the "Enable Business ALert"
[SAve]
reload the same business alert
Mark the "Enable Business ALert"
[save]
proceed to next Business Alert ID.




------
Robert

 

[barbola]

Thanks. We don't use Business Alerts...I know I tried to once but my screen was showin stuff way different from the vendor's screen and we couldn't get it to work.

I might look at it again once we upgrade to GP9.0 (end of this month).

thanks.