Slow Running UBE's on ERP 8.0

[JayDee1]

Slow Running UBE\'s on ERP 8.0

Hello All,

We are experiencing a non-critical UBE slow-down that has puzzled me for the last several months. After upgrading to ERP 8.0 from XE, UBE's that were submitted to any of APP servers started running slightly slower than usual. One custom report in particular, however, started running a LOT slower than before. This is a product reorder report that was written in house. This report would take up to 30 minutes as opposed to only 5 minutes previously. I found out that once the report was submitted to the application server, it would just sit there for several minutes before it would start executing. I later found out that the application server was waiting to communicate with the database server. Once the app server had started to talk to the DB server, this report would actually start executing on the App server and would finish very quickly. The only solution that I have found that will fix this is to include a static IP route for the database server in each of the application servers. This fixes the latency issue. My question is why is this happening and is there a better resolution than using static IP's. The steps I have taken to fix this issue along with our configuration and setup are listed below. Thank you for your help.

Actions Performed:

Disjoined and rejoined all servers from our Domain
Checked all DNS and WINS settings
Tested various settings in JDE.INI for queues and JDENET
Reinstalled networking components on the App servers
Created new versions of the reorder report with different queue assignment
Reinstalled all JDE services on one of the two App servers
Rebuilt one of the two App servers


Hardware:

DB server: HP DL 560 Quad 3.0 GHZ Pentium Xeon, 6GB RAM
Enterprise Server: HP DL 380, Dual 3.2 GHZ Pentium Xeon
2 Application servers: HP DL 380’s, Dual 3.0 GHZ Pentium Xeon’s, 4GB of Ram for each server


Software:

DB: SQL 2000 Enterprise Edition SP3a running on Windows 2000 Advance Server
Enterprise/App servers: Windows 2000 Server SP 4.0 and Windows 2003 Server SP1(on one of the two app servers)


JDE:
ERP 8.0 SP 22

 

[DBohner-(db)]

RE: Slow Running UBE\'s on ERP 8.0

JD1,



It sounds like not all the Indexes have been recreated on the server.
Usually, the slowness at the beginning of a UBE is caused by the running of
a SQL statement that has missing indexes. Check to see if the slow reports
have similar data selection.



Another issue could be security. One of my clients had 'extreme' business
unit security (lots of include/exclude of ranges of MCU). When the report
was run with security - it often took over an hour to kick out a couple
pages. When we would zap security - it took seconds to run. We believed
the issue was due to some form of indexing - but I rolled off before it was
resolved. One very odd issue with this customer's security, though - when
more than 13 ranges were secure - the statement dawgged. When 12 or fewer
ranges were secured - it was done in seconds.



Just a couple considerations.



(db)



_____

From: [email protected] [mailto:[email protected]] On
Behalf Of JayDee1
Sent: Thursday, February 02, 2006 3:15 PM
To: [email protected]
Subject: Slow Running UBE's on ERP 8.0

 

[JayDee1]

Re: RE: Slow Running UBE\'s on ERP 8.0

Daniel,

We rebuild SQL indexes for all of our Production tables on a weekly basis and again the same reports with the same data selection run faster with the static IP than without. This is why I'm convinced that this issue is somehow network related. Furthermore, there is no table level security on any of our tables since we handle that through solution explorer, application and version security. You bring up a very good point, however, since we are getting ready to implement row-level security and its impact on UBE performance must be taken into consideration.

 

[DBohner-(db)]

Re: RE: Slow Running UBE\'s on ERP 8.0

JD1,

How are you regenerating your indexes weekly?

You might have misunderstood my meaning, regarding checking to see if an index is missed. You need to check to see if one is missing (one that had never been created or defined to E1.

Capture your SQL statement in your JDEDebug.Log - then run it through some sort of query analyzer (there are a lot of them out there - I like AQUA).

When the analyzer runs - it compares against existing indexes, shows how the data will be brought back (using which indexes) and scores the process. The benefit to checking the indexes this way - some Query Analyzers will inform you that it is advantageous to create a new index on some table...

On the Network Note: Some DNS servers release their cache/memory way to fast. You should ask your network team to place a DNS on each node of the network and to HARD-CODE the name/stack of the Mission Critical machines. Those IPs should never (or rarely) change.

(db)

 

[JayDee1]

Re: RE: Slow Running UBE\'s on ERP 8.0

Daniel,

Thanks for the information. I will download AQUA and will run the logs through it to see what emerges. Will keep you posted. Thanks again......