Query with "OR" criteria timing out with Lawson 9.0.1

We have run into similar situations here, however, when we upgraded, we also were in the middle of a life cycle hardware replacement, and basically attributed the longer processing time to the changed table structures and our new servers. Thankfully, our users didn't go too crazy about this one (they chose other things to be crazy about). Good luck

We recently upgraded to LSF 9.0.1.10 and MSP 9.0.1.8 and at both older and newer 9.0.1.3 add-in versions we have this issue. We used to be able to query the PAYDEDUCTN table for a few deduction codes but now can only run it for one deduction at a time. As you can imagine the customers are not happy. I have an Incident open but so far no suggestions (other than to run it one at a time or to remove all criteria entirely - ha ha). I don't have details, but the developers changed something to improve performance and might have actually made it worse, something about a "bad class file"? Glad to hear we aren't the only ones!

I am also glad to hear it's not just us, although I was hoping for an "oh yeah, that's a really easy fix" response Barb, did you file an incident directly with Lawson/Infor?

Yes, I have a high priority Incident open with InforXtreme support (6558602) since April 3rd, and also emailed a bit with the Add-In Guru Don Peterson who actually spoke with the developers in Manila who made the changes. On April 10 the theory was that there was a bad class file, so I was waiting for the developers to respond. On May 1st I asked for status and learned that the developers had looked at it and determined there wasn't a bug in the class file but did acknowledge that they made some fixes to improve query performance. They are now waiting for me to run some tests in the browser on a "quiet system". I have not been able to schedule that with my customers yet. If you have the time/resources it would be great if you could open your own Incident and refer to mine and do the testing at your shop.

I opened an incident, I will reference yours and hopefully will be able to get somewhere with this. Thanks for the info! I'll keep you posted on what I find out.

Great! I also just found out I can schedule quiet time on our system on Monday - so I'll try to set the ios logging properties file and run the query in the browser then. Hopefully that will get us farther along.

Laura - what LSF and MSP version is this happening on? LSF 9.0.1.what? MSP9.0.1.what? Today I ran a very simple query from our paydeductn table for employee=1 and got a couple thousand records back in 14 seconds. Then I changed the query to be (employee=1 OR employee=2) and it timed out. Is that happening to you - all queries with OR criteria against large tables timing out?
FYI news on our "quiet time" testing with the DME query in a browser. The browser timed out but the query kept running for nearly two hours until it ended. The ios.log indicated that it returned the right number of records in result. So we now know that the query runs, just slow as molasses in January.

Remember that addins with the "OR" criteria is likely going to end up doing a table scan.
Did you try using the index with EMPLOYEE set to 1;2 ??

I was just using the employee=1 or employee=2 to test the issue. The customers are running payroll deduction reports for several deduction codes for particular payroll check dates. I don't see that there is a way to use the indexes for that.

Barb, WOW!! Yes, that is exactly what is happening to us - for example, if I pull a query from APINVOICE of one invoice number, it comes back instantaneously. Or even if I have it be invoice~1 then it returns thousands of results within seconds. But as soon as I add an OR (invoice=1 OR invoice=2) it takes 20-30 times as long. Yes, it's around a minute, but this is a super simple query just to demonstrate the results we are seeing. Do that on an ACTRANS or GLTRANS table expecting to get thousands upon thousands of results and it takes 10 minutes!

So I filed that incident and so far I've gotten a "yep, we were able to replicate your results and I've filed JT-463289" and then they closed my incident. Not sure what to do from here.

And on a side note, I have no idea what LSF and MSP mean - I'm not that technical

Laura - very interesting! Can you please reply with the following?
1. What is your Incident number (so I can have my Incident responder talk to yours to see if it is the same issue)?
2. It sounds like your queries are just slow, not completely failing with a time-out - is that true?
3. What is the priority on your Incident - 1 or 2 or 3? If 3, then that JT-463289 might not be worked on and fixed for a long time. If priority 2 or higher it has a chance of being fixed within a few months.
4. And most importantly, it would really help me to know exactly what versions you are on (LSF is the Lawson environment and MSP is the Lawson applications). Can you please contact your IT support staff and get the full LSF and MSP version numbers? I am on LSF 9.0.1.10 and MSP 9.0.1.8.
Thanks for your help, we really need it!

1. My incident number is 6650050
2. I set my time-out to 500 and have adjusted it up to 800 periodically depending on the complexity of the query we are running. I don't think we've had any that haven't run in that 800 seconds, but when it was anything less than 500 they were timing out.
3. The severity on my incident was 3, medium.
4. We are also on environment 9.0.1.10 and apps 9.0.1.8

John, I tried using the index for one of the queries I am running against the ACTRANS table - pulling transactions created from payroll in the current period.  Unfortunately, no data was returned because the "ORs" I am looking for are accounting units that begin with 1009, 1054, or 1078 (and another 9) for the current period.  I think the index only works for exact values, correct?

My criteria looks like this:
Fiscal year=2013 AND Period=11 AND System=PR AND (AU~1009********* OR AU~1054********* OR AU~1078********* OR etc...)

I set my timeout to 9999 and let it run without any indexes, and it took 23 minutes to return the results.

So then I tried indexing the fiscal year and period, and it took 24 minutes. 

Do you have any suggestions that would make this particular query run faster?

Just a note to say that I was informed on Friday that the developer has isolated what changed and what is happening and that the priority 2 JT-463289 should be out in a few weeks. (Laura, this is the same JT number you were given. Thanks for opening your Incident. I opened mine on April 3rd, but it seemed that not much happened until I could refer to other clients having the same symptoms.)

Barb, that's great! I'm glad we both opened incidents so they didn't just write us off.

Laura - here is an update on this. We applied the fix JT-463289 which was an environment patch collection (more than just this one fix) and it resolved the Add-in query issues against large table with "or" criteria. Unfortunately it also delivered something that caused Security Violation errors when our managers attempted to add a Personnel Action through Manager Self-Service. Since we were in the middle of testing for a project that required MSS-Personnel Actions we were forced to back off this patch collection. When things settle down we will apply the latest collection and see if the security problems are resolved in that collection.