topic Re: Ztable Read Problem in Application Development and Automation Discussions

Ztable Read Problem

Former Member — Fri, 15 Jun 2007 11:21:23 GMT

I have a Z table call ZPRODUCTION there are so many records about 2,000,000 and daily growing.

Now I want a select one field from that table.

1.I have one internal table with 150 records.

2.I looped that table and fetch each record to ZPRODUCTION table for getting one record from that.

My problem is it was getting 15minutes to read that 150 records.

I have already create an index to Z table against what I use for where condition.

When I run the program I checked with SM50, action is sequential read

Please can you anybody help me to solve this matter.

Re: Ztable Read Problem

Former Member — Fri, 15 Jun 2007 11:26:50 GMT

Hi Nelson ,

When you enter the same set of input in SE11 to fetch the data , does it also take the same amount of time.

If not then please do an SQL trace when you access using SE11 and see what is the diffrenece.

Regards

Arun

Re: Ztable Read Problem

Former Member — Fri, 15 Jun 2007 11:29:24 GMT

hi,

could you please submit the code, so that we can check whats going wrong.

there seem to be a problem in your select statement.

regards

fazeel

Re: Ztable Read Problem

Former Member — Fri, 15 Jun 2007 11:45:44 GMT

this is the code

Getting Planning Sections (Module / Line and Workcenter)

SELECT * FROM ZPLAN_SECTIONS

INTO CORRESPONDING FIELDS OF TABLE IT_PLAN_CAPACIT

WHERE ZPLANT IN SO_PLANT

AND ZMODUL IN SO_MODUL

AND ZLINE IN SO_LINE.

Getting Order Details

SELECT ZPLAN_SECTIONS_{ZPLANT ZPLAN_SECTIONS}ZMODUL ZPLAN_SECTIONS_{ZWCENT ZPLAN_SECTIONS}ZLINE AFKO_{AUFNR AFKO}GAMNG AFKO_{PLNBEZ AFKO}GSTRS

INTO CORRESPONDING FIELDS OF TABLE IT_PLAN_CAPACIT2

FROM ZPLAN_SECTIONS

INNER JOIN CRHD ON ZPLAN_SECTIONS_{ZMODUL = CRHD}MATYP

AND ZPLAN_SECTIONS_{ZWCENT = CRHD}ARBPL

AND ZPLAN_SECTIONS_{ZLINE = CRHD}SORTB

INNER JOIN AFVC ON CRHD_{OBJID = AFVC}ARBID

INNER JOIN AFKO ON AFVC_{AUFPL = AFKO}AUFPL

WHERE ZPLAN_SECTIONS~ZPLANT IN SO_PLANT

AND ZPLAN_SECTIONS~ZMODUL IN SO_MODUL

AND ZPLAN_SECTIONS~ZLINE IN SO_LINE

AND CRHD~OBJTY = 'A'

AND AFVC~STEUS = 'PP09'

AND AFKO~GSTRS IN SO_ORDAT.

LOOP AT IT_PLAN_CAPACIT2 ASSIGNING <FS_PLAN_CAPACIT>.

Getting Complete Production Qty (From ZPRODUCTION table)

SELECT SINGLE LMNGA

INTO <FS_PLAN_CAPACIT>-LMNGA

FROM ZPRODUCTION

WHERE AUFNR = <FS_PLAN_CAPACIT>-AUFNR

AND BUDAT = SO_ORDAT.

Re: Ztable Read Problem

Former Member — Fri, 15 Jun 2007 11:47:52 GMT

Hi Nelson ,

One options is to use select into table for all entries.

This will reduce the number of hits on the database , but i am not sure if it will reduce the time , becauase then also you will have to loop and modify the internal table.

What i would suggest is join all the three table , i.e. join this table z.._production.. also in the select statement just above the loop statement.

Regards

Arun

Reagds

Arun

Message was edited by:

Arun R

Re: Ztable Read Problem

kostas_tsioubris — Fri, 15 Jun 2007 11:49:12 GMT

Hi,

have you checked that your index exists in database? When you create an index most possible it will require another step in order to be created in your database system. Also make sure you have included the mandt field in your index.

Kostas

Re: Ztable Read Problem

Former Member — Fri, 15 Jun 2007 11:50:52 GMT

is it not possible for you to take out the select from the loop...i mean execute the select first and get records in a table and then do a loop.

a select within a loop will always reduce performance and should be avoided as much as possible

write a select like

select lmnga from zproduction into table itab for all entries in IT_PLAN_CAPACIT2

where aufnr = it_plan_capacit2 and .....

after this do your looping on the table and read the table you used in the above statement....this will yield better results

Re: Ztable Read Problem

Former Member — Fri, 15 Jun 2007 11:54:44 GMT

if AUFNR BUDAT ARE NOT THE KEY FIELDS YOU CAN TRY THIS

SELECT LMNGA

INTO <FS_PLAN_CAPACIT>-LMNGA

FROM ZPRODUCTION UP TO 1 ROWS

WHERE AUFNR = <FS_PLAN_CAPACIT>-AUFNR

AND BUDAT = SO_ORDAT.

IT MAY HELP YOU OR BETTER TO GO FOR ALL ENTRIES IT WILL BE FASTER AS SAID BY OTHERS.

REGARDS

SHIBA DUTTA