E1: DB: Rowset Size and Performance in SQL Server 2008 Database (Doc ID 1144405.1)

Last updated on APRIL 13, 2020

Applies to:

Symptoms

EnterpriseOne is configured as follows:

• EnterpriseOne 8.12 with Tools Release 8.98.3.0
• E1 Platform/DB: Windows x64 and SQL Server 2008
• E1 Applications:
• P09210A - Trial Balance / Ledger Comparison
• P09214 - Trial Balance by Object

• JDE.INI for the Enterprise Server shows:
  

[JDENET]
enterpriseServerTimeout=60000

[DB SYSTEM SETTINGS]
SSQueryTimeout=1
SSQueryTimeoutRetries=17
Rowset Size=1

• JDE.INI for the Client shows:
  

[JDENET]
enterpriseServerTimeout=60000

[DB SYSTEM SETTINGS]
SSQueryTimeout=1
SSQueryTimeoutRetries=17
Rowset Size=0

• Application Logic: Both application P09210A and P09214 are fetching data through Business Function B0900090 - TrialBalanceCalculateBalances (Trial Balance Calculate Balance) which is called at Grid Record is Fetched event.

• Number of rows which meets selection criteria: 126,447
  
  
• Duration of time until a single row is returned as a result in Grid:
• PD812 (where B0900090 is running in Local): 20 seconds
• JPD812 (where B0900090 is running in Server): 130 seconds
• JPD812 in iSeries: 20 seconds
• In issuing the same SQL query through a query tool (outside of E1) takes 5 seconds

• Consideration: Since a Table Trigger against F0902 is in place, when turning on logging it takes over 8 minutes for both server and client. So strictly for testing purposes, the Table Trigger F0902_Currency was removed.

While running P09210A/P09214 it ends in error after 2 minutes time as follows:

Changes

In the logic server JDE.INI, the following change was made:

[DB SYSTEM SETTINGS]
Rowset Size=1

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.