topic Re: Optimise Select in Application Development and Automation Discussions

Optimise Select

k_gjergja — Wed, 27 Jun 2012 13:29:43 GMT

Hello ,

I have select statement that takes a lot of time to be executed and I am looking for the best way to improve it.

gjahr is key field .

Please advice if there is any addition that can improve the performance .

   SELECT bukrs gjahr blart belnr rmwwr bldat budat erfnam rbstat
    FROM rbkp   INTO CORRESPONDING FIELDS OF TABLE it_output
     WHERE gjahr IN gjahr and
           rbstat = 'A'.

Thanks

Chris

Re: Optimise Select

laurent_fournier2 — Wed, 27 Jun 2012 13:35:38 GMT

Hi Chris,

As I can see, table rbkp has an index per vendor and invoice status. There is a chance to improve you current select statement by using this index.

Add an empty range for vendor and select using status and vendor. Then, read only the entries for the year you are looking for. Sometimes is most fast to collect many entries with an index and then try to read only the required entries from an internal table than to select the exact entries from the table in once.

Try that and reply.

Re: Optimise Select

RaymondGiuseppi — Wed, 27 Jun 2012 13:43:56 GMT

GJAHR is one of the primary key but not the first, so almost all databases won't be able to optimize access..

Execute a SQL trace and analyze execution plan.

At last solution you could create a secondary index (with RBSTAT ?), but look for any other criteria to access the data, as well as other database table that can share the required data like FI/CO documents

Regards,

Raymond

Re: Optimise Select

Former Member — Wed, 27 Jun 2012 13:58:26 GMT

Also refrain from using INTO CORRESPONDING FIELDS OF. Create your internal table with fields in the same order as you want to select.

Regards,

Shravan

Re: Optimise Select

Former Member — Wed, 27 Jun 2012 14:47:17 GMT

RBKP is a transactional table.

With your query, it will try to fetch all the data in the particular year. Of course it is going to be to a huge data. Also, RBSTAT is a non-key field. This select query HAS to take lot of time.

Talk with the functional person. See if you can restrict or narrow down the number of Documents (BELNR). Apply that criteria in the ABAP and fetch the document numbers in a dummy intermediate table. Then use For all entries in your current query.

I would not recommend the secondary index. As the field is about status and it will rather hog system resources and might impact other standard functionality. I strongly suggest you not to do that.