peterbruce
Legendary Poster
Fellow JDEListers,
I have an inconsistancy with the R098011 - 52 Period Accounting Post that I don't understand.
We have 2 companies (00006 and 00007) that have 52 Period Accounting enabled. Our F0911 has approximately 9 million rows. We have added data selection to only select companies 00006 and 00007. The SQL statement produced (extracted from the jdedebug.log run on a fat client) is:
SELECT * FROM PRODDTA.F0911 WHERE ( ( GLALT2 = ' ' AND GLPOST = 'P' AND GLSUMM <> 'Y' AND GLCO IN ( '00006','00007' ) ) ) ORDER BY GLCO ASC,GLDGJ ASC
Originally this SQL would not invoke an index and would take a long time to table scan the F0911. We added a one column index on the GLCO which brought the time to run to a more acceptable level - when there were appropriate batches to post. However, when there was no batches for the R098011 to post it takes the same amount of time. Below is are extracts from 2 jdedebug.log's for local executions of the R098011 that had nothing to post.
Aug 30 09:46:42 ** 4036/168 SELECT * FROM PRODDTA.F0911 WHERE ( ( GLALT2 = ' ' AND GLPOST = 'P' AND GLSUMM <> 'Y' AND GLCO IN ( '00006','00007' ) ) ) ORDER BY GLCO ASC,GLDGJ ASC
First:
Aug 30 09:46:42 ** 4036/168 Entering JDB_StartRetainPrevious
Aug 30 09:46:42 ** 4036/168 Entering JDB_Fetch
Aug 30 09:46:42 ** 4036/168 ORACLE DBFetch: Invoke OCI Fetch fetchNumRows = 81
Aug 30 10:10:52 ** 4036/168 Entering JDB_CloseView (VIEW = V0911M)
Second:
Aug 30 11:47:07 ** 896/3300 SELECT * FROM PRODDTA.F0911 WHERE ( ( GLALT2 = ' ' AND GLPOST = 'P' AND GLSUMM <> 'Y'
AND GLCO IN ( '00006','00007' ) ) ) ORDER BY GLCO ASC,GLDGJ ASC
Aug 30 11:47:07 ** 896/3300 Entering JDB_StartRetainPrevious
Aug 30 11:47:07 ** 896/3300 Entering JDB_Fetch
Aug 30 11:47:07 ** 896/3300 ORACLE DBFetch: Invoke OCI Fetch fetchNumRows = 81
Aug 30 12:09:48 ** 896/3300 Entering JDB_CloseView (VIEW = V0911M)
Notice the time gaps of 24 minutes and 10 seconds and 22 minutes and 41 seconds that are highlighted. The same SQL, when executed manually (using TOAD), takes only 7 seconds.
Can someone please explain why the R098011 is taking 20 to 30 minutes while the same SQL executed manually takes only 7 seconds
I have an inconsistancy with the R098011 - 52 Period Accounting Post that I don't understand.
We have 2 companies (00006 and 00007) that have 52 Period Accounting enabled. Our F0911 has approximately 9 million rows. We have added data selection to only select companies 00006 and 00007. The SQL statement produced (extracted from the jdedebug.log run on a fat client) is:
SELECT * FROM PRODDTA.F0911 WHERE ( ( GLALT2 = ' ' AND GLPOST = 'P' AND GLSUMM <> 'Y' AND GLCO IN ( '00006','00007' ) ) ) ORDER BY GLCO ASC,GLDGJ ASC
Originally this SQL would not invoke an index and would take a long time to table scan the F0911. We added a one column index on the GLCO which brought the time to run to a more acceptable level - when there were appropriate batches to post. However, when there was no batches for the R098011 to post it takes the same amount of time. Below is are extracts from 2 jdedebug.log's for local executions of the R098011 that had nothing to post.
Aug 30 09:46:42 ** 4036/168 SELECT * FROM PRODDTA.F0911 WHERE ( ( GLALT2 = ' ' AND GLPOST = 'P' AND GLSUMM <> 'Y' AND GLCO IN ( '00006','00007' ) ) ) ORDER BY GLCO ASC,GLDGJ ASC
First:
Aug 30 09:46:42 ** 4036/168 Entering JDB_StartRetainPrevious
Aug 30 09:46:42 ** 4036/168 Entering JDB_Fetch
Aug 30 09:46:42 ** 4036/168 ORACLE DBFetch: Invoke OCI Fetch fetchNumRows = 81
Aug 30 10:10:52 ** 4036/168 Entering JDB_CloseView (VIEW = V0911M)
Second:
Aug 30 11:47:07 ** 896/3300 SELECT * FROM PRODDTA.F0911 WHERE ( ( GLALT2 = ' ' AND GLPOST = 'P' AND GLSUMM <> 'Y'
AND GLCO IN ( '00006','00007' ) ) ) ORDER BY GLCO ASC,GLDGJ ASC
Aug 30 11:47:07 ** 896/3300 Entering JDB_StartRetainPrevious
Aug 30 11:47:07 ** 896/3300 Entering JDB_Fetch
Aug 30 11:47:07 ** 896/3300 ORACLE DBFetch: Invoke OCI Fetch fetchNumRows = 81
Aug 30 12:09:48 ** 896/3300 Entering JDB_CloseView (VIEW = V0911M)
Notice the time gaps of 24 minutes and 10 seconds and 22 minutes and 41 seconds that are highlighted. The same SQL, when executed manually (using TOAD), takes only 7 seconds.
Can someone please explain why the R098011 is taking 20 to 30 minutes while the same SQL executed manually takes only 7 seconds