Rebuild SQL Indexes in E1 Package Tables

With the change to table-based, XML-format metadata (formerly specs) in EnterpriseOne, first-use dynamic e-generation became a performance issue, impacting end users after a package deployment and affecting their perception of system performance. This article describes an easy method of lessening this impact.

A very short primer on auto package discovery and dynamic egeneration as it applies to the package tables:

Since 8.12/8.96, when a package is built, tables are created in the build environment's Central Objects database containing metadata for the objects in the package. The tables, named CentralObjectsTablePackageName (Ex: F98762BPDOCF001) are used by both the Enterprise servers and Web servers, guaranteeing consistency across the system.

The Enterprise servers access the package tables for objects as they are needed, bringing them across the wire to a local cache in the filesystem. The Java servers access the same package tables, bringing the objects across the wire, serializing them into the Serialized Objects tables and also placing them in cache.

Since the Enterprise and Java server now access the object metadata from the database server instead of local TAM files in the case of the Enterprise server and Serialized Objects (Java servers), there is now an additional burden associated with retrieving object metadata, an increase in the network load between the Enterprise/Java servers and the database server and an increase in the load on the database server.

A read of records in the package tables can be optimized by ensuring that the indexes are properly defragmented and the statistics are up-to-date. In SQL Server the index rebuild process does both.

Since we now have the XML metadata in tables, and we know that the dynamic e-generation is going to be accessing these tables heavily in the period immediately following a package deployment, it behooves us to rebuild the indexes on the package tables.

Execute the SQL2005/SQL2008 transact sql code below after the build completes but prior to deploying the package.


--SQL Script Begin
-- Enter appropriate variables in the SET statements for:
-- @DatabaseName
-- @SchemaOwner
-- @PackageName

declare @DatabaseName varchar (50)
declare @SchemaOwner varchar (50)
declare @PackageName varchar(50)
declare @SQL varchar(8000)

SET @DatabaseName = 'JDE_PD812'
SET @SchemaOwner = 'PD812'
SET @PackageName = 'BPDOCF001'

SET @SQL =
'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98762' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98720' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98711' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98713' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98712' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98710' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98743' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98751' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98750' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98740' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98741' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98306' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98761' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98760' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98745' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98753' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98752' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98770' + @PackageName + ' REBUILD ' + CHAR(13)

EXEC (@SQL)
--SQL Script End

Rebuilding the indexes of the package tables helps lessen the burden on the database server during the dynamic (or manual) e-generation process and improves object first-use performance.