Select query: performance optimization

[Former Member]

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

[Former Member]

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.

[Former Member]

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.

[Former Member]

SELECT * FROM tsp01

INTO TABLE lt_tsp01

WHERE rqfinal = < > and rqclient < > and rqowner = sy-uname .

ashok

[Former Member]

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

[Former Member]

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

[Former Member]

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

[former_member194613]

use the escape sequence for the code inspector

Do not add nonsense-modifications.

[Former Member]

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.

[Former Member]

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

[former_member194613]

> 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.