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
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: