Slow Searches on F4211 & F42119

I'm at a client who is having issues with slow searches on the "Next Status" field in the Sales Order Detail screen. Simply searching for a next status of 600 takes minutes. I've attached the JDEdebug.log for the "find" clik involved. The Client is running XE Update7 SP22_Q1 AS400 V5R1. The client is coexistent with World. Their were no indexes for OW on the f4211 or f42119, so using OMW I generated indexes for these files thinking it had to help...it did not. There is approx 10k records in the f4211 and 1.8m records in the f42119 files.

After creating indexes is there something I need to do to activate them? should I restart OW services maybe?

Thanks all.

Bill

Your log only shows the SQL request...not the total time until data is returned. Is this the only log you have?

My bad, i posted here before the data returned....new log incoming.

The problem lies with the following select statement :

SELECT SDKCOO, (blah blah), SDTORG FROM CRPDTA/F4211 WHERE ( SDNXTR = '600' ) UNION SELECT SDKCOO, (blah blah), SDTORG FROM CRPDTA/F42119 WHERE ( SDNXTR = '600' ) ORDER BY 2 ASC , 3 ASC , 1 ASC , 4 ASC

It takes 1' 56" to perform this search on your implementation - it takes 6 seconds for me to perform this on my implementation (when I changed NXTR to "540" as a test).

Why ? Its probably because its a horrible query.

I have 48,000 records in my F4211 and 350,000 records in my F42119. You have 10,000 records in the F4211 and 1.8m in the F42119.

Its probably, therefore, the F42119 thats causing your issue.

Do you have an index on the F4211 and the F42119 built on "NXTR" ? If not, then thats why it takes so long for you. Try creating a custom index and reindex and test in CRP. You can do this using the Table Design Aid and by using the toolset to re-create the index. You might also want to regenerate your indices, maybe that might help ?

As standard, there are indices on Next Status :

F4211 has index 002 based on Order Type, Next Status, LineType
F42119 has index 003 based on Order Type, Next Status, LineType

To see if these are being picked up, try using Order Type and/or Line Type in your search.

I suggest you run the Performance Monitor. It will tell you what indices you may need to make. I've got a paper out on www.jdetips.com on how to use this function, either using green screen or operations navigator.

Thanks all. This fix worked great, my response on a next status search is now under 3seconds until data is visible.