10-29-2007 4:30 AM
hi,
i need to pull out data from table afru but none of the field at where clause is key field in table afru.
we not allowed to create indexes so would like to know how to optimise the fetch.
SELECT SUM( lmnga ) SUM( xmnga )
INTO (wa-lmnga, wa-xmnga)
FROM afru
WHERE aufnr EQ wa-aufnr AND
vornr EQ wa-vornr AND
werks EQ wa-werks and
stokz ne 'X' and
stzhl eq '0'.
thanks
10-29-2007 4:33 AM
try to include all primary key for data fetching
MANDT MANDT CLNT 3 0 Client
RUECK CO_RUECK NUMC 10 0 Completion confirmation number for the operation
RMZHL CO_RMZHL NUMC 8 0 Confirmation counter
In a SELECT statement, only the fields (field-list) which are needed are selected in the order that they reside on the database, thus network load is considerably less. The number of fields can be restricted in two ways using a field list in the SELECT clause of the statement or by using a view defined in ABAP/4 Dictionary. The usage of view has the advantage of better reusability.
SELECT SINGLE is used instead of SELECT-ENDSELECT loop when the entire key is available. SELECT SINGLE requires one communication with the database system, whereas SELECT-ENDSELECT needs two
Always specify the conditions in the WHERE-clause instead of checking them with check-statements, the database system can then use an index (if possible) and the network load is considerably less. You should not check the conditions with the CHECK statement because the contents of the whole table must be read from the database files into DBMS cache and transferred over the network. If the conditions are specified in the where clause DBMS reads exactly the needed data.
Complex code is not embedded within a SELECT / ENDSELECT statement.
No complex WHERE clauses, since complex where clauses are poison for the statement optimizer in any database system.
For all frequently used SELECT statements, try to use an index. You always use an index if you specify (a generic part of) the index fields concatenated with logical ANDs in the Select statement's WHERE clause
When loading data into Internal table, INTO TABLE OR APPENDING TABLE is used instead of a SELECT/APPEND combination. It is always faster to use the INTO TABLE version of a Select statement than to use APPEND statements.
Use a select list with aggregate functions instead of checking and computing, when trying to find the maximum, minimum, sum and average value or the count of a database column.
Rewards if useful........
Minal Nampalliwar
10-29-2007 4:35 AM
Eliana,
use in this way
SELECT SUM( lmnga ) SUM( xmnga )
INTO (wa-lmnga, wa-xmnga)
FROM afru
<b>upto 1 rows</b>
WHERE aufnr EQ wa-aufnr AND
vornr EQ wa-vornr AND
werks EQ wa-werks and
stokz ne 'X' and
stzhl eq '0'.
endselect.
10-29-2007 4:43 AM
Srikanth,
You can fetch the data w.r.t a primary key in that table and the loop at the internal table and delete the data which are not corresponding with the workarea details as you had mentioned.
I mean fetch all data w.r.t rueck or rmzhl from afru and then loop at the internal table and delete the contents w.r.t the work areas details as per the requirement.
K.Kiran.
10-29-2007 4:53 AM
kiran,
If the primary key is directly known, why will a person choose this type of indirect fetch. As the primary key is not known only, eliana is trying to fetch with the fields that are available to her.
10-29-2007 5:15 AM
Hi,
Srikanth is correct. I donno why people don't read!!!!
Eliana you can try this
SELECT lmnga
xmnga
stokz
INTO TABLE afru
FROM afru
WHERE aufnr EQ wa-aufnr AND
vornr EQ wa-vornr AND
werks EQ wa-werks and
*stokz ne 'X' and
stzhl eq '0'.
Delete tab_afru where stokz eq 'X'.
Loop at tab_afru.
SUM.
Endloop.
This might help. The premise here is the 'NE' is performance intensive which it is since it's a rejection criteria rather than selection.
Regards
Nishant
10-29-2007 5:27 AM
hi,
really appreciate the help the point given accordingly.
now i have i issue, as i only want to sum 2 quan data type field, the sum in the loop will sum up other quan data type field as well. how should i avoid that?
Loop at tab_afru.
SUM.
Endloop.
thanks
10-29-2007 5:32 AM
Hi Eliana,
I understood from your Select query that there are only two fields.
Can you please put the actual code and I'll see what can be done ?
regards
Nishant
10-29-2007 5:45 AM
hi,
loop at it_work into wa_work.
perform check_status using wa_work-aufnr wa_work-objnr1.
IF chk_del ne 'Y'.
SELECT SUM( lmnga ) SUM( xmnga )
INTO (wa_work-lmnga, wa_work-xmnga)
FROM afru
WHERE aufnr EQ wa_work-aufnr AND
vornr EQ wa_work-vornr AND
werks EQ wa_work-werks and
stokz ne 'X' and
stzhl eq '0'.
IF sy-subrc = 0.
it_work-lmnga = wa_work-lmnga.
it_work-xmnga = wa_work-xmnga.
it_work-f_del = ''.
ENDIF.
ELSE.
it_work-f_del = 'Y'.
ENDIF.
modify it_work transporting lmnga xmnga f_del.
clear wa_work.
endloop.
10-29-2007 5:50 AM
Hey,
No need to worry,
Just create and internal table tab_afru with header line with fields : lmnga, xmnga and stokz.
Then use select as mentioned.
IF sy-subrc = 0.
it_work-lmnga = tab_afru-lmnga.
it_work-xmnga = tab_afru-xmnga.
it_work-f_del = ''.
ENDIF.
I think this should do it.
regards
Nishant