deniko
Member
Hello everyone,
We have a perfomance problem while fetching records from F41021 (from P40ITM2) and F4102 (from P41026). Visually the problem looks like that: one presses Find and then waits for about 1.5 minutes for the results. Looking into jdedebug.log one can see that 2 same select statements are executed with "timeout" error, after that the same select executed with (nolock) modifier, this third select results in success.
Does anyone have any idea what could we do about it?
PS E1 E810, tools realease 3
Example of jdedebug.log for search from P40ITM2:
Oct 11 13:00:15.084 - 2132/2108 [JDBOLEDB] SQL: request = 0x20DE2660:1849: SELECT T0.IMITM, T0.IMLITM, T0.IMAITM, T0.IMDSC1, T0.IMSRTX, T0.IMDRAW, T0.IMRVNO, T0.IMUOM1, T0.IMUOM2, T0.IMSTKT, T0.IMIFLA, T0.IMTFLA, T0.IMTMPL, T0.IMSEG1, T0.IMSEG2, T0.IMSEG3, T0.IMSEG4, T0.IMSEG5, T0.IMSEG6, T0.IMSEG7, T0.IMSEG8, T0.IMSEG9, T0.IMSEG0, T0.IMMIC, T0.IMDLTL, T0.IMDPPO, T0.IMDUAL, T1.LIITM, T1.LIMCU, T1.LILOCN, T1.LILOTN, T1.LIPBIN, T1.LIGLPT, T1.LILOTS, T1.LILRCJ, T1.LIPQOH, T1.LIPBCK, T1.LIPREQ, T1.LIQWBO, T1.LIOT1P, T1.LIOT2P, T1.LIOT1A, T1.LIHCOM, T1.LIPCOM, T1.LIFCOM, T1.LIFUN1, T1.LIQOWO, T1.LIQTTR, T1.LIQTIN, T1.LIQONL, T1.LIQTRI, T1.LIQTRO, T1.LINCDJ, T1.LIQTY1, T1.LIQTY2, T1.LIURAB, T1.LIURRF, T1.LIURAT, T1.LIURCD, T1.LIJOBN, T1.LIPID, T1.LIUPMJ, T1.LIUSER, T1.LITDAY, T1.LIURDT, T1.LIQTO1, T1.LIQTO2, T1.LIHCMS, T1.LIPJCM, T1.LIPJDM, T1.LISCMS, T1.LISIBW, T1.LISOBW, T1.LISQOH, T1.LISQWO, T1.LISREQ, T1.LISWHC, T1.LISWSC, T1.LICHDF, T1.LIWPDF, T1.LICFGSID FROM PS_CRP.CRPDTA.F4101 T0,PS_CRP.CRPDTA.F41021 T1 WHERE ( T1.LIMCU = ' 0180' AND T0.IMMIC <> '3' ) AND ( T0.IMITM=T1.LIITM ) ORDER BY T1.LIITM ASC,T1.LIMCU ASC,T1.LILOCN ASC,T1.LILOTN ASC.
Oct 11 13:00:23.807 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:00:23.807 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:00:23.807 - 2132/2108 [JDBOLEDB] Info: Connection is lost and do 1 retry.
Oct 11 13:00:32.339 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:00:32.339 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:00:32.339 - 2132/2108 [JDBOLEDB] Info: Connection is lost and do 2 retry.
Oct 11 13:00:40.861 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:00:40.861 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:00:49.414 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:00:49.414 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:00:49.514 - 2132/2108 [JDBOLEDB] SQL: request = 0x20DE2660:1849: SELECT T0.IMITM, T0.IMLITM, T0.IMAITM, T0.IMDSC1, T0.IMSRTX, T0.IMDRAW, T0.IMRVNO, T0.IMUOM1, T0.IMUOM2, T0.IMSTKT, T0.IMIFLA, T0.IMTFLA, T0.IMTMPL, T0.IMSEG1, T0.IMSEG2, T0.IMSEG3, T0.IMSEG4, T0.IMSEG5, T0.IMSEG6, T0.IMSEG7, T0.IMSEG8, T0.IMSEG9, T0.IMSEG0, T0.IMMIC, T0.IMDLTL, T0.IMDPPO, T0.IMDUAL, T1.LIITM, T1.LIMCU, T1.LILOCN, T1.LILOTN, T1.LIPBIN, T1.LIGLPT, T1.LILOTS, T1.LILRCJ, T1.LIPQOH, T1.LIPBCK, T1.LIPREQ, T1.LIQWBO, T1.LIOT1P, T1.LIOT2P, T1.LIOT1A, T1.LIHCOM, T1.LIPCOM, T1.LIFCOM, T1.LIFUN1, T1.LIQOWO, T1.LIQTTR, T1.LIQTIN, T1.LIQONL, T1.LIQTRI, T1.LIQTRO, T1.LINCDJ, T1.LIQTY1, T1.LIQTY2, T1.LIURAB, T1.LIURRF, T1.LIURAT, T1.LIURCD, T1.LIJOBN, T1.LIPID, T1.LIUPMJ, T1.LIUSER, T1.LITDAY, T1.LIURDT, T1.LIQTO1, T1.LIQTO2, T1.LIHCMS, T1.LIPJCM, T1.LIPJDM, T1.LISCMS, T1.LISIBW, T1.LISOBW, T1.LISQOH, T1.LISQWO, T1.LISREQ, T1.LISWHC, T1.LISWSC, T1.LICHDF, T1.LIWPDF, T1.LICFGSID FROM PS_CRP.CRPDTA.F4101 T0,PS_CRP.CRPDTA.F41021 T1 WHERE ( T1.LIMCU = ' 0180' AND T0.IMMIC <> '3' ) AND ( T0.IMITM=T1.LIITM ) ORDER BY T1.LIITM ASC,T1.LIMCU ASC,T1.LILOCN ASC,T1.LILOTN ASC.
Oct 11 13:00:58.216 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:00:58.216 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:00:58.216 - 2132/2108 [JDBOLEDB] Info: Connection is lost and do 1 retry.
Oct 11 13:01:06.949 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:01:06.949 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:01:06.949 - 2132/2108 [JDBOLEDB] Info: Connection is lost and do 2 retry.
Oct 11 13:01:15.571 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:01:15.571 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:01:24.204 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:01:24.204 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:01:24.304 - 2132/2108 [JDBOLEDB] Data selection processed with high concurrency option.
Oct 11 13:01:24.304 - 2132/2108 [JDBOLEDB] SQL: request = 0x20DE2660:1849: SELECT T0.IMITM, T0.IMLITM, T0.IMAITM, T0.IMDSC1, T0.IMSRTX, T0.IMDRAW, T0.IMRVNO, T0.IMUOM1, T0.IMUOM2, T0.IMSTKT, T0.IMIFLA, T0.IMTFLA, T0.IMTMPL, T0.IMSEG1, T0.IMSEG2, T0.IMSEG3, T0.IMSEG4, T0.IMSEG5, T0.IMSEG6, T0.IMSEG7, T0.IMSEG8, T0.IMSEG9, T0.IMSEG0, T0.IMMIC, T0.IMDLTL, T0.IMDPPO, T0.IMDUAL, T1.LIITM, T1.LIMCU, T1.LILOCN, T1.LILOTN, T1.LIPBIN, T1.LIGLPT, T1.LILOTS, T1.LILRCJ, T1.LIPQOH, T1.LIPBCK, T1.LIPREQ, T1.LIQWBO, T1.LIOT1P, T1.LIOT2P, T1.LIOT1A, T1.LIHCOM, T1.LIPCOM, T1.LIFCOM, T1.LIFUN1, T1.LIQOWO, T1.LIQTTR, T1.LIQTIN, T1.LIQONL, T1.LIQTRI, T1.LIQTRO, T1.LINCDJ, T1.LIQTY1, T1.LIQTY2, T1.LIURAB, T1.LIURRF, T1.LIURAT, T1.LIURCD, T1.LIJOBN, T1.LIPID, T1.LIUPMJ, T1.LIUSER, T1.LITDAY, T1.LIURDT, T1.LIQTO1, T1.LIQTO2, T1.LIHCMS, T1.LIPJCM, T1.LIPJDM, T1.LISCMS, T1.LISIBW, T1.LISOBW, T1.LISQOH, T1.LISQWO, T1.LISREQ, T1.LISWHC, T1.LISWSC, T1.LICHDF, T1.LIWPDF, T1.LICFGSID FROM PS_CRP.CRPDTA.F4101 T0 (NOLOCK) ,PS_CRP.CRPDTA.F41021 T1 (NOLOCK) WHERE ( T1.LIMCU = ' 0180' AND T0.IMMIC <> '3' ) AND ( T0.IMITM=T1.LIITM ) ORDER BY T1.LIITM ASC,T1.LIMCU ASC,T1.LILOCN ASC,T1.LILOTN ASC.
Oct 11 13:01:35.209 - 2132/2108 [JDBOLEDB] ODDBPerformRequest::main() 0x20DE2660:1849 pSession = 0x20D9BAC8; cursType = 4; syntaxFlag = 32.
Oct 11 13:01:35.209 - 2132/3080 Exiting JDB_SelectKeyed with Success
We have a perfomance problem while fetching records from F41021 (from P40ITM2) and F4102 (from P41026). Visually the problem looks like that: one presses Find and then waits for about 1.5 minutes for the results. Looking into jdedebug.log one can see that 2 same select statements are executed with "timeout" error, after that the same select executed with (nolock) modifier, this third select results in success.
Does anyone have any idea what could we do about it?
PS E1 E810, tools realease 3
Example of jdedebug.log for search from P40ITM2:
Oct 11 13:00:15.084 - 2132/2108 [JDBOLEDB] SQL: request = 0x20DE2660:1849: SELECT T0.IMITM, T0.IMLITM, T0.IMAITM, T0.IMDSC1, T0.IMSRTX, T0.IMDRAW, T0.IMRVNO, T0.IMUOM1, T0.IMUOM2, T0.IMSTKT, T0.IMIFLA, T0.IMTFLA, T0.IMTMPL, T0.IMSEG1, T0.IMSEG2, T0.IMSEG3, T0.IMSEG4, T0.IMSEG5, T0.IMSEG6, T0.IMSEG7, T0.IMSEG8, T0.IMSEG9, T0.IMSEG0, T0.IMMIC, T0.IMDLTL, T0.IMDPPO, T0.IMDUAL, T1.LIITM, T1.LIMCU, T1.LILOCN, T1.LILOTN, T1.LIPBIN, T1.LIGLPT, T1.LILOTS, T1.LILRCJ, T1.LIPQOH, T1.LIPBCK, T1.LIPREQ, T1.LIQWBO, T1.LIOT1P, T1.LIOT2P, T1.LIOT1A, T1.LIHCOM, T1.LIPCOM, T1.LIFCOM, T1.LIFUN1, T1.LIQOWO, T1.LIQTTR, T1.LIQTIN, T1.LIQONL, T1.LIQTRI, T1.LIQTRO, T1.LINCDJ, T1.LIQTY1, T1.LIQTY2, T1.LIURAB, T1.LIURRF, T1.LIURAT, T1.LIURCD, T1.LIJOBN, T1.LIPID, T1.LIUPMJ, T1.LIUSER, T1.LITDAY, T1.LIURDT, T1.LIQTO1, T1.LIQTO2, T1.LIHCMS, T1.LIPJCM, T1.LIPJDM, T1.LISCMS, T1.LISIBW, T1.LISOBW, T1.LISQOH, T1.LISQWO, T1.LISREQ, T1.LISWHC, T1.LISWSC, T1.LICHDF, T1.LIWPDF, T1.LICFGSID FROM PS_CRP.CRPDTA.F4101 T0,PS_CRP.CRPDTA.F41021 T1 WHERE ( T1.LIMCU = ' 0180' AND T0.IMMIC <> '3' ) AND ( T0.IMITM=T1.LIITM ) ORDER BY T1.LIITM ASC,T1.LIMCU ASC,T1.LILOCN ASC,T1.LILOTN ASC.
Oct 11 13:00:23.807 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:00:23.807 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:00:23.807 - 2132/2108 [JDBOLEDB] Info: Connection is lost and do 1 retry.
Oct 11 13:00:32.339 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:00:32.339 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:00:32.339 - 2132/2108 [JDBOLEDB] Info: Connection is lost and do 2 retry.
Oct 11 13:00:40.861 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:00:40.861 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:00:49.414 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:00:49.414 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:00:49.514 - 2132/2108 [JDBOLEDB] SQL: request = 0x20DE2660:1849: SELECT T0.IMITM, T0.IMLITM, T0.IMAITM, T0.IMDSC1, T0.IMSRTX, T0.IMDRAW, T0.IMRVNO, T0.IMUOM1, T0.IMUOM2, T0.IMSTKT, T0.IMIFLA, T0.IMTFLA, T0.IMTMPL, T0.IMSEG1, T0.IMSEG2, T0.IMSEG3, T0.IMSEG4, T0.IMSEG5, T0.IMSEG6, T0.IMSEG7, T0.IMSEG8, T0.IMSEG9, T0.IMSEG0, T0.IMMIC, T0.IMDLTL, T0.IMDPPO, T0.IMDUAL, T1.LIITM, T1.LIMCU, T1.LILOCN, T1.LILOTN, T1.LIPBIN, T1.LIGLPT, T1.LILOTS, T1.LILRCJ, T1.LIPQOH, T1.LIPBCK, T1.LIPREQ, T1.LIQWBO, T1.LIOT1P, T1.LIOT2P, T1.LIOT1A, T1.LIHCOM, T1.LIPCOM, T1.LIFCOM, T1.LIFUN1, T1.LIQOWO, T1.LIQTTR, T1.LIQTIN, T1.LIQONL, T1.LIQTRI, T1.LIQTRO, T1.LINCDJ, T1.LIQTY1, T1.LIQTY2, T1.LIURAB, T1.LIURRF, T1.LIURAT, T1.LIURCD, T1.LIJOBN, T1.LIPID, T1.LIUPMJ, T1.LIUSER, T1.LITDAY, T1.LIURDT, T1.LIQTO1, T1.LIQTO2, T1.LIHCMS, T1.LIPJCM, T1.LIPJDM, T1.LISCMS, T1.LISIBW, T1.LISOBW, T1.LISQOH, T1.LISQWO, T1.LISREQ, T1.LISWHC, T1.LISWSC, T1.LICHDF, T1.LIWPDF, T1.LICFGSID FROM PS_CRP.CRPDTA.F4101 T0,PS_CRP.CRPDTA.F41021 T1 WHERE ( T1.LIMCU = ' 0180' AND T0.IMMIC <> '3' ) AND ( T0.IMITM=T1.LIITM ) ORDER BY T1.LIITM ASC,T1.LIMCU ASC,T1.LILOCN ASC,T1.LILOTN ASC.
Oct 11 13:00:58.216 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:00:58.216 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:00:58.216 - 2132/2108 [JDBOLEDB] Info: Connection is lost and do 1 retry.
Oct 11 13:01:06.949 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:01:06.949 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:01:06.949 - 2132/2108 [JDBOLEDB] Info: Connection is lost and do 2 retry.
Oct 11 13:01:15.571 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:01:15.571 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:01:24.204 - 2132/2108 [JDBOLEDB] error: Failure in object ODSelect / method ODSelect::execute().
Oct 11 13:01:24.204 - 2132/2108 [JDBOLEDB] error: HRESULT = 80040e31, SQLState = HYT00, NativeError = 0, msg = Timeout expired.
Oct 11 13:01:24.304 - 2132/2108 [JDBOLEDB] Data selection processed with high concurrency option.
Oct 11 13:01:24.304 - 2132/2108 [JDBOLEDB] SQL: request = 0x20DE2660:1849: SELECT T0.IMITM, T0.IMLITM, T0.IMAITM, T0.IMDSC1, T0.IMSRTX, T0.IMDRAW, T0.IMRVNO, T0.IMUOM1, T0.IMUOM2, T0.IMSTKT, T0.IMIFLA, T0.IMTFLA, T0.IMTMPL, T0.IMSEG1, T0.IMSEG2, T0.IMSEG3, T0.IMSEG4, T0.IMSEG5, T0.IMSEG6, T0.IMSEG7, T0.IMSEG8, T0.IMSEG9, T0.IMSEG0, T0.IMMIC, T0.IMDLTL, T0.IMDPPO, T0.IMDUAL, T1.LIITM, T1.LIMCU, T1.LILOCN, T1.LILOTN, T1.LIPBIN, T1.LIGLPT, T1.LILOTS, T1.LILRCJ, T1.LIPQOH, T1.LIPBCK, T1.LIPREQ, T1.LIQWBO, T1.LIOT1P, T1.LIOT2P, T1.LIOT1A, T1.LIHCOM, T1.LIPCOM, T1.LIFCOM, T1.LIFUN1, T1.LIQOWO, T1.LIQTTR, T1.LIQTIN, T1.LIQONL, T1.LIQTRI, T1.LIQTRO, T1.LINCDJ, T1.LIQTY1, T1.LIQTY2, T1.LIURAB, T1.LIURRF, T1.LIURAT, T1.LIURCD, T1.LIJOBN, T1.LIPID, T1.LIUPMJ, T1.LIUSER, T1.LITDAY, T1.LIURDT, T1.LIQTO1, T1.LIQTO2, T1.LIHCMS, T1.LIPJCM, T1.LIPJDM, T1.LISCMS, T1.LISIBW, T1.LISOBW, T1.LISQOH, T1.LISQWO, T1.LISREQ, T1.LISWHC, T1.LISWSC, T1.LICHDF, T1.LIWPDF, T1.LICFGSID FROM PS_CRP.CRPDTA.F4101 T0 (NOLOCK) ,PS_CRP.CRPDTA.F41021 T1 (NOLOCK) WHERE ( T1.LIMCU = ' 0180' AND T0.IMMIC <> '3' ) AND ( T0.IMITM=T1.LIITM ) ORDER BY T1.LIITM ASC,T1.LIMCU ASC,T1.LILOCN ASC,T1.LILOTN ASC.
Oct 11 13:01:35.209 - 2132/2108 [JDBOLEDB] ODDBPerformRequest::main() 0x20DE2660:1849 pSession = 0x20D9BAC8; cursType = 4; syntaxFlag = 32.
Oct 11 13:01:35.209 - 2132/3080 Exiting JDB_SelectKeyed with Success
