SQL 2014 custom index - can I do this in 9.2.1.1

Michael L.

Well Known Member
Hello List. We have a couple 3rd party UBE’s that take between 5 and 30 hours to complete. We recently had an audit on our SQL 2014 database. Several indexes have been recommended of which they say should be done sooner than later. Below is one of those.

CREATE INDEX [ix_F44S712F_IDMCU_IDOBJ_IDSUB_IDSBLT_IDSBL_IDLT_includes] ON [JDE_PRODUCTION].[PRODDTA].[F44S712F] ([IDMCU], [IDOBJ], [IDSUB], [IDSBLT], [IDSBL], [IDLT]) INCLUDE ([IDSCENARIO], [IDSCNVERNM], [IDRCRECTYP], [IDSEQ], [IDACTVSEQ], [IDEV01], [IDEL], [IDDL01], [IDOPTION], [IDSELECTID], [IDRMID], [IDRLSELID], [IDVZSELID], [IDCOMBOPT], [IDCORMID], [IDCOACTFLG], [IDRBSELID], [IDHCCLASS1], [IDHCCLASS2], [IDHCCLASS3], [IDSTDSEL], [IDSELINA], [IDBHSEL], [IDEV07], [IDNTY09], [IDTGCOST], [IDTGRCOST], [IDCRMRCLS], [IDRCCST], [IDRMISINCL], [IDSLTYP], [IDADDREM], [IDRMK], [IDHBST1], [IDHBST2], [IDHBST3], [IDHBST4], [IDHBST5], [IDAN01], [IDAN02], [IDAN03], [IDAN04], [IDAN05], [IDCRTU], [IDCRTJ], [IDCRTT], [IDWRKSTNID], [IDHBOPID], [IDUPMB], [IDUPMJ], [IDUPMT], [IDJOBN], [IDPID]) WITH (FILLFACTOR=100, ONLINE=ON);

Typically we create custom indexes in the JD Edwards toolset and then generate one single index.

Can we create the entire index above in the JD Edwards toolset and by that I mean including everything to the right of the “include” and if not would we be better off creating the index as is directly into the SQL database or use the JD Edwards toolset, dropping everything to the right of the include and just add the key?

Thanks in advance for any feedback.

Our 9.2.1.1 config

Application Release: 9.2
Tools Release: 9.2.1.1
Application (Enterprise) Servers - 2012 standard x64 (64-bit)
Web Servers - 2012 standard R2 Datacenter x64 (64-bit)
Database servers - 2012 standard R2 Datacenter x64 (64-bit) / SQL server 2014
WebLogic Server Administration Console 12c
Dell EqualLogic
 

Larry_Jones

Legendary Poster
OK Here's my opinion / 2 cents.

If the UBE's were developed without defining the index in the JDE Toolset then adding a index now via the Toolset has no benefit other than preventing the index from showing up on a exception report at Upgrade time.

If it was me I'd add the index in the database only and be done with it.

Cheers,
 

Tom_Davidson

VIP Member
Here is my 2 cents. ;) I'd create it in JDE. If the index is actually being used, then I try to create it because I don't know if <b>I</b> will be around come upgrade time. I include them when possible, but my 2 cents is no more valid than Larrys, you can make a case either way.
 

RussellCodlin

Reputable Poster
My $0.02, which is in AUD rather than USD so not worth as much (although we don't have pennies so have to round up to AU$0.05). JDE indexes are designed to be utilised by JDE (eg sort orders, filters etc) and can be used as such. Indexes that are required for database tuning reasons should be created directly in the database and this is because performance tuning is very database specific so what is required to get SQL Server to play nice is not the same as Oracle. On top of that, performance tuning in SQL Server 2014 is not the same as SQL Server 2016. So when it comes to upgrades etc, you'd expect that the database version is likely to change at the same time and thus tuning activities need to be completed again.
 
Top