topic Re: Data fetch from table GLPCA taking long . in Application Development and Automation Discussions

Data fetch from table GLPCA taking long .

Former Member — Mon, 19 Mar 2007 09:09:05 GMT

Hi Friends,

I am fetching five fields from GLPCA table and i have RACCT and RPRCTR ( account Number and profit Centre res ) in the where condition. neither of these field is the primary key of the table GLPCA. I have around 161096,482 entries in the GLPCA. It takes around 20 min to fetch the data. can you guys think of some method so that it works faster.

Re: Data fetch from table GLPCA taking long .

Former Member — Mon, 19 Mar 2007 09:12:38 GMT

There are 4 secondary indexes created on this table.

Try using fields in the where condition such tat one of the four indexes is used.

for example:

one of the idexes has the following fields:

KOKRS

RYEAR

RPRCTR

RVERS

RACCT

Similarly have a look at the other secondary indexes in SE11 for the table GLPCA.

Regards,

Ravi

Re: Data fetch from table GLPCA taking long .

Former Member — Mon, 19 Mar 2007 09:28:57 GMT

As mentioned above try to use the index available. or try creadting your own index.

You have to use the fields mentioned in the index in the same order in ur where clause too.

regards,

madhu

Re: Data fetch from table GLPCA taking long .

Former Member — Mon, 19 Mar 2007 09:31:51 GMT

Hi Vidya,

If you are selecting the data through a For All Entries in itab addition and the itab has more data i.e. around 1 or 2 Lakh rec, your selection is going to take time.

So try using Open Cursor Statement and get 200 records from each fetch statement. Also try selecting 200 records in SE16 with your selection data and test its effeciency. By doing this you are grabbing the records in chunks and not as a whole block.

Do tell me if the issue still persists with the way you are writting the select.

Have a good day,

Anand

Re: Data fetch from table GLPCA taking long .

Former Member — Mon, 19 Mar 2007 09:32:12 GMT

Hi Vidya,

Also try to use Package size and check out if it reduces some time

 select field1 field2 from ztable into corresponding fields of wa_itab package size 10000 where XXXXX..
  append wa_itab to itab.
  clear wa_itab.
endselect.

Re: Data fetch from table GLPCA taking long .

Former Member — Mon, 19 Mar 2007 09:42:38 GMT

Hi Anand,

I am not using for all entries in itab statement. Infact the code is as below

select refdocnr

docln

drcrk

hsl

rprctr

into table i_data

from glpca

where racct eq p_racct and

rprctr in r_prctr and

budat in r_date.

Can you suggest something here.

Regads,

Vidya.

Re: Data fetch from table GLPCA taking long .

Former Member — Mon, 19 Mar 2007 10:36:22 GMT

Hi Vidya,

The time utilization is due to the way data base is been accessed. Just think that you are selecting same data in SE16 with 200 hits. See how that behaves. If you find its Ok(i.e. around 1/2 mins) go for open cursor logic. Check this syntax and change the code accordigly and try:

OPEN CURSOR w_cur1 FOR

SELECT ktabg vkorg ktaar ktaer kunnr

vtweg spart

FROM vbka

WHERE ktabg IN so_ktabg AND

vkorg IN so_vkorg AND

kunnr IN so_kunnr AND

ktaar IN so_ktaar AND

ktaer IN so_ktaer.

DO.

FETCH NEXT CURSOR w_cur1 INTO CORRESPONDING

FIELDS OF TABLE it_vbka2 PACKAGE SIZE 200.

IF sy-subrc NE 0.

CLOSE CURSOR w_cur1.

EXIT.

ENDIF.

ENDDO.

It behaves just like Select ... Endselect. The most important thing here is the Pakage size which you have to decide based on the SE16 results. Also please mind that if the Package size is less, number of times it hits the DB is high, reducing the efficiency thereby.

Regards & Thanks,

Anand

Re: Data fetch from table GLPCA taking long .

Former Member — Mon, 19 Mar 2007 11:43:44 GMT

Hi Chandrashekhar,

Can you please tell the logic of Package size. It is improving the fetch time . if possible can you tell the logic like why its improving the time. How does package size help.

Regards,

Vidya .

Re: Data fetch from table GLPCA taking long .

Former Member — Mon, 19 Mar 2007 12:01:36 GMT

If you use the PACKAGE SIZE addition, the lines of the selection are not written into the internal table at once, but in packets. You can define packets of <n> lines that are written one after the other into the internal table. If you use INTO, each packet replaces the preceding one. If you use APPENDING, the packets are inserted one after the other. This is only possible in a loop that ends with ENDSELECT. Outside the SELECT loop, the contents of the internal table are undetermined. You must process the selected lines within the loop.

chk this link:

regards,

madhu

Re: Data fetch from table GLPCA taking long .

Former Member — Mon, 19 Mar 2007 12:06:41 GMT

Hi vidya,

If you give package size as 10000 , it will fetch 10000 records at a time

Re: Data fetch from table GLPCA taking long .

Former Member — Tue, 20 Mar 2007 04:22:47 GMT

Hi Friends,

The solution provided by you all helped in improving the performence. So thanks to you all.

Regards,

Vidya.