09-04-2009 8:33 AM
Hi,
I run a report in background programatically. To extract the Spool number i wrote the following select query.
SELECT * FROM tsp01
INTO TABLE lt_tsp01
WHERE rqowner = sy-uname .
SORT lt_tsp01 BY rqident DESCENDING.
READ TABLE lt_tsp01 INTO lwa_tsp01 INDEX gc_int_1.
MOVE lwa_tsp01-rqident TO lv_spool_id.
It is working fine. But when i run code inspector, it shows an error message interms of performance " Large table TSP01: No first field of a table index in WHERE condition".
Can you tell me a better way to write this query. How can i optimize it.
Thanks
Vishnu
Moderator message - Moved to the correct forum
Edited by: Rob Burbank on Sep 4, 2009 1:44 PM
09-04-2009 8:40 AM
Hi, if you do not want to change the program, because you select on the owner, add an extra index for the table on owner.
09-04-2009 8:57 AM
Hi,
I will suggest to use field spool request no RQIDENT, in your query rather than creating an new index.
Try using RQIDENT not initial, then it will access primary index and your issue will be resolved.
Hope it helps you.
09-04-2009 5:32 PM
SELECT * FROM tsp01
INTO TABLE lt_tsp01
WHERE rqfinal = < > and rqclient < > and rqowner = sy-uname .
ashok
09-08-2009 9:44 AM
Hi,
Try to do the fetch using the key field (RQIDENT). But if your functionality is that, u don't get this key field, then try to create a secondary index for the table with the index field as RQOWNER.
Regards
Ramesh Sundaram
09-08-2009 2:16 PM
hi,
insted of this,
SELECT * FROM tsp01
INTO TABLE lt_tsp01
WHERE rqowner = sy-uname .
u can use like this,
SELECT * FROM tsp01
INTO TABLE lt_tsp01
WHERE RQIDENT like '%'
rqowner = sy-uname .
Sure .. it wil work..
Regards,
JJP
09-09-2009 6:16 AM
WHERE RQIDENT sl_RQIDENT (Declare a Select option with no display to avoid the Code Inspector Error)
RQIDENT = sy-mandt (Guess ur Requirement is Client Specific)
rqowner = sy-uname .
Performance will not be affected as all the Secondary indexes has been used
cheers
09-09-2009 8:49 AM
use the escape sequence for the code inspector
Do not add nonsense-modifications.
09-09-2009 9:04 AM
Hi.
You can use this code, is better for the performance and is the same:
SELECT MAX rqident INTO lv_spool_id FROM tsp01
WHERE rqowner = sy-uname .
Regards.
09-09-2009 1:49 PM
Hi Vishnu,
For large tables you should really use key fields or at least an index of the table to access the data.
In your case, access seems to be per user. So, as it is not in key fields (only RQIDENT which is the spool number), you should use index A of table TSP01 which contains fields :
- RQFINAL which is a flag indicating spool status ("." indicates it is finished)
- RQCLIENT : client where the spool has been generated (generally sy-mandt)
- RQOWNER : the owner of the spool
So in order to have better performance (and to avoid message in code inspector), you should have a query like this one:
SELECT * FROM tsp01
INTO TABLE lt_tsp01
WHERE rqfinal = '.' AND
rqclient = sy-mandt AND
rqowner = sy-uname .
And as mentioned by Alex Alvarez, since your goal is only to have the last spool number, you should only get the max RQIDENT!
Regards,
Samuel
09-09-2009 2:30 PM
> Large table TSP01:
Please check how large is the table actually in your system, the code inspector does not know
The last comment is an approach to use the available secondary key, you must add the client, because the table itself does not have the client in first position.
Additionally you could use the aggregate, see above, to get the result directly from the DB.