topic Re: Select query: performance optimization in Application Development and Automation Discussions

Select query: performance optimization

Former Member — Fri, 04 Sep 2009 07:33:09 GMT

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

Re: Select query: performance optimization

Former Member — Fri, 04 Sep 2009 07:40:53 GMT

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.

Re: Select query: performance optimization

Former Member — Fri, 04 Sep 2009 07:57:23 GMT

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.

Re: Select query: performance optimization

Former Member — Fri, 04 Sep 2009 16:32:56 GMT

SELECT * FROM tsp01

INTO TABLE lt_tsp01

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

ashok

Re: Select query: performance optimization

Former Member — Tue, 08 Sep 2009 08:44:08 GMT

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

Re: Select query: performance optimization

Former Member — Tue, 08 Sep 2009 13:16:42 GMT

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

Re: Select query: performance optimization

Former Member — Wed, 09 Sep 2009 05:16:25 GMT

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

Re: Select query: performance optimization

Former Member — Wed, 09 Sep 2009 07:49:03 GMT

use the escape sequence for the code inspector

Do not add nonsense-modifications.

Re: Select query: performance optimization

Former Member — Wed, 09 Sep 2009 08:04:13 GMT

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.

Re: Select query: performance optimization

Former Member — Wed, 09 Sep 2009 12:49:39 GMT

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

Re: Select query: performance optimization

Former Member — Wed, 09 Sep 2009 13:30:01 GMT

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