PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 06/19/2013 2:47 PM by  BarbLR
Query with "OR" criteria timing out with Lawson 9.0.1
 16 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Laura
Accounting Systems Analyst
National Heritage Academies
Basic Member
(45 points)
Basic Member
Posts:19


Send Message:

--
05/09/2013 8:14 AM
    We are upgrading to 9.0.1 this weekend and during testing have run across several queries we use on a regular basis where we have "OR" criteria.  There's a fairly simple one that is just pulling 3 fields from APINVOICE and the criteria is: {invoice1} OR {invoice2}.  When we query on 9.0.0, that takes less than 2 seconds.  Running the same query against 9.0.1, even after rebuilding it, it takes over a minute.

    Has anyone else encountered this?  Is it just the version of add-ins we are using (9.0.3.1601) or is it something else?  Thanks for any help!
    stephanie
    Veteran Member
    (922 points)
    Veteran Member
    Posts:330


    Send Message:

    --
    05/09/2013 9:06 AM
    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
    BarbLR
    Systems Architect
    Munson Healthcare
    Veteran Member
    (888 points)
    Veteran Member
    Posts:306


    Send Message:

    --
    05/09/2013 9:17 AM
    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!
    Laura
    Accounting Systems Analyst
    National Heritage Academies
    Basic Member
    (45 points)
    Basic Member
    Posts:19


    Send Message:

    --
    05/09/2013 10:06 AM
    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?
    BarbLR
    Systems Architect
    Munson Healthcare
    Veteran Member
    (888 points)
    Veteran Member
    Posts:306


    Send Message:

    --
    05/09/2013 12:34 PM
    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.
    Laura
    Accounting Systems Analyst
    National Heritage Academies
    Basic Member
    (45 points)
    Basic Member
    Posts:19


    Send Message:

    --
    05/09/2013 1:38 PM
    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.
    BarbLR
    Systems Architect
    Munson Healthcare
    Veteran Member
    (888 points)
    Veteran Member
    Posts:306


    Send Message:

    --
    05/09/2013 1:48 PM
    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.
    BarbLR
    Systems Architect
    Munson Healthcare
    Veteran Member
    (888 points)
    Veteran Member
    Posts:306


    Send Message:

    --
    05/14/2013 2:22 PM
    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.
    John Henley
    Private
    Private
    Senior Member
    (9587 points)
    Senior Member
    Posts:3213


    Send Message:

    --
    05/14/2013 3:53 PM
    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 ??
    Thanks for using the LawsonGuru.com forums!
    John
    BarbLR
    Systems Architect
    Munson Healthcare
    Veteran Member
    (888 points)
    Veteran Member
    Posts:306


    Send Message:

    --
    05/15/2013 6:19 AM
    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.
    Laura
    Accounting Systems Analyst
    National Heritage Academies
    Basic Member
    (45 points)
    Basic Member
    Posts:19


    Send Message:

    --
    05/15/2013 2:10 PM
    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
    BarbLR
    Systems Architect
    Munson Healthcare
    Veteran Member
    (888 points)
    Veteran Member
    Posts:306


    Send Message:

    --
    05/15/2013 2:29 PM
    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!
    Laura
    Accounting Systems Analyst
    National Heritage Academies
    Basic Member
    (45 points)
    Basic Member
    Posts:19


    Send Message:

    --
    05/16/2013 7:20 AM
    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
    Laura
    Accounting Systems Analyst
    National Heritage Academies
    Basic Member
    (45 points)
    Basic Member
    Posts:19


    Send Message:

    --
    05/17/2013 9:59 AM
    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?
    BarbLR
    Systems Architect
    Munson Healthcare
    Veteran Member
    (888 points)
    Veteran Member
    Posts:306


    Send Message:

    --
    05/20/2013 9:51 AM
    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.)
    Laura
    Accounting Systems Analyst
    National Heritage Academies
    Basic Member
    (45 points)
    Basic Member
    Posts:19


    Send Message:

    --
    05/20/2013 10:11 AM
    Barb, that's great! I'm glad we both opened incidents so they didn't just write us off.
    BarbLR
    Systems Architect
    Munson Healthcare
    Veteran Member
    (888 points)
    Veteran Member
    Posts:306


    Send Message:

    --
    06/19/2013 2:47 PM
    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.
    You are not authorized to post a reply.