Wednesday, September 2, 2009

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.
Subscribe to Jeff Stevenson's Technology Blog - Get an email when new posts appear

6 comments:

Jeremy said...

Jeff,

This is very cool stuff. I am going to give it a try immediately! Thanks for your continual advice. You are always very helpful and I am always in anticipation of your next tip!

Thanks,
Jeremy

Jeremy said...

Jeff,

I tried this out today along with an older post you had titled, "Determine Index Fragmentation in a SQL Server Database". After I rebuilt the package tables I ran the code from the older post to analyze the fragmentation of the central objects database. I expected to see a list of indexes for the package tables with a 0% fragmentation. The results only showed the non-package specific tables and they were around 89% fragmentation.

Should I be seeing indexes for the package tables and/or do I need to rebuild the non-package tables?

Jeff Stevenson said...

Jeremy,

Yup, you're right. Let me look into it.

Jeff Stevenson said...

Fixed it. The problem was the portion of the code that returned the Object Librarian name of the table by joining the table name in sys.objects with the table name in Object Librarian. Turns out there is no table in OL named F98711PD900FA or somesuch. Who knew?

Grab the new code in http://jeffstevenson.karamazovgroup.com/2008/09/determine-index-fragmentation-in-all.html

Jeremy said...

Very interesting... The new code works great! Is there any value gained from rebuilding the tables in the central objects that aren't package specific?

Jeff Stevenson said...

There's always a benefit to maintaining indexes. I generally schedule index rebuilds weekly for all databases...including Central Objects.