Select statement Time Out Error while fetching data from tables having 100+ million records.

[Former Member]

All

We are getting Time out error in a function module which has to get data from a table having 121 million records.

Though the final output has just 6.7 million records but since one table has 121 million records its timing out.

We used Cursor / Views etc to optimize this but failed to avoid this error.

The select statements are as under

ITAB1 - 6.7 million records

BUT000 - 121 million records

+++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT CLIENT PARTNER CHDAT CRDAT FROM BUT000

     APPENDING TABLE i_ztab_ds_fm_test FOR ALL ENTRIES IN ITab1

     WHERE PARTNER = ITab1-ACCOUNT

++++++++++++++++++++++++++++++++++++++++++++++++++++++

Kindly suggest how to overcome this time out error.

PS - I am unable to find the Forum for posting this message since this has changed a lot.

cheers

Vinod

[former_member189725 · Accepted solution]

Please let us know the SAP release as well as the runtime error that you encounter .

Can you check this SAP note

Note 1091254 - SAP BP : Select on BUT000 takes excessive time

Regards

Ratnajit

[Former Member · Accepted solution]

Thanks Ratnajit but still my problem persists.

This SELECT statement TIMEs Out ......................

--------------------------------------------------------------------------------------------------------------------

SELECT CLIENT PARTNER CRDAT CHDAT FROM BUT000

   APPENDING TABLE i_ztab FOR ALL ENTRIES IN t1_ZICW_ORDIDX

   WHERE PARTNER = t1_ZICW_ORDIDX-ACCOUNT

----------------------------------------------------------------------------------------------------------------------

[Former Member · Accepted solution]

Could you check EXPIRE_TIME at the database level in "sqlnet.ora", if the system is running on Oracle.

Best regards,

Orkun Gedik

[Former Member]

Hi Vinod,

instead of FOR ALL ENTRIES use INNER JOIN if possible, as the FOR ALL ENTRIES statement possibly splits the entries of itab1 in numerous selects. You can prove this with an SQL-trace (ST05).

The statement below improved performance around factor 10 to 170 depending on cold or warm db-buffers in comparison to a former FOR ALL ENTRIES statement.

  SELECT ltap~lgnum ltap~tanum ltap~tapos INTO wa

         FROM ltak

         INNER JOIN ltap ON ltak~lgnum = ltap~lgnum

                        AND ltak~tanum = ltap~tanum

         WHERE ltap~matnr = p_matnr

           AND ltap~lgnum = p_lgnum

           AND ltak~betyp = 'F'.

Best regards,

Volker Wochermaier

[Former Member]

Hi Vinod,

Do you face with the time-out at the database level or SAP application server level? Could you provide more information?

Best regards,

Orkun Gedik