Results 1 to 9 of 9

Thread: Limiting the execution time of E1 Queries on the database - Especially if signed off

  1. #1
    Senior Member johndanter's Avatar
    Join Date
    Sep 2003
    Location
    Cork, Ireland
    Posts
    1,304

    Limiting the execution time of E1 Queries on the database - Especially if signed off

    Hi List,

    I've had a query from one of my DBAs on how I can prevent E1 from executing awfully long queries. Is there a way of setting a maximum execution time.
    We are noticing some queries that last 10 hrs!!! And the user who interactively fired them off is probably out of the office, tucked up in bed.

    And is there a way to detect that a user is no longer active, therefore kill all requests sent. I think the users are exiting IE rather than singing off.

    Is this kind of issue and config done on the database level or done in E1's INI file settings

    Here are a few examples of long running queries. I know they are poorly written as in the top one for example no FY is used for a start, but still, once a user clicks find it's too late.

    Seconds 19,414.19
    SQL
    SELECT DISTINCT GLHDGJ,GLR1,GLPOST,GLCRR,GLDGJ,GLLT,GLUSER,GLEXA,G LCRCD,GLALT9,GLRE,GLDCT,GLKCO,GLDOC,GLICUT,GLHCRR, GLICU,GLPN FROM PRODDTA.F0911 WHERE (((((GLICUT = :1 AND GLUSER = :2 )) AND (GLCO = :3 OR GLCO = :4 OR GLCO = :5 OR GLCO = :6 OR GLCO = :7 OR GLCO = :8 OR GLCO = :9 )) AND (GLMCU = :10 OR GLMCU BETWEEN :11 AND :12 OR GLMCU = :13 OR GLMCU = :14 OR GLMCU = :15 OR GLMCU BETWEEN :16 AND :17 OR GLMCU BETWEEN :18 AND :19 OR GLMCU = :20 OR GLMCU = :21 OR GLMCU = :22 OR GLMCU BETWEEN :23 AND :24 OR GLMCU = :25 ))) ORDER BY GLDCT ASC , GLDOC ASC , GLKCO ASC , GLDGJ DESC , GLLT ASC , GLRE DESC


    Seconds 10,661.66
    SQL
    SELECT ILICU,ILLNID,ILMCU,ILUKID,ILTREX,ILTRDJ,ILDCT,ILKC O,ILDOC,ILDGL,ILMMCU,ILKCOO,ILAID FROM PRODDTA.F4111 WHERE (((ILDCT = :1 ) AND (ILMCU = :2 OR ILMCU = :3 OR ILMCU = :4 OR ILMCU = :5 OR ILMCU = :6 OR ILMCU BETWEEN :7 AND :8 OR ILMCU = :9 OR ILMCU = :10 OR ILMCU = :11 OR ILMCU = :12 OR ILMCU = :13 ))) ORDER BY ILDOC ASC , ILICU ASC , ILDCT ASC , ILKCO ASC , ILLNID ASC , ILDGL ASC
    Last edited by johndanter; 03-09-2018 at 02:36 AM.
    John Danter
    You don't go out looking for a job dressed like that do you, on a weekday?

    E900 TR 9.1.5.3 Unix

  2. #2
    Member Tom_Davidson's Avatar
    Join Date
    Nov 2000
    Location
    Wisconsin, USA
    Posts
    714
    It depends on your platform:
    On the IBM i you can set the QQRYTIMLMT to a max number of seconds, this is system wide, or you can create a QAQQINI with the QUERY_TIME_LIMIT value set and make sure your JDE tasks use this file.

    On Oracle you could set up a resource profile.

    Not sure on MS SQL

    Tom
    Cleindori Consulting
    8.12/8.98.4.4, 9.1/9.1.5.3, 9.2/9.2.0.5/6
    IBM i, WebLogic on Windows, DBCS, Global installations.

  3. #3
    Quote Originally Posted by johndanter View Post
    Hi List,

    I've had a query from one of my DBAs on how I can prevent E1 from executing awfully long queries. Is there a way of setting a maximum execution time.
    We are noticing some queries that last 10 hrs!!! And the user who interactively fired them off is probably out of the office, tucked up in bed.

    And is there a way to detect that a user is no longer active, therefore kill all requests sent. I think the users are exiting IE rather than singing off.

    Is this kind of issue and config done on the database level or done in E1's INI file settings

    Here are a few examples of long running queries. I know they are poorly written as in the top one for example no FY is used for a start, but still, once a user clicks find it's too late.

    Seconds 19,414.19
    SQL
    SELECT DISTINCT GLHDGJ,GLR1,GLPOST,GLCRR,GLDGJ,GLLT,GLUSER,GLEXA,G LCRCD,GLALT9,GLRE,GLDCT,GLKCO,GLDOC,GLICUT,GLHCRR, GLICU,GLPN FROM PRODDTA.F0911 WHERE (((((GLICUT = :1 AND GLUSER = :2 )) AND (GLCO = :3 OR GLCO = :4 OR GLCO = :5 OR GLCO = :6 OR GLCO = :7 OR GLCO = :8 OR GLCO = :9 )) AND (GLMCU = :10 OR GLMCU BETWEEN :11 AND :12 OR GLMCU = :13 OR GLMCU = :14 OR GLMCU = :15 OR GLMCU BETWEEN :16 AND :17 OR GLMCU BETWEEN :18 AND :19 OR GLMCU = :20 OR GLMCU = :21 OR GLMCU = :22 OR GLMCU BETWEEN :23 AND :24 OR GLMCU = :25 ))) ORDER BY GLDCT ASC , GLDOC ASC , GLKCO ASC , GLDGJ DESC , GLLT ASC , GLRE DESC


    Seconds 10,661.66
    SQL
    SELECT ILICU,ILLNID,ILMCU,ILUKID,ILTREX,ILTRDJ,ILDCT,ILKC O,ILDOC,ILDGL,ILMMCU,ILKCOO,ILAID FROM PRODDTA.F4111 WHERE (((ILDCT = :1 ) AND (ILMCU = :2 OR ILMCU = :3 OR ILMCU = :4 OR ILMCU = :5 OR ILMCU = :6 OR ILMCU BETWEEN :7 AND :8 OR ILMCU = :9 OR ILMCU = :10 OR ILMCU = :11 OR ILMCU = :12 OR ILMCU = :13 ))) ORDER BY ILDOC ASC , ILICU ASC , ILDCT ASC , ILKCO ASC , ILLNID ASC , ILDGL ASC
    Have you run the queries through the database manually? It doesn't seem like these queries should take hours.

    I ran the following through a not-so-strong SQL server and it took 6 minutes:


    SELECT ILICU,ILLNID,ILMCU,ILUKID,ILTREX,ILTRDJ,ILDCT,ILKC O,ILDOC,ILDGL,ILMMCU,ILKCOO,ILAID FROM TESTDTA.F4111
    WHERE (((ILDCT = 'RI') AND (ILMCU = 5000 OR ILMCU = 300 OR ILMCU = 200 OR ILMCU = 22 OR ILMCU = 21 OR ILMCU BETWEEN 6000 AND 7000 OR ILMCU = 5 OR ILMCU = 6 OR ILMCU = 7 OR ILMCU = 8 OR ILMCU = 9 )))
    ORDER BY ILDOC ASC , ILICU ASC , ILDCT ASC , ILKCO ASC , ILLNID ASC , ILDGL ASC

  4. #4
    Senior Member johndanter's Avatar
    Join Date
    Sep 2003
    Location
    Cork, Ireland
    Posts
    1,304
    Thanks for the replies :0

    Hi Tom,
    I am on an Oracle database 11G and a Unix box. You are suggesting it's the middleware? Which makes really as E1 passes it down to Unix, Unix passes it down to the DB

    Hi brother_of_karamazov,
    Your database volumes will be different to mine? But thanks for execution. Food for thought though.
    John Danter
    You don't go out looking for a job dressed like that do you, on a weekday?

    E900 TR 9.1.5.3 Unix

  5. #5
    Quote Originally Posted by johndanter View Post
    Thanks for the replies :0

    Hi Tom,
    I am on an Oracle database 11G and a Unix box. You are suggesting it's the middleware? Which makes really as E1 passes it down to Unix, Unix passes it down to the DB

    Hi brother_of_karamazov,
    Your database volumes will be different to mine? But thanks for execution. Food for thought though.
    68,730,826 records in the F4111.

    The queries above are well matched to indexes in E1 and the where clauses, while a bit unconventional, shouldn't cause problems.

    It would probably be best to find out why those queries ran that long. I cannot imagine even a SELECT * running that long unless you have an enormous amount of records in the F0911/F4111. Can you determine the actual execution time or if the queries were being blocked by another statement?

  6. #6
    Quote Originally Posted by Tom_Davidson View Post
    It depends on your platform:
    On the IBM i you can set the QQRYTIMLMT to a max number of seconds, this is system wide, or you can create a QAQQINI with the QUERY_TIME_LIMIT value set and make sure your JDE tasks use this file.

    On Oracle you could set up a resource profile.

    Not sure on MS SQL

    Tom
    Query Governor on SQL.

  7. #7
    Senior Member johndanter's Avatar
    Join Date
    Sep 2003
    Location
    Cork, Ireland
    Posts
    1,304
    Hi folks

    JAS server connects to the database via JDBC, but what governs the 'cancel' or 'killing' of the database requests once a user signs off?

    We are seeing the SQL a grid generates on Find still running even after the user killed IE or even if they signed off correctly.

    Thanks

    John
    John Danter
    You don't go out looking for a job dressed like that do you, on a weekday?

    E900 TR 9.1.5.3 Unix

  8. #8
    Quote Originally Posted by johndanter View Post
    Hi folks

    JAS server connects to the database via JDBC, but what governs the 'cancel' or 'killing' of the database requests once a user signs off?

    We are seeing the SQL a grid generates on Find still running even after the user killed IE or even if they signed off correctly.

    Thanks

    John
    I don't think anything does honestly. If a user forces a signout or closes the browser after hitting Find, the query will still execute at the database level. I think there was a feature request for this a while back but I am pretty sure nothing was done.

    The best one can do is limit Open Finds using Application Query Security, train users on proper Query By Example, secure Scroll to End and train users not to exit IE using the 'X'.

  9. #9
    Senior Member johndanter's Avatar
    Join Date
    Sep 2003
    Location
    Cork, Ireland
    Posts
    1,304
    Quote Originally Posted by brother_of_karamazov View Post
    I don't think anything does honestly. If a user forces a signout or closes the browser after hitting Find, the query will still execute at the database level. I think there was a feature request for this a while back but I am pretty sure nothing was done.

    The best one can do is limit Open Finds using Application Query Security, train users on proper Query By Example, secure Scroll to End and train users not to exit IE using the 'X'.
    Great stuff. This is what our CNc have suggested.
    I knew E1 does it in certain screens but I had no idea where it was setup or if it was configurable or came out the box. I couldn't see anything in the code, so guessed it maybe a setting somewhere, but knowing what to search for sometimes is half the battle


    We will also be looking at DB setting SQLNET.EXPIRE_TIME This does what I hoped existed, which is the periodic polling back up to see if the requester is still waiting for an answer

    Thanks all
    John Danter
    You don't go out looking for a job dressed like that do you, on a weekday?

    E900 TR 9.1.5.3 Unix

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.