Question:
Hi Friends,
I was hoping you could help me with my select statement for GLPCA.
The table has an index below:
KOKRS
RYEAR
RPRCTR
RVERS
RACCT
The select statement I did was:
SELECT rbukrs
rprctr
kokrs
racct
rfarea
hsl
ksl
kostl
FROM glpca INTO CORRESPONDING FIELDS OF lwa_glpca
WHERE kokrs IN so_kokrs
AND ryear EQ p_gjahr
AND rprctr IN r_prctr
AND rvers EQ c_version
AND racct IN r_gl
AND rldnr EQ c_ledger
AND ( rrcty = '0'
OR rrcty = '2' )
AND poper IN so_perio
AND rbukrs IN so_bukrs
AND kostl IN r_kostl.
But NO MATTER WHAT COMBINATIONS of where statement I use, there's no signigicant change in speed. A maximum runtime error if it is processed in foreground. Do I need to use a native SQL statement instead?
Please help me with this.
Thanks in advance!!!
Answer:
Care to share what you entered at the selection screen when you run the program?
Answer:
Hi,
Below are the values in the screen:
kokrs = maximum of 10 controlling areas
ryear = single year or current fiscal year
rprctr = 100 or more profit centers
rvers = single version
racct IN 100 or more Cost elements
rldnr EQ single ledger
( rrcty = '0'
OR rrcty = '2' )
poper = period 1 to 12
rbukrs = 12 company codes
kostl = 100 plus or more cost centers
The 100 plus values for cost centers, profit centers and cost elements are retrieved somewhere in the program.
Answer:
Run ST05 and see the SQL statement hits the correct index. If yes, nothing much you can do about it .. if not, then most probably the subsequent 'WHERE' statements need to be check after you've retrieve the entries.
Answer:
As you are using several values for KOKRS the index will not be helping you much. Possibly you could change your logic to read data for 1 KOKRS at a time (KOKRS EQ xxx) and summing it up afterwards.
Answer:
How much data do you think this report would usually bring back? It seems to me that it will always bring back a lot of data, perhaps a complete year's worth of data if you are specifying all your controlling areas and all your profit centres and all your cost elements (I don't know how many profit centres or cost elements you have but 100+ I guess could mean the whole lot). If there is a lot of data involved any select will be slow however correctly indexed, and you are using the complete index and you don't get much more correct than that. Did the performance trace say that the select is using this index? If not, and if you have a cost based optimiser on your database you could try refreshing the database stats. If so, as has been said, you are a bit stuck and will either have to run the report with more specific selection criteria or run it in the background.
Answer:
As you are using several values for KOKRS the index will not be helping you much. Possibly you could change your logic to read data for 1 KOKRS at a time (KOKRS EQ xxx) and summing it up afterwards.
Is that generally true - to use the index you have to specify a single value of the first field? If so I wasn't aware of it.
Made me think though - it seems a strange from a business POV to read at this level of detail across several contriolling areas. If it's summary data you want, there are a whole horde of other GL* tables which should have considerable fewer entries.