Create Index In/Out the OneWorld

[DavidHuang]

Hi all,

Now we have two way to create a index for a OneWorld table:

1) Using the OneWorld Table Design Tools.
2) Creating the index in database directly, say running a SQL script in SQL
Server.

Do you know what's the difference between them?

What's will be affected in OneWorld (application/technique) if using the 2nd
method?

Which way is better if we want to improve the performance of the QBE query
via create a index on the non-key field?

By the way, I am not a DBA.

Thanks & Regards,
David Huang

 

[DBohner-(db)]

Just some quick note (warning/suggestion):

* if you create an index, external to JDE - that is not referenced within JDE - you will not get the 'search' criteria options when creating fetch ER (where you select the name of the index and the index items are splatted '*')
* Alex (Everest International) has a business function tool that allows you to call independant functions, external to JDE. I've read that you can use his tool to run the JDE BSFN and create one index at a time. He'll, probably, pipe up and comment on it's use (I'm not selling, just making you aware of another great tool out there).

(db)

 

[DavidHuang]

Hi Daniel,

Thanks very much for your quick response.

But can we get the QBE query performance improved if we create the index external to JDE ?

For example:

Our testing showed that if we queried a sale order in the P4210 form using the keys: Company/Order Type/Order Number, it only took 2 seconds to get the detail record on the screen. But if we use Customer PO(not a key/index) to inquiry, it will costed us 60 seconds to get the same order detail information. (In our system, each Company/Order Type/Order Number own a unique Customer PO in the detail record.) And there are 1.5 Million records in our F4211.

We won't create fetch ER using the externally created index, just want to get the query performance improved.

I am not a DBA, so far have no chance to test although the testing is easy.

Please comment.

Thanks
David Huang

 

[DBohner-(db)]

Whether you create the index internal to jde or externally - won't sacrifice your performance. The create index command should be the same.

However, if you ever want to do a fetch from within JDE - you do want to create the index internal to the toolset.

I'm sure there will be a day when you want to fetch by the index key - you can save yourself a great frustration by creating the index, internally, the first time.

Just my ramblings

(db)

 

[bradbeckett1]

Also, if you ever regenerate indexes or regenerate the table from OneWorld, if the index in question was built via the toolset it will be regenerated automatically.

If it was built via SQL script, it won't be automatically regenerated.

Not a big deal until a new DBA or developer comes in and doesn't know there are indexes that aren't in OW.

Best practice is to build the index in the toolset, at least IMHO.

 

[Larry_Jones]

David,

to answer your specific question: "But can we get the QBE query performance improved if we create the index external to JDE ?"

the answer is yes, assuming that:
a) You've done your analysis of the slow running query and determined that its performance would benefit if a specific ordered index was created;
b) You perform the SQL Server equivalent of an Oracle "Analyze Table" command to generate statistics about the new index so the database engine can make the determination to use it.

The other points given are valid but not really all that important for your needs. Its the database engine that decides how it will retrieve the data. Even ER Select/Fetch statements that specify a given index are just really controlling what goes in the "Order By" clause of the SQL Statements passed to the DB Engine.

Use a script to generate and analyze your custom indices. Save the script(s) in a documented location. That way when you need to recreate the custom indices all you need to do is rerun the script.

The above is only In My Humble Opinon of course - worth what you paid for it

 

[DavidHuang]

Hi all,

Thanks for your Humble Opinon

. I get lots of helpful information from your feedback. And that's why JDELIST is valuable.

Warmest Regards,

 

[antoine_mpo]

Hello David,

Another thing you can consider is that if you create a new index within jde, you need to generate it. But jde does not generate only your new index, it generate all indexes. Which means : first it drop all the indexes of the file, and then recreate all indexes specified within jde.
So for some files (for instance wo file F4801) that can be huge, and which contains many indexes, it could take a while to regenerate all indexes (and in the meantime, if you do it on the fly, it can lead to poor performance for all your database).
The advantage of creating the index out of jde, is that you can create it without droping existing indexes, and test that it will indeed increase your search performance.
If it's well the case, may be it could be "cleaner" to create it within jde.

Rgds,