topic Re: Time Out selecting Table in Application Development and Automation Discussions

Time Out selecting Table

Former Member — Thu, 03 Jul 2008 15:30:35 GMT

Hi friends,

Now I'm with another problem.

I have a Specific table with 150.000.000 register approximately. I'm using the select bellow, with the command PACKAGE SIZE, but anyway a time out occurs. The Basis don't have permission to change the time of time out. You can Help-me.

EXAMPLE BELLOW:

SELECT rvers

drcrk

docnr

docln

racct

rcntr

hsl

sgtxt

budat

FROM zglt1sbra

APPENDING TABLE gt_zglt1sbra

PACKAGE SIZE 150000

WHERE rrcty = v_rrcty

WHERE rrcty EQ gw_rrcty

AND rvers IN r_rvers

AND ryear IN br_gjahr

AND docnr IN br_belnr

AND rbukrs EQ br_bukrs-low

AND activ NE c_rkiv

AND activ NE c_rkiu

AND budat IN br_budat

AND zzzxblnr IN br_xblnr.

ENDSELECT.

Re: Time Out selecting Table

JozsefSzikszai — Thu, 03 Jul 2008 15:36:52 GMT

hi,

this is a Z table, we don't know much about it, i.e. its primary and secondary keys could be interesting. But I've seen this:

AND ryear IN br_gjahr

...

AND budat IN br_budat

I assume budat(4) = ryear (if the financial year = calendar year),which the selection on gjahr is not really necessary.

On the other hand I ahve no idea these 150.000.000 records are in how many years? For example you could archive records which are older than 01.01.2007 and the whole thing could be faster.

hope this helps some

Re: Time Out selecting Table

Former Member — Thu, 03 Jul 2008 15:41:17 GMT

1)Do not use SELECT.ENDSELECT. Change the select so as to hit the DB in one shot, as shown below:


select f1 f2 f3
into table i_tab
from dbtab
where f1 in s_f1 and
          f2 in s_f2.

if sy-subrc = 0.

endif.

2)The where clause should have fields in the order as in DB table.

3)Do not use "NE" condition in SELECT. It breaks the search key.Retrieve the records without it and then delete invalid records from the internal table, as shown below:


DELETE I_tab WHERE f3 '01'.

5) If that also doesn't help create a secondary index on the DB table.

Re: Time Out selecting Table

Former Member — Thu, 03 Jul 2008 15:44:27 GMT

Hello Flavio Ferreira,

You can do two things to avoid the time out issue:

1) Speak with your BASIS and try to do some Secondary indexing on some of the table fields.

2) As per the Eric Cartman's response you can archive the tabe data upto Dec 2007 .

Hope this helps.

Thanks,

Greetson

Re: Time Out selecting Table

Former Member — Thu, 03 Jul 2008 15:47:30 GMT

Hi,

a bit old fashioned but it may work:

statics:    s_cursor type cursor.
....

DO.
If sy-index = 1. "first loop.
      open cursor with hold s_cursor for
         SELECT ..."continue your statement without package size
endif.

        fetch next cursor s_cursor
                   into corresponding fields
                   of table " your table
                   package size "your size.
        if sy-subrc <> 0.
          close cursor s_cursor.
          EXIT
        endif.
  COMMIT WORK. "resets runtime for max runtime
ENDDO.

Be sure not to append all entries but to process them block by block - else memory would be a problem...

Kind regards,

HP.