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

johndanter

johndanter

Legendary Poster
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,GLCRCD,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,ILKCO,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:
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
 
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,GLCRCD,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,ILKCO,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,ILKCO,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
 
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.
 
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?
 
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.
 
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
 
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'.
 
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
 
Back
Top