Multipe same select statements executed instead of one

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

It might be (is) a good idea to switch from OLEDB to ODBC. There are known issues. Solution ID: 200949255. It is now recommended if not required to switch all datasources to ODBC from OLEDB.

Hello,

Wow somebody who has the same problem as we have. We have exactly the same problem in our environment. We see this happen when submitting UBE on our enterprise server. And when it is accessing large tables. (f0911,F4111) After having logged this case with Peoplesoft and have several consultants looking at this issue we could not found a reason why this is happening. The latest reaction of Oracle was " Please log a case with microsoft because there is the problem". Yesterday I was very surprised when looking in the Service Pack 8.95 B1. There was a SAR with exactly our issue. SAR number 7705341.
So we are not the only one having this problem.

I already tested oledb and this gives the same problem.

If you have any other questions please e-mail me on: [email protected].

Regards,

8.10 8.94_N1 sql server 2000.